When you are working as a professional SQL Server database administrator, there’s a very high chance that you get to deal with several SQL Server instances throughout all the development cycles within the business you are a part of. When this happens, it is a common thing to have dedicated environments to serve different purposes (e.g., Development, Testing, Production/Clients, etc.).
From time to time, it is a healthy practice to compare your database environments to see if there are any mismatches between them (assuming that, for instance, your Development environment must be pretty much identical to your Staging/UAT environment) to avoid any surprises from the development team that triggers the classic finger-pointing all the way to the DBA team.
In my case, I’ve tried to use tools like:
Schema Compare extension for Azure Data Studio: Even though it works just fine, I like to stick with SSMS since it is more useful to me as a production DBA rather than as a DB Developer.
Schema Compare for Visual Studio Code: For some reason, it has been quite buggy lately and has not been a very reliable tool for this purpose.
For those reasons, I’m going to give a shot at dbForge’s Schema Compare to see if I finally can have a reliable tool for my schema-comparison needs in my workflow.
Installation
You can get the dbForge Schema Compare (a part of SQL Comparison Bundle) by clicking on the “Get Trial”, as you can see in the screenshot below.
This is what it looks like the very first time that you open it, so I’m clicking “New…” right away:
After triggering a new schema comparison, you’ll be prompted with the information of the Source and Target databases that you want to compare:
If you click the “Type:” dropdown, you’ll see that the tool offers more options, so I’m going to select “Backup” just to show that the screen changes based on what you selected:
I’m going to stick with the “Database” option for simplicity, so by expanding the “Connection:” dropdown, we can see that only a “” option appears, so I’m clicking it to see what’s next:
You’ll see a “Connection Manager” window, and since there’s nothing in there, I’m hitting “New…”
You’ll see a screen that will require you to enter the basic information for your SQL Server instance. A cool feature that I find very useful is the categorization of the instance, for which the tool already provides some categories out of the box, but you can create your own to organize them based on your own criteria.
Once you have created your connection, we’ll end up in the previous screen, but I want to show you something extra that I found that the tool can do right away:
When you click the green arrow in the “Database” section, you’ll be given the option to create a new database in the instance that you have specified. I’ve never seen this option in any other tool, so that’s very cool!
These buttons give you the option to:
“Copy source settings to target”
“Copy target settings to source”
“Swap source and target settings”
If we explore the “Options” section, we’ll find a ton of ticks to enable/disable for our compare operation. You should take your time with these because each DBA will surely have different requirements based on the business and environments under his/her support.
*Additionally, you can either save the specific set of options as your defaults or go with the pre-selected ones that Devart provides out of the box.
For demonstration purposes, I have grabbed a full backup of a database I have around and restored it in a different instance. In that other instance, I have created a new view and modified a tiny thing in the only table there, just to see what kind of output I get:
So the tool will tell me the differences between existing objects on each side and also will tell me which objects exist only on one of either side.
Moving forward with the synchronization attempt, you can see that you have a few options before pulling the trigger; for instance, you can save a script file with what the tool would apply in the target instance, for post-analysis or whatever another purpose you might give it.
Here’s something I’ve never seen in either of the tools I’ve used so far: the option to perform a backup of the target database prior to the application of the changes. This is a feature that tells me that Devart really thought about its target audience, us the DBAs, which is something I can truly appreciate because things can go south at any given point in time.
Finally, you get a summary of the actions that will be performed:
And after applying everything, I went on to check if the backup was generated, and boom! Indeed.
Final thoughts
● This is a very robust Compare Database Schema tool for SQL Server that I would highly recommend to any DBA that has to perform such a task as part of his/her workflow.
● The tool is packed with a myriad of options that allow the DBA to tweak it to his/her own particular use cases.
● Since I also have installed the SQL Tools Professional Trial, a set of extensions for my SQL Server Management Studio were included, and guess what… Schema Compare is included in the bundle! That is a huge win for me because now I pretty much have everything I need within SSMS itself, without having to hoop around different applications.
I’ll be definitely checking this one out (and the rest, of course), but that will be in another post because I have already taken enough of your time, so thank you for your attention, and kudos to Devart for this excellent tool!
Top comments (0)