SQL Data Compare Latest version: 7.0

Worked example: synchronizing data in two databases

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

In the example, the Magic Widget Company has a SQL Server database running on a live Web server. 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 content of the development database, which need to be transferred to the production database.

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 sample databases:

• WidgetDev is the development database
• WidgetLive is the production database

To create the sample databases on your SQL Server:

1. Click here to view the SQL creation script for the sample databases.
2. Copy the script, paste it in your query editor, and then run it.

   The sample databases are created and populated with data.

Setting up the comparison

1. If you have not yet started SQL Data Compare, select it from your Start menu; if it is already running, click  Comparison Projects.
2. 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 two databases; you can also compare a backup with a database, or with another backup.

3. In the Server box, type or select the name of the SQL Server where you created the sample databases.
4. Type or select WidgetDev in Database on the left side and WidgetLive in Database on the right side.

   If the databases are not displayed in the Database lists, right-click each Database box and click Refresh, or scroll to the top of the list and click Refresh.

   The copy buttons enable you to save time by copying all the settings from one side to the other; the copy buttons are available only when you have selected a database or backup file.

5. Click the Tables & Views tab.

   

   The Tables & Views tab enables you to select the tables and views you want to compare, and which columns you want to compare. The Tables & Views tab also enables you to specify the comparison key, which SQL Data Compare uses to match rows in the two databases. Note that by default, SQL Data Compare does not compare views.

6. Click the Options tab to include views in the comparison.

   

   The Options tab provides a set of advanced features that enable you to modify the behavior of SQL Data Compare during comparison and synchronization.

   You can find out more about a setting by moving your mouse over it. The information about the setting is displayed in the Description box.

7. Select the Include views check box, and then click the Tables & Views tab again.

   The WidgetPriceList view is now included in the comparison.

   Notice that for the WidgetPrices table, only 4 out of the 6 columns will be compared because the column names do not match.

8. Click the Remap Objects tab to include the rest of the columns in the comparison.

   

   The Remap Objects tab displays a list of table and view mappings. Mappings define which tables and views in the two databases SQL Data Compare can compare. For example, the WidgetDescriptions table in the WidgetDev database is mapped to the WidgetDescriptions table in the WidgetLive database. SQL Data Compare enables you to modify the table mappings. For example, you may want to compare tables with different or similar names.

   The Remap Objects tab also enables you to specify the schema (owner) mappings. You do this by clicking Map Owners on the Remap Objects tab. For this example, leave the settings as they are.

9. Click Partial for the WidgetPrices table to view the column mappings.

   

   A dialog box is displayed for you to edit the column mappings.

   

10. In the lower pane of the dialog box, click DateValidFrom and then ValidFrom, and then click Map to map the columns.

    SQL Data Compare moves the columns to the upper pane of the dialog box.

11. Map the DateValidTo and ValidTo columns in the same way, and click Close.

    The status for the WidgetPrices table is set to Mapped.

12. Click Save, and save your project as Project 1.

    If your comparison project requires a considerable amount of configuration, you may find it useful to try out different settings and save the settings in separate projects; to do this, use Save As.

13. Click Compare Now.

SQL Data 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 Data 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, and 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:

To display the comparison results in a single list, in the Group by box, select No groups; to display the comparison results in groups, select Type of difference.

To view the comparison results for each object group, click or click the object group bar.

The type of row difference is displayed:

The grid also shows how many rows of each type exist for each table or view. For example, the table WidgetPrices contains three rows that exist in WidgetDev but not in WidgetLive, one row that exists in both databases but has different values, and no rows that exist in WidgetLive but not in WidgetDev; All Identical shows that there are two rows that are identical.

To filter the tables and views, type your search criteria in the Filter box. Filter the list to display only the tables and views whose names contain Widget by typing Widget in the Filter box; in this example, 1 table or view is shown as hidden.

To display all the tables and views again, click the button in the Filter box.

To view detailed information about rows, click the appropriate box. For example, to view the rows that are different for the WidgetDescriptions table, click the box for the WidgetDescriptions table, as shown in the illustration.

WidgetID is the comparison key , which SQL Data Compare uses to match rows in the two databases. For the row where WidgetId is 2, the values for Short Description and Description are the same in both databases, so the data is displayed in gray text; the Picture values are different so they are displayed with the darker shaded background.

To view the value details double-click the value, or right-click and click Show Viewer. Binary Data values are displayed as links. Click the Binary Data link for the row where WidgetId is 2.

For tables or views that contain a large number of columns, you may find it useful to display the column values vertically so that you can see records more easily. To do this, click .

To display the column values horizontally again, click .

You can show the next or previous difference using the arrow buttons. Click the Contacts table, then click the All Rows tab, and then click , to display the first value that is different.

You can move through each difference in turn using the and buttons. To move through the differences for the current column, click or

To view the synchronization SQL for the Contacts table, click the SQL tab. You can view the synchronization SQL for other tables by clicking the table in the list of tables.

You can search the values by typing search text in the Find box. Click the All Rows tab ensuring that the Contacts table is selected, then in the Find box type Mitchell, and then click to find the first occurrence.

Synchronizing the databases

To synchronize the databases, you first choose the data that you want to synchronize by selecting the appropriate check boxes. You can select individual tables or views to synchronize, and for each table or view you can select the rows that you want to synchronize. By default, all rows that differ are selected when you run a comparison on a project.

For this example, select all the tables and their rows. Ensure that the check boxes are cleared for the WidgetPriceList view. You do this by clearing the All Different check box.

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

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

In this example, we will ensure that the WidgetLive database is backed up before the data is synchronized. Select the Backup database using check box. If you have installed SQL Backup on the SQL Server, you can select Red Gate SQL Backup instead. This example describes how to back up the WidgetLive database using the SQL Server native BACKUP command. For details about backing up databases before synchronization using SQL Backup, and compressing or encrypting the backup, see Using the Synchronization wizard.

Backup folder is set to the default backup folder for the SQL Server instance. In the box to the right of Backup folder, type the backup file name, for example WidgetLive.bak

To generate the synchronization SQL script, click Next.

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 table or view
• Warnings displays any warnings about unexpected behavior that may occur when you synchronize the databases

You can display the synchronization SQL script by clicking View SQL Script. When you view the SQL script, you can save the script if required.

When you have looked at the script, click Next.

You can choose to run the SQL script from within SQL Data Compare, launch your query editor so that you can review the script, or save the SQL script without synchronizing the databases.

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 SQL script (the defaults).

Click Finish to run the synchronization.

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

When the synchronization has completed and SQL Data Compare has re-compared the databases, a message dialog box is displayed. Click OK to close the message box.

The results are shown in the main window. In this example, all tables and their rows are shown to be identical, confirming that the synchronization has been a success. Notice that the WidgetPriceList views are also now identical, because the tables that they reference have been synchronized.