Keep Your SQL Servers Synchronized with SQL Compare and SQL Data Compare

Upon pressing the ­Compare button, SQL Compare collects all object information regarding the two databases. This is followed by an informational dialog box status indicating the results of the comparison step. Once this dialog box is by passed, the following comparison results screen is displayed:

As you can discern, the screen is divided into three panels. First and foremost, the top panel displays command options, the databases being compared (color coded), as well as a grid with four columns that can be sorted by pressing on the column headers. The color coding of the databases are good visual indicators and provide guidance of the databases being compared. The grid columns list the following headers:

1. Type - Lists the object type (i.e. table, stored procedure, etc) for the databases
2. Name – Lists the object name.
3. Status – Synchronization status indicator flag. The meaning of each indicator is as follows.

4.    Synchronize – Checking this checkbox selects the objects to be synchronized in the direction as noted by the synchronization Status (#3 above) indicator.

Selecting one of the rows in the top panel automatically changes the script displayed on the bottom left hand panel. This “script” panel displays the Creation SQL Scripts, and the object synchronization scripts generated by SQL Compare (again, refer to figure 2.0). The bottom right hand panel contains the second database being compared. Since I decided to begin with a blank database object, this panel is blank at this time in the synchronization process. Pressing the “Synchronization” command on the top panel initiates the synchronization process.

However, before the process begins, the following directional dialog screen is displayed:

As noted above in figure 3.0, radio buttons are available to select the direction (i.e. source and target databases) in which to perform the synchronization process. This is followed by the Synchronization script screen wizard as shown below:

This is the actual SQL script file that will be executed during the synchronization process. As noted on this screen, there is a “Save script” option. Since these are standard SQL script commands, the script can be saved for execution at a later time from SQL Query Analyzer. Or, as I decided, the scripts can be executed directly from SQL Compare. Pressing the Next button initiates the script execution and results in the following dialog box.

After pressing the Finish button, a ‘warning’ dialog box provides you with an opportunity to cancel the synchronization process. Proceeding past the warning will result in the database object synchronized as the following screen shows: 

 As can be clearly seen, the top panel “Status” indicator has now changed to “=” to reflect that the two objects are synchronized. Also, notice that the bottom right hand panel (which was once empty) now contains the scripts that were synchronized.