Figure 16. Step 2 of the Synchronization Wizard: Dependencies.
In Step 2 (figure 16), dependant objects will be displayed if there are any associated with the objects you selected to synchronize. Click Next to proceed to the summary.
Figure 17. Step 3 of the Synchronization Wizard: Summary.
In the Summary, Step 3 (figure 17), the Action Plan tab provides a synopsis of the script, grouped by command type and in the order in which the commands will be executed. The Modifications tab provides the script, which is grouped by object. The Warnings tab provides information about inefficiencies in the modifications script that might cause the synchronization process to fail.
I would suggest that you refer to Warnings in order to find out if there are any real showstoppers to the synchronization process.
Figure 18. Step 4 of the Synchronization Wizard: Confirm.
Step 4 is to confirm that the synchronization process is ready to take place (figure 18). Clicking View SQL Script will display the synchronization script. There is a provision to save the script to a file. The same file can be executed in either SQL Data Compare or SQL Server Query analyzer. Click Finish to let SQL Data Compare complete the synchronization process. When synchronization is complete, a message will be displayed.
Final Word
There are a few conditions that need to be considered. Some may be unavoidable. Others can be rectified during the SQL Data Compare process.
By default, the first time you run a comparison for a particular project; all objects that differ are selected. You select the objects you want to synchronize by selecting the appropriate check box in the synchronize column on the comparison results.
SQL Data Compare version 5 includes the following revisions from previous versions:
- Complete redesign of the graphical user interface.
- Improved side-by-side comparison of column values.
- New interactive help information.
- Added support:
- For project management, including the ability to specify options for each project.
- For creating custom comparison keys.
- For mapping owners (or schemas), objects, and columns.
- For filtering rows using WHERE clauses.
- For forcing binary collation on strings using checksum comparisons on large volumes of data that changes infrequently.
I have extracted few counters about this information from the SQL Data Compare help file that are useful to know.
If you clear the project option Include identity columns, but you then use an identity column as the comparison key, the identity column is included in the comparison results.
If the data has not been synchronized then you may need to look at the following conditions/rules in the database that might restrict the synchronization process.
- Check whether triggers are defined on the tables. For instance, if you have a trigger defined on a table that inserts data into another table on INSERT, DELETE, or UPDATE, the data in the tables will change as the synchronization is run, which will cause unpredictable results. To avoid this, select the Disable DML triggers [project option] before you generate the synchronization script.
- Primary keys are defined on columns with differing collation order. This is affected if you compare tables that have primary keys defined on columns that have different collation order, SQL Data Compare may produce unpredictable results.
- Tables do not have identical schema, this is default behavior in any process to produce unpredictable or erroneous results.
SQL Data Compare may be unable to compare databases if there is insufficient disk space. SQL Data Compare uses temporary files when it compares the databases.
SQL Data Compare always warns you if it is unable to roll back changes.
Conclusion
I believe database administrators and developers who are struggling to perform data and comparison synchronization on a daily basis need a utility like SQL Data Compare. They would be helped even more with SQL Bundle, which includes SQL Compare Pro, SQL Data Compare Pro, SQL Packager Pro, SQL Toolkit, and DTS Package Compare.
This utility makes the job a lot easier to manage. I highly recommend downloading a 14-day evaluation version of either SQL Data Compare or SQL Bundle from Red Gate Software.
]]>