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.