Red Gate Software SQL Compare 3.2

Although SQL Server ships with an impressive array of management tools, one notably missing function is the ability to compare versions of a database, track changes that have been made, and reconcile the difference between them. Red Gate Software's SQL Compare fills this important gap. SQL Compare lets DBAs or developers analyze the schema of two databases and can report their differences as well as synchronize the differences it finds. SQL Compare works with all database objects (including tables, views, indexes, stored procedures, and user-defined functions—UDFs) and all schema objects (including users and roles). The product is aware of database relationships and creates objects in the correct order according to their dependencies. After analyzing the source and target databases, SQL Compare produces scripts that you can execute to synchronize the database schema. SQL Compare is a SQL Server–specific product that requires SQL Server 7.0 or later. The product doesn't work with other databases such as Oracle or IBM DB2, so you can't use it to migrate databases between different platforms.

Installing SQL Compare is a snap. The product is available as an 8MB download from the Red Gate Web site (www.red-gate.com). Running the setup program installs the product in just a couple of seconds. You can install SQL Compare on your SQL Server database server, but a more typical installation runs from an administrative workstation.

Running SQL Compare is simple and straightforward. After you start SQL Compare, the software prompts you for the connection information for the source and target databases you want to work with. The available databases on each server appear in a dropdown list. After you select the databases, SQL Compare performs the initial comparison and displays the summary screen that Figure 1 shows.

SQL Compare's interface is simple and easy to read. The Status column in the top screen shows you the synchronization status of database objects. The equal sign indicates that the schema of the two objects is the same. A double-headed blue-and-red arrow in the Status column indicates that the schema of the two objects is different. A single-headed blue arrow indicates that the object isn't present in the database the arrow is pointing to. A checkbox in the Synchronize column lets you choose to exclude objects from the synchronization.

In addition to the summary, SQL Compare can also display a side-by-side view of the database schemas highlighting the differences, as you see in the bottom half of Figure 1. SQL Compare lets you synchronize objects in either direction and will generate a script that performs the syn- chronization. You can review synchronization scripts before executing them, and you can run the scripts directly from SQL Compare or save them and run them later from Query Analyzer. SQL Compare can also produce HTML- or Microsoft Word-format reports that show database differences and show the scripts that synchronize the various database objects. An Options menu, which you can access from the Comparison menu, lets you customize the comparison process, giving you the ability to ignore column order, comments in stored procedures, and collation order.

The comparison process is surprisingly quick. When I ran the comparison on the SQL Server 2000 AdventureWorks sample database, the comparison completed in less than a minute. Although I didn't encounter any problems while using SQL Compare, it's always a good idea to back up your databases before running any scripts that might delete data or drop objects.

In case you need to compare and synchronize data as well as structure, you can use Red Gate's companion product, SQL Data Compare, to compare data between two databases. Both products are available individually, and Red Gate also sells them as part of the SQL Bundle Developer's Edition, which includes a toolkit that lets you run the SQL Compare and SQL Data Compare products from the command-line.

Manually comparing and synchronizing the schema between two databases is a tedious process that can take hours. SQL Compare reduces the time to minutes. When you think about the value of a DBA or developer's time, you can probably calculate that the product basically pays for itself the first time you use it. And Red Gate offers a fully functional 14-day trial that you can download to test the product. SQL Compare is a product that should be in every DBA's toolkit.