Monday, February 1, 2021

SQL Server Data Compare - comparing large databases

Comparing the data from two SQL Server databases is kind of like comparing two text files to each other, so it should be easy, right? No, it is not easy. The focus of this article is just one of the challenges - the size of the databases being compared. Here is what a data compare tool, like xSQL Software's Data Compare for SQL Server, should be able to do: 

  • In order to compare the data it first needs to pair the tables and views and determine what comparison key to use for each table/view in order to pair rows from both sides with each other
  • Next, read the data from both sides (imagine millions of rows on each table), compare the data and keep track of the differences
  • Display the data differences in an easy to navigate manner- the user wants to see what's different between Server1.Table1 and Server2.Table2. Again, remember we are talking potentially millions of rows for each table pair so just slapping a data-grid on a window is not going to cut it. 
  • Generate a synchronization script which can get to be very large if there are a lot of differences between the databases. 
  • Execute the synchronization script on the target database. Once again, if the script is very large, executing it on the target is not a trivial matter. 
Except for the pairing of the tables step which is not affected by how much data you have to compare, all the other items in the above list are challenging to handle since your resources are limited. Many data compare tools out there will quickly demonstrate this challenge - as soon as you try to compare two relatively large databases they will crash or just keep "spinning the wheel" forever. We have invested considerable amount of effort into designing and building a data compare tool that efficiently handles any size database, restricted only by the disc space available. Our data compare tool seamlessly serializes data into disc, loading the data into the main memory only if and when necessary, and executing the synchronization script against the target database in manageable chunks the size of which is configurable by the user. 

Furthermore, to ensure an efficient comparison, our Data Compare for SQL Server, allows you to both exclude certain columns from the comparison and use row filters to compare only rows that you want to compare (more on those later) thus significantly reducing the workload. 

Download our Data Compare for SQL Server today. 

0 comments:

Post a Comment