Red Gate Software SQL Data Compare 3.4

Red Gate Software recently released SQL Data Compare 3.4, a companion product to SQL Compare.You can use both products together to compare and synchronize data in Microsoft SQL Server databases, but SQL Data Compare can synchronize the actual data in two databases. Additionally, you can use SQL Data Compare to troubleshoot data replication problems, test applications, and verify that data migration to production was successful, and it simplifies database development.

Installing SQL Data Compare and running the setup program was simple and straightforward; installation took just a few seconds. The application let me perform a simple comparison, or it gave me the option to select 1 to 4 comparison criteria: Identical records, Different records, Missing records (not found in the target database), and Additional records (found in the target database). I compared two remote versions of Microsoft AdventureWorks—a database containing 93 tables and views—on my 2.8GHz HP laptop (equipped with a 32-bit AMD Athlon processor) and found that SQL Data Compare ran surprisingly fast; the comparison finished in less than 40 seconds. The comparison results display in the SQL Data Compare summary window, which Figure 1 shows.

The top half of the SQL Data Compare summary window displays the status of all of the objects compared, and the bottom half of the window lists the specific differences found in each table or view.The tabs at the bottom of the window let you display each type of data difference. SQL Data Compare lets you optionally synchronize any or all of the data changes. To exclude an object from the synchronization, enter a check box in the Synchronize column next to the object name in the top half of the summary window, as Figure 1 shows.

Click the SQL tabs at the bottom of the window to see the SQL statements that will perform the synchronization.The SQL tab with the blue arrow shows the SQL that will update the target to match the source, and the SQL tab with the red arrow shows the SQL that will update the source to match the target. To synchronize the databases, click the Synchronize icon on the toolbar to launch the Synchronization Wizard.You use the wizard to select the direction of the synchronization and review the resulting SQL script.You can run the synchronization script directly from the Synchronization Wizard, from the Query Analyzer, or from the SQL Server 2005 Query editor.

SQL Data Compare works best if the databases you compare have like schemas. If the schemas aren't identical, SQL Data Compare attempts to match common columns by name (to make sure that it is comparing similar data). SQL Data Compare can also maintain referential integrity by dropping foreign keys and recreating them during the synchronization process. However, one limitation is that when the application compares two tables or views, it requires that each database have a primary key.

SQL Data Compare has no limit on the size of the databases that it can compare, but performance speed can be affected by the capacity of the system it runs on, the size of the databases it compares, and whether the source and target database are in use.

SQL Data Compare is a very useful tool for both SQL Server database administrators and database developers, and if you haven't used it I recommend that you try it out. Red Gate Software offers the option to download a fully functional, 14-day timed trial of SQL Data Compare and offers a variety of product bundling options.