Here's a quick snippet to get a listing of the database backups that last occurred on a server. Most solutions provided a single backup listing, but not the brief summary of the last backup details I was looking for.
Had a blast making this song. Unfortunately, creating video of slow motion pizza cutting was going to be a bit awkward to do, as I didn't feel like asking a pizza place to let me stand behind the counter and video pizza assembly. :-)Best listened to with headphones, not a phone speaker :-)
This was an experimentation with several new tools for me.
I first used Presonus Studio One 3 to record some basic track parts like the guitar. I then added some drums using BFD3 (which I've previously reviewed). Using the tools to adjust and randomize the velocity and humanize the rhythm helped create something that sounds more realistic. Then I recorded a fun guitar riff, and used Melodyne Essentials, which is included in Studio One, to convert the guitar part into MIDI. This allowed me to then change the guitar part I had recorded into a MIDI song part and I choose to replace with Pizzicato strings, as I really liked the sound of Presence XT string section. I've never heard a post-rock style song with pizzicato strings, so figured I might bring a little variety to the post-rock mix.
The video editing was extremely simplistic. I used Edius 8.2, which is a fantastic NLE for broadcast video editing. I plan on writing or creating a video presentation on that soon to help others interested in video editing to see a little more on that process. I graded with Magic Bullet Looks as I liked the grain and dark feel of the image. Using stabilization effect in Edius, and slowing the speed to 35% (originally 1080p 60fps Panasonic GH3) the resulting imagery was great. I recorded this imagery handheld from this camera on a rainy day in my backyard. I thought the close up of the rain puddling would be interesting for something, and finally found a use. Hope you enjoy my experiment, I'll work later on doing some walkthroughs for anyone interested more in converting audio to MIDI, basic editing with Edius, and some workflow examples with Presonus Studio One. Music:
- Presonus Studio One 3
- BFD3 Drums
- Presence XT Strings
- Guitar: Epiphone Wildkat
- Amp: Vox AC15C2
- Zoom H6 Interface
- 5 string bass Video
- Panasonic GH3
- Editing with Edius 8.2
- Graded with Magic Bullet Looks
I hadn't seen much talk on doing data comparisons on temporal tables, as they are a new feature. I went through the exercise to compare current to historical to see how Red Gate & Devart handled this. I'm a part of the Friends of Red Gate program, so love checking out their latest updates, and I'm also a regular tester on Devart which also provides fantastic tools. Both handled Temporal Tables with aplomb, so here's a quick walk through on how I did this.
{{< admonition type="info" title="Updated: 2020-04-29" >}} broken image links removed {{< /admonition >}}
With the latest version of SSMS, you can see the temporal tables labeled and expanded underneath the source table.
To begin the comparison process, you need to do some custom mapping, which requires navigating into the Tables & Views settings in SQL Data Compare
To remap the Customers to Customers_Archive, we need to select this in the tables and choose to unmap the Customer and the Customer-Archive Tables from each other. This is 2 unmapping operations.
Go into the comparison settings on the table now and designate the key as the value to compare against. For the purpose of this example, I'm just doing key, you can change this however you see fit for your comparison scenario.
In this example, I'm removing the datetime2 columns being used, to instead focus on the other columns.
If you run into no results coming back, look to turn off the setting in compare options for Checksum comparison, which helps improve the initial compare performance. With this on, I had no results coming back, but once I turned off, the comparison results came back correctly.
This entry was matched in DbForge SQL Data Compare as a conflict due to matching the key in a non-unique manner. The approach the two tools take is a little different. In RG Data Compare
The entry identified as potential conflict by DbForge is identified in the Only In Destination.
Both tools report differences. RG's tool has focused on the diff report being simple CSV output. This is fine in the majority of cases, though I'm hoping for additional XLSX and HTML diff reports similar to DbForge eventually. In the case of the CSV output, you could consume the information easily in Power-BI, Excel, or even... SQL Server :-) No screenshot on this as it's just a csv output.
Going into the mapping, you can see support for Customers and Customers_Archive, which is the temporal history table for this. In this case, I mapped the current table against the temporal table to compare the current against the change history.
As a simple example, I just provided the primary key. You could get creative with this though if you wanted to compare specific sets of changes.
Looks like the conflict is handled differently in the GUI than Red Gate, as this provides back a separate tab indicating a conflict. Their documentation indicates: Appears only if there are conflict records (records, having non-unique values of the custom comparison key). DbForge Data Compare for SQL server Documentation - Data Comparison Document
The diff reports provided by DbForge Data Compare are very well designed, and have some fantastic output options for allowing review/audit of the rows.
Here is a sample of a detail provided on the diff report. One feature I found incredibly helpful was the bold highlighting on the columns that had diffs detected. You can trim down the report output to only include the diff columns if you wish to further trim the information in the report.
Overall, good experience with both, and they both support a lot of flexibility with more specialized comparisons.
Untrusted constraints can be found when you alter/drop foreign key relationships and then add them back without the proper syntax.If you are deploying data through several tables, you might want to disable foreign keys on those tables during the deployment to ensure that all the required relationships have a chance to insert their data before validation.
Once you complete the update, you should run a check statement to ensure the Foreign Key is trusted. The difference in the check syntax is actually ridiculous.... This check would not ensure the actual existing rows are validated to ensure compliance with the Foreign Key constraint.
This check would check the rows contained in the table for adherence to the foreign key relationship and only succeed if the FK was successfully validated. This flags metadata for the database engine to know the key is trusted.
I originally worked through this after running sp_Blitz and working through the helpful documentation explaining Foreign Keys or Check Constraints Not Trusted.
Untrusted Check Constraints and FKs can actually impact the performance of the query, leading to a less optimal query plan. The query engine won't know necessarily that the uniqueness of a constraint, or a foreign key is guaranteed at this point.
I forked the script from Brent's link above and modified to iterate through and generate the script for running the check against everything in the database. This could be modified to be server wide if you wish as well. Original DMV query credit to Brent, and the the tweaks for running them against the database automatically are my small contribution.
Note: I wrote on this a while back, totally missed that I had covered this. For an older perspective on this: Stranger Danger... The need for trust with constraints
If my ranking in google search drops to the end because of this post... well.... I'm ok with that.
I've been using this for over a year as a beta tester (they provided license for me to test and use latest versions). I had a perfect case to demo the craziness of some sites with ads vs using Adguard the other day and figured I'd share it if you... like me... hate the clutter they provide on many sites. Now, note that other factors come into play here. For example, the site I hite should have been using some optimization for images presented. This site was pretty insane on the ad content, so others aren't as dramatic. I just saw this as a Google Now recommended article and checked it out.
WITH NO ADBLOCKING 679 Requests 11.3 MB for a single webpage load Finish Time 1.1min
WITH ADGUARD 116 Requests 2.2 MB for a single webpage load Finish Time 7.23sec This was loaded with the mobile emulator. If you are paying for data (for instance I'm on Project Fi), this could be a huge difference in your browsing bandwidth.
This blew my mind. According to Adguard metrics it had saved me over 2GB. Now, even taking this with a grain of salt, I was still pretty impressed by the results. I can immediately tell when I'm not running Adguard on android as the ads are everywhere. Adguard android has some additional functionality that provides the ability to create a local VPN and filter apps as well, so if you are using some app that has a annoying banner add right near the menu, this will most likely eliminate it. This is a pretty big image, and I blurred out text/ads to avoid any issues. Any guess at which one was the one with Adguard running?
There are options to allow some "acceptable" ads. Not interested in this personally, but those who are should know it's offered. Cost can be a little higher than some options due to yearly cost, but the reward of a constantly developing product seems worth it, especially for folks that browse a lot.
Only con I've come across with this is custom filtering options are a bit confusing for a non-technical user. Hopefully this will improve in the future to offer a much easier ad editing experience like some other similar toolkits. Lastly, a better notification of potentially blocking issues would be nice. I've come across a few sites that Adguard has blocked on various scripts or other "needed" actions that prevent the site from working. Disabling temporarily is acceptable for me, as this is quick with the chrome extension (pause for 30 secs). I'd say a better notification system, if even possible, on potentially site disrupting scripts/cookies blocked would be great enhancement. Overall, highly recommend this cross platform solution if you are looking for a better way to browse... with less ads!
{{< admonition type="info" title="Updated 2020-07-06" >}} After a great chat with Sean today (creator), I did see some pretty cool benefits that for those looking for more scalability, will find pretty interesting.
Overall, great conversation and found out some really cool things about postcode commands that could be PowerShell driven. Definitely worth a further look if you want an alternative to the commonly used Ola Hallengren solution, and especially if you are wanting more table driven configuration options over the need to customize the commands in the agent steps. {{< /admonition >}}
{{< admonition type="info" title="Updated 2017-01-25" >}} While I think the minionware solution is pretty awesome, I think it takes more work for the value, and can be a bit confusing to correctly setup, vs the Ola Hallengren solution, esp since you can install this quickly with dbatools now. I'd lean towards Ola Hallengren for simple implementations, and consider MinionWare's option if you are looking at their flexibility in the table based configuration. The learning curve seems higher to me, but more for those looking to tweak options a lot. Both are great solutions, just be aware MinionWare will require a little more digging to leverage it fully. {{< /admonition >}}
Here's my personal tweaked settings for deploying Minionware's fantastic Reindex & Backup jobs. In the development environment, I wanted to have some scheduled jobs running to provide a safety net, as well ensure updated statistics, but there were a few default settings I wanted to adjust. In particular, I tweaked the default fill factor back to 0/100. I also installed all the objects to a new "minion" database instead of in master, as I'm beginning to be a fan of isolating these type of maintenance jobs with logging to their own isolated database to easy portability. I also adjusted the default retain days on backups to 30.
You can use this template as a guide to help you adjust the default backup settings to fit your environment a little better. There has been various forms of discussion on the adjustments of Fill Factor for example on the defaults. For more detailed explanation, see Brentozar.com post An Introduction to Fillfactor in SQL Server. For my usage, I wanted to leave the fill factors as default, so the install scripts flips these back to my desired settings. I also run the sp_config command to ensure backup compression is enabled to save some space.
Maybe this will help you get up to speed if you want to try out this great solution, but tweak a few defaults. The ease of installation across multiple instances makes this my current favorite solution, followed by the fantastic Ola Hallengren solution.
I ran into an error: The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)
I evaluated the sql server configuration manager protocols for sql server and saw that named pipes was disabled. I tried ensuring that this wasn't causing the issue, but enabling but it didn't fix. Thankfully, Andrew on StackOverflow had the answer here:
First thing you should do is go into the logs (Management\SQL Server Logs) and see if SQL Server successfully registered the Service Principal Name (SPN). If you see some sort of error (The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service) then you know where to start. We saw this happen when we changed the account SQL Server was running under. Resetting it to Local System Account solved the problem. Microsoft also has a guide on manually configuring the SPN. Andrew 3/19/2014
When I went into the configuration manager I changed the format from the DOMAIN\USER to searching with advanced and matching the user. The username was applied as USER@DOMAIN.COM instead. When I applied, and restarted the sql service, this still didn't fix.
I read some help documentation on this on smatskas.com but it didn't resolve my issue as I had the correct permissions, and I verified no duplicate SPN by running the command setspn -x I ran gupdate /force to ensure was properly in sync with the policies and it did get the time updated. However, the problem persisted. I went back to checking for a specific conflict by running
Still no luck....
Finally, I switched the account to use LocalSystem (this was in a dev environment) following the directions by Bob Sullentrup and this allowed it to successfully register the SPN.
I'll update my blog post when I have a better understanding on exactly why this occurs, but for now, at least I was able to proceed.
I've used Central Management Server registered servers in SSMS for primarily one purpose, saving connections. :-) This is definitely not the intended usage. As I've explored the benefits of using this a little more, I put a few notes together to help share the concepts I came across. I was brand new to this feature in SQL server, and found some of the functionality pretty powerful, especially if you are in an environment that has a lot of servers to manage and ensure consistent configuration among all of them.
If you need to move your Central Management Server (CMS) entries to a new CMS, then use the export and import functionality located under the CMS > Tasks context menu.
Right click on the CMS group and choose new query. This tab will now execute the same query in parallel across all the selected instances, allowing quick adjustments. One creative use of this is to register two databases in the server group, then click new query. They could be on the same server if you wish. Once you start a new query on this group you could run the same query on two separate databases with no extra effort. An alternative to using this is Red Gate's SQL Multiscript which offers a bit more customization in the behavior and project file saving for multi-server, multi-database query running. You can identify a multiserver query at the bottom identified by
It's easy to forget you are running a server group query. Use some type of visualization and don't leave the query window open longer than you need to, especially in a production environment. One hint can be setting up the Red Gate tab color if you have SQL Prompt. You can see the connection details on the tab are a little different, listed with the CMS server group name + database, such as the image below
In reviewing technet article on Policy-Based Management
From the CSM context menu you can perform some nice functionality such as start, start, restart of SQL services, view error logs, and even pull up the SQL configuration manager for that instance! Take advantage of this to easily adjust settings across instances without having to log into remote machines.
There are a few different types of policy behaviors to know about. From MSDN article Administer Servers by Using Policy-Based Management I found that there were a few ways the evaluation of a policy is handled.
This suprised me a little. The policy functionality wasn't available in the CMS registered server tab. Instead, go to the server in Object Explorer and expand Management > Policy Management > Policies
Add new condition, there is a large list of policies to evaluate. You can detailed information on them on MSDN here.
You'll see a huge list of Facets to evaluate and then you can easily setup logic to evaluate this.
In this case, I linked back to my favorite resource for server configuration... the Ozar! Providing some detail back on this could be great for quickly providing details later back to someone reviewing the results.
For all this hard work, you'll get two fancy new icons in object explorer. With this work, I'm thinking saving your policies for backup with scripts would be a great idea.... scripting this would be much faster than all these steps to check one setting. I wish the dialogue had been focused on setting up multiple conditions quickly instead of all that work for a single Fact to be evaluated.
My first run gave me a failure, despite to my eyes being the right match. I had to change my approach from being @FillFactor != 0 to being Policy should be @FillFactor = 0 and it would pass, else it would fall. I was thinking in reverse. MSDN indicates to be aware that: IMPORTANT! The functions that you can use to create Policy-Based Management conditions do not always use Transact-SQL syntax. Make sure that you follow the example syntax. For example, when you use the DateAdd or DatePart functions, you must enclose the datepart argument in single quotes.
Thankfully, I found that there were a lot policies already presetup by Microsoft. The default location I had for the 2016 installation was C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Policies\DatabaseEngine\1033 . You can navigate to these by right clicking on the Server Group you want to evaluate, and then Evaluate Policies > Choose Source > Files > SQL Server Best Practices folder > Database Engine > 1033 > Rule to explore
I can see the value for enforcing policies across a vast number of servers, and monitoring for compliance. For my environment, primarily dealing with a lot of developer sandboxes, the effort this requires is a bit too much. For my scenario, I'll probably stick with some home grown queries, powershell SMO checks, and the awesome OmniCompare tool that is getting better and better each iteration. A previous article I wrote discussed the functionality of this tool here: OmniCompare: A Free Tool to Compare SQL Server Instances
Creating tracks at home can be very intensive in time, so I'm always looking for a better way to bring a larger sound to a track with less effort. One of the big gaps for me has been the drum parts. I've evaluated a few options, and finally dived into BFD3 for this latest project that is a post-rock style track. This was my first time diving into BFD3 for a full song, so I had a bit to learn. Disclaimer: BFD3 generously provided me with a copy to evaluate. This doesn't affect my reviews, as I just love dig in and recommend good software!
I was using this in Presonus Studio One. I created a drum track and then started to explore. I started my exploration based on using the Groove Editor, as I wanted to benefit from the library of preset grooves that were already created, and use these with some modifications to be the drum parts for me song, allowing me to hopefully create the track I wanted quickly. There is a lot of functionality I'm not going to dive into, such as the vast array of microphone adjustments, compression options, and other mixing options. My focus is going to be as a songwriter, how could I use this to help me generate a believable drum track for my song without it being an ordeal.
One thing that was really easy to do with BFD3 was modify the kit configuration. I liked the sound of Pork Pie Kick for this song, so I just played a groove, and then switched it over with the checkbox (on hover).
The Grooves section provides a preset set of associated rhythms to allow you to easily prepare a song based on related rhythms. This is ideal for helping you create a song quickly by finding associated rhythms.
This is really important to a natural sounding drum part. You definitely do not want everything at the same velocity as no real drummer would do this. There are some easy ways to adjust the velocity as well as the humanization of the rhythm in both Presonus Studio One 3 and BFD3 plugin. In this example, I adjust it in BFD3 directly.
One other cool feature for quickly pumping out some grooves is the ability to "paint" the rudiment you've selected. This means you could easily pick some drag paradiddles on the snare without having to click each point in time. I found this incredible helpful for creating some interesting grooves.
I tried the BFD3 Eco, but due to wanting flexibility to edit the grooves and do more tweaks, the Eco version was not a good fit for for me. If, however, you are looking for an experience that is more groove oriented, with less tweaking/adjustments, then you should consider starting with the Lite version. If you are looking for the full flexibility then you might want to evaluate the full one instead. FXpansion indicates on their support page in the comparison on the differences that:
What are the differences between BFD Eco and BFD2/BFD3? BFD Eco is optimized for ease of use and fast results - it is deep enough for deeply sculpting drum sounds into all manner of shapes but is streamlined enough so that you won't be overwhelmed with options. It has less detailed sounds than BFD2 and BFD3 but is much more light on system resources. BFD2 and BFD3 contain far more control over each aspect of each kit-piece as well as a configurable mixing engine with custom aux channels and much more. They also feature full editing of Grooves, more control over exports and many other features too numerous to list. FXpansion Support Article
The power and flexibility of this software is pretty amazing. I've only touched on a small fraction of what it is supposed to be capable of, as I'm using a lot of the simple functionality to "paint" some rhythms. I was a little disappointed in the initial groove library as far as the single 6/8 groove test goes, as it felt more difficult to get a groove I liked than when I demoed Ez Drummer
However, the flexibility in editing with the easy humanization and editing of the kit made up for this. I think the vast sound library of plugin packs is what is designed to expand this, so I'll probably cover some of those later to evaluate if they fill in the gap on providing a even larger variety of preset grooves for the wannabe composer. Note that the library is around 40GB for the initial load, so my isolated 6/8 groove test isn't reflective of the rest of the available library. There is a lot to work with, and the expansion libraries seem to be pretty vast in number.
Another area that would be awesome to enhance is providing something similar to "song finder" that EzDrummer 2 had, where a certain rhythm was tapped, and related matches found. EzDrummer 2 then provides something similar to Grooves where the song structures for verse, chorus, bridge, etc are laid out. I'm not partial to the format that EzDrummer used, I actually prefer the Groove library format of BFD3, but I sure would love seeing the search capability added to help quickly match grooves fitting the feel you are trying for. I'm pretty happy with the documentation and support, as they have a wide range of well made videos showing demonstrations on the product, how to use, etc. I look forward to incorporating this into my workflow and going through some more training videos to better understand a solid workflow, as I'm just starting to wrap my head around it, along with learning how to navigate Presonus Studio One
Check out a trial if you want to give it a shot and post some feedback below on what you think. Remember, I'm not an affiliate, or getting paid for anything on this, I just like reviewing and referring folks to good software. Good luck!
I had some cross database comparisons that I wanted to simplify, but ensuring the collation matched. The amount of objects that I would have had to drop and recreate was a bit daunting, so I looked for a way to migrate the database to a different collation.Using the Red Gate toolkit, I was able to achieve this pretty quickly. There are other methods with copying data built in to SSMS that could do some of these steps, but the seamless approach was really nice with the SQL Toolbelt.
Note: I'm a member of Friends of Redgate program, and am provided with licenses for testing and feedback. This doesn't impact my assessments, as I just love finding good tools for development, regardless of who makes them!