If you are a professional SQL Server DBA, then at some point in your career, you’ve had (or will have) the need to perform the migration of data. Whether it is for an internal deployment within your environments or because you are seeking to modernize your database infrastructure (especially nowadays with so many cloud offerings). When such migrations take place, you want to be 100% sure that you didn’t leave anything behind (assuming that you want to lift and shift your data, of course), and SQL Data Compare is a wonderful tool that can help you walk that path beautifully.
Installation
You can download dbForge Data Compare by clicking on one of the “Get Trial” buttons. However, I highly recommend clicking the one that says “dbForge SQL Tools Professional Trial” because that will also include the SSMS extension, which is totally worth it.
Note: From this point forward, I will be using the stand-alone version of Data Compare.
Once you open Data Compare, this is what you will see:
Since we don’t have anything set up, because it’s the very first time that we’re opening Data Compare, then let’s click “New…”.
It will take us to this screen, where we should specify the source and target information to connect to the respective databases that we wish to compare.
When you click the “Type” dropdown, it will give you the option to compare a database, a backup file, or a set of scripts. By the way, you are not limited to comparing the exact same type for source and target, and you can perfectly compare a database against a backup file.
Let’s go ahead and try the database option to see what output we get:
You’ll see a bunch of options to configure the comparison that will be carried out. Make sure to take the time to go through all of the available options so that you can customize it to your own particular case.
Following that, we get the option to specify the objects to compare:
This is what you get after you click on “Compare”:
By comparing the data using the primary key on each side, it will tell you the differences it encounters, something I can already find very useful for double-checking data migration scenarios instead of relying on either a tedious manual process or a time-consuming custom-developed routine.
Not only that, if you find differences between the source and the target, you can hit that green arrow to synchronize the data, even cooler!
That alone would’ve made my day and would be enough, but let’s continue to explore the other options to see how it operates. To spice up things a bit, I will delete some records in my target table, generate a database backup, and see what Data Compare does.
As you can see, It gives me this error message because the product currently supports the comparison of backup files of SQL Server instances lower than 2014. Since I only have a 2017 and a 2019 Developer instance, then I’m not able to show you this particular option. However, digging up a bit, I stumbled upon a forum discussion (in Devart forums) where it is mentioned that the product will eventually support comparing backups of higher SQL Server versions. Hopefully, this will come sooner rather than later, and I’m totally sure it will because I totally have faith in Devart because of what they’ve accomplished so far because in terms of products for SQL Server DBAs, it is nothing short of amazing!
Moving up to the next option, I’m going to try comparing a generated database script against a database in one of my instances. For this, I have simply just used SQL Server Management Studio to generate a single script file of my source database and have placed it in the directory that will be entered in the options panel.
*Additionally, make sure to select the correct collation so that you avoid any warnings up ahead. Or you can tick the “Use target database properties” checkbox if that’s good enough for you.
Here we will see pretty much the same window showcased in the previous demonstration, so the same advice applies here: make sure to take your time to understand and enable/disable all the options that perfectly adhere to your own particular use case.
In the next screen, we will see the detected objects to be compared, so here you can choose whatever it is that you are targeting.
This is the output I got, so it basically detected that in my script, there was a table also named “table1”, and compared using that.
One very cool feature I forgot to bring up is that you can export the comparison data into HTML, CSV, or XLS format, which is highly convenient for you to have to share with your superior, a developer, or simply to document your work.
Whenever you are ready to proceed with the synchronization phase, then you’ll have the highly convenient option to perform a database backup prior to the synchronization. See, things like this are what make me have very high hopes in Devart when it comes to the development of new products/features and/or fixes in current products; this is because the attention to this particular detail is second to none; I salute you Devart team!
After all, has been said and done, you’ll have a script of the actions applied at your disposal and the generated backup of the affected database in case things go south ;)
Final thoughts
● Synchronization of data can be a tough thing to keep an eye on if not done properly. However, dbForge Data Compare simply makes it easy and convenient for you.
● I remember several years ago, working on a database migration project, I had to go through many hoops to compare the source against the target. I wish I had a tool like this to help me with my workflow; I can now appreciate the value it gives to the professional DBA, and you definitely have the opportunity to give it a shot.
● This was a high-level approach to the tool. If you keep digging deeper, you will find more useful stuff, like automating operations from the command line, which basically allows you to integrate the tool with your DevOps daily operations.
● You can find way more information in Devart’s official documentation center for Data Compare.
Top comments (0)