Problem solve Get help with specific problems with your technologies, process and projects.

Compare and synchronize databases with SQL Compare

Do you frequently need to compare or synchronize your databases? Read this tip by SQL Server expert Roman Rehak to learn the secrets of effective database synchronization using SQL Compare.

The need for database synchronization and comparison is fairly common and having the right tool to do it is important. There are several tools on the market to assist in this task, as well as some homegrown scripts floating around. One tool is SQL Compare from Red Gate Software Limited.

For more information on database synchronization

Easy how-to for database synchronization

Database synchronization on two separate active servers

IT administrators will benefit from database synchronization in several situations. During the database development process, you may want to compare the development database and the QA database and generate scripts to bring QA to the same level of quality as the development database. Similarly, once the QA process is finished, you often compare QA to production and generate database change scripts for execution when the new version of the application is ready for deployment. You may want to keep track of all changes and maintain change scripts manually, so even if you decide not to use this type of software for synchronization, you’ll be able to compare databases to make sure you didn’t miss anything.

The SQL Compare tool offers you a quick, easy way to compare selected objects or all objects in a database and produce a script that will synchronize them. Each comparison of database A with database B is treated as its own project. When you finish comparing and possibly synchronizing, you can save the whole set of selections as a project. The project saves things like source database, destination database and the comparison options you selected. So if you need to work with the same two databases in the future, you can simply rerun the comparison with the same options and filters.

When you use SQL Compare to start a new project, the first screen (shown in Figure 1) lets you select the source and destination databases.

Database SynchronizationFigure 1.

You can start a comparison at this point, but if you want more control over what you are comparing and what you want to ignore, select the Options tab and modify the options, as shown in Figure 2:

Database Synchronization

Figure 2.

One of my favorite features in SQL Compare is the vast selection of objects you can choose to ignore during comparison as well as the number of options defining what should be included in the final script. For example, if you want to compare a production database with a QA database, it’s handy to exclude permissions from comparisons; after all, it’s very likely that you use different logins and users in those two environments.

A good comparison tool should give you enough flexibility to include what you need, and only what you need, without much hassle and without having to modify the generated script. This is one area where SQL Compare really shines. It’s been around for years, has improved based on user feedback and now provides great balance in flexibility. I recommend that you define your most common set of comparison options and save it as “My Default.”

Once you’ve compared the databases, you’ll get results (see Figure 3).

Database Synchronization

Figure 3.

SQL Compare categorizes all objects into four areas: objects that exist in both databases but are not the same; objects only in the source database; objects only in the destination database; and identical objects in both databases. You can browse through and examine the differences. You can also use the filter on the left to eliminate specific types of objects from the view to make it easier to find something. Also, you can narrow down the list of compared objects and exclude individual objects from synchronization.

Once you have your filtering and object inclusion options in place, several things are possible. In my company, we email the results of the comparison to the development team together with any questions we might have and ask them to explain or provide comments on new objects. SQL Compare greatly helps with this task through its Generate Comparison Results Report feature. You can generate a report in Microsoft Excel, XML, simple HTML and interactive HTML. The last one is the most flexible since it uses JavaScript and dynamic HTML to allow easy browsing, expanding and collapsing. The report generator allows you to exclude identical objects or only include objects that you selected for synchronization.

To synchronize databases, just start Synchronization Wizard from the menu. The button on the toolbar remains disabled until you explicitly select at least one object for synchronization. The wizard gives you two options: (1) to create a synchronization script for you, or (2) to let SQL Compare execute the scripts directly into the destination database. But, as I always stress, you should never fully trust generated scripts.

Unless the database you are working with is relatively unimportant or you are sure you can quickly restore it from a backup, you should always save generated scripts and examine them. There are a number of concerns, and a visual inspection can prevent an incident;  the script may not do exactly what you wanted, or it could contain code for objects you don’t  want to create on the destination (for example, permissions or users).

The best scripting tool provides options, and each selected option means more code. Visual inspection of generated scripts will ensure that the code is what you wanted -- and only what you wanted.

Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He contributes to Visual Studio Magazine, SQL Server Magazine and other publications and presents at user groups and conferences in the U.S. and Canada.

Dig Deeper on Microsoft SQL Server Tools and Utilities

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.