Quickly Compare and Synchronize SQL Server Database Contents with SQL Data Compare

In an enterprise-wide network environment, managing multiple versions of the same database is a common task for a database administrator. In some cases, you will need to synchronize the databases, the database objects, or the actual data either by using a third-party utility, or by using a traditional method, such as backup and restore, or the DTS Package Wizard.

SQL Data Compare by Red Gate Software is designed to help you quickly synchronize the data between databases, saving you time over other methods. This latest version introduces some new features to an already full feature list:

• Compare and synchronize database schemas or data.
• Migrate databases easily from SQL Server 2000 to SQL Server 2005.
• Install and upgrade packaged databases remotely.
• Backup, package, and distribute your databases more efficiently.
• Rescue and troubleshoot your databases.
• Automate common tasks and use its functionality in your applications.

DBAs who are struggling to synchronize databases on a regular or daily basis will find the job much easier to do with this reliable tool.

Process and Procedure

To get a start with the tool, you can create a project to compare the data between two identical tables, between two databases, or even between two SQL Servers. Click Comparison Projects and New to create a new project in Project Configuration (figure 1).

Figure 1. Project Configuration.

The Data Sources tab (figure 2) lets you specify the database details. Select the source and destination servers, enter the required authentication, and select the databases to compare.

Figure 2. Data Sources tab.

During the selection of SQL Servers to compare, if you are experiencing problems connecting to or selecting a SQL Server that is not running on the LAN, make sure you have specified the Client ALIAS correctly. For instance, if you are accessing the SQL Server via an internet connection, you may need to create an alias to the SQL Server using the TCP/IP protocol.

For this review, I have created a few tables and modified the data on the Northwind database on one of the two SQL Servers I have selected.

The Tables & Views tab (figure 3) gives you the opportunity to select tables and views to compare. To include all the objects in the comparison click All; to exclude all the objects for the comparison click None.

To match the rows within the two selected databases, you need a comparison key for each object. You can define an automatic comparison key if the tables contain a matching primary key, unique index, or unique constraint. In the case of views, it should contain a matching unique, clustered index.

Figure 3. Tables & Views tab.

By default, the first time you run a project all matching tables and views are selected for comparison. If the structure of the databases you are comparing has changed while you are working on a comparison project, click Refresh to update the Tables & Views tab.

The first time that you run a project all matching columns are also selected for comparison. You can select which columns to compare by clicking the appropriate Compare box (figure 4).

Figure 4. Compare columns.