Comparing and Synchronizing Two Databases

This worked example demonstrates a basic comparison and synchronization of two SQL Server databases.

In the example, the Magic Widget Company has a SQL Server database running on a live Web server. This database contains a number of tables, views, stored procedures, and other database objects. The Magic Widget Company's development team has been working on an upgrade to their Web site. As part of this upgrade, they have made a number of changes to the structure of the database. They have already transferred the changes from the development server to a staging server, but they now need to transfer the changes to the production server.

You can follow the example on your own system. You will need access to a SQL Server to do this.

Setting up the databases

The worked example uses the following databases:

WidgetStaging is the staging database
WidgetProduction is the production database

To create these two databases on your SQL Server:

If they already exist, delete the databases WidgetStaging and WidgetProduction from your SQL Server.
Click here to view the SQL creation script for the databases.
Copy the script, paste it in your SQL application, and then run it.
The databases and their schema are created.

Setting up the comparison

If you have not yet started SQL Compare, select it from your Start menu; if it is already running, click Comparison Projects.
On the Comparison Projects dialog box, click New.
The Project Configuration dialog box is displayed.
Ensure that the Data source type is set to Live database.
In this example, we will compare live databases; you can also compare snapshots that have been created using SQL Compare and scripts folders containing object creation scripts.
Type or select WidgetStaging in Database on the left side and WidgetProduction in Database on the right side.
If the databases are not displayed in the Database lists, right-click in each Database box and click Refresh, or scroll to the top of the list and click Refresh.
Click Compare Now.
SQL Compare displays a message dialog box that shows the progress of the comparison.

If you select the Close dialog box on completion check box, SQL Compare closes this message dialog box automatically the next time that you run a comparison on a project. For this example, leave the setting as it is.
Click OK to close the message box.

Viewing the comparison results

The comparison results are displayed in the main window.

In this worked example, the comparison results are grouped by:

	objects that exist in both databases but are different
	objects that exist in WidgetStaging but do not exist in WidgetProduction
	objects that exist in both databases and are identical

To view the objects in a group, click

You can view a side-by-side, color-coded listing of the differences in the creation SQL, by clicking an object. For example, if you click WidgetPrices, you can see the differences for the table WidgetPrices.

You can print a summary of the comparison results by opening the File menu and clicking Print. You can export the comparison results by opening the Tools menu and clicking Export Comparison Results.

For full details of how to use the comparison results window, see Viewing the Comparison Results.

Selecting the objects to synchronize

To synchronize the databases, you first select the objects you want to synchronize. You do this by using the appropriate check boxes in the Include column.

For this worked example, select all the objects that differ.

If the Filter Objects pane is not displayed, click Filter Objects.

If you are using SQL Server 2005, a list of SQL Server 2005 object types is also displayed.
On the Filter Objects pane, ensure that all the object types are selected, so that all the objects are displayed in the main window, and are available for inclusion in the synchronization.
Click the close button to close the Filter Objects pane.
For each of the object groups, ensure that all the objects are selected by clicking All.
Note that you need to do this only for the following object groups:

objects that exist in both but are different

objects that exist only in (local).WidgetStaging

All the check boxes in the Include column are now selected.

Synchronizing the databases

When you have selected the objects to synchronize, click Synchronization Wizard.

Synchronization changes will be made to the WidgetProduction database. Confirm the direction in which the changes will be applied by clicking Next.

In this worked example, there are no dependencies. Click Next to generate the synchronization script.

The Summary page displays the following tabs:

Action Plan provides a synopsis of the script, grouped by command type, in the order in which the commands will run
Modifications provides a synopsis of the script, grouped by object
Warnings displays any warnings about inefficiencies in the script, or reasons the script may fail

In this example, SQL Compare displays a warning to inform you that it cannot use the ALTER TABLE command to change the IDENTITY column, so the synchronization script will rebuild the WidgetReferences table. Warnings are displayed whenever tables require rebuilding as these may be slow operations. Data in tables is preserved when tables are rebuilt.

You can display the synchronization SQL script by clicking View SQL Script. You can then save the script if required.

When you have looked at the script and warnings, click Next to go to the Confirm page.

You can choose either to run the SQL script from within SQL Compare or to launch your SQL application so that you can review the script.

In this example, we will choose to run the script, then compare the databases again to check the results, and save a copy of the synchronization script (the defaults).

Click Finish to run the synchronization.

A confirmation dialog box is displayed. Click Synchronize Now to continue.

SQL Compare displays a message dialog box that shows the progress of the synchronization.

If you select the Close message box on completion check box, SQL Compare closes this message dialog box automatically the next time that you run a comparison on a project or synchronize databases. For this example, leave the setting as it is.

Click OK to close the message box.

SQL Compare then re-compares the databases, and a message dialog box shows the progress of the comparison. Click OK.

The databases are compared and the results are shown in the main window. In this example, all objects are shown to be identical, confirming that the synchronization has been a success.

	objects that exist in both but are different
	objects that exist only in (local).WidgetStaging