Wednesday, February 1, 2012

One-click SQL Data Compare

Comparing and synchronizing data in two SQL Server databases is most of the time a fairly straight forward process: you select the databases you want to compare; xSQL Data Compare maps the tables and views automatically, identifies and selects the comparison keys and performs the comparison at the end of which it shows you the results on the screen. At that point you can click a button and generate the synchronization script which, if executed on the target will make the target the same as the source. So, the interaction required is minimal.
However, there are often times when the process is not very simple. Here are some possible complications:
  • Two identical tables might be owned by different schemas in both databases so with the default settings xSQL Data Compare will not map those two tables with each other. So, you either have to change the mapping rules to ignore the schema or you have to manually map those two tables with each other;
  • You might have tables that have no primary key defined on them and no unique constraints that can be used as comparison keys. In these cases you will need to drill down to those object pairs and manually select a comparison key that can be a combination of columns from this table;
  • You might want to completely exclude some tables from the comparison;
  • You might want to tweak the behavior of the comparison engine by adjusting the options to your needs;
For a relatively large database you might spend hours preparing the comparison. That is where the xSQL Data Compare’s “comparison session” saves the day – once you go through the comparison configuration process every "bit" of the configuration is stored in a session. Next time you launch xSQL Data Compare you will see a box on the main window for that session with a "Launch" action link – one click on that link and the comparison process will be done for you - many hours saved.

xSQL Data Compare supports SQL Server 2008 R2/2008/2005/2000 and it is free for SQL Server Express – download your copy from:


Post a Comment