Compare and synchronize databases with 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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in July 2012
homegrown
scripts floating around. One tool is SQL Compare from
Red Gate Software
Limited.
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.
Figure 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:
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).
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.
ABOUT THE AUTHOR
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation