My first real production experience with SQL Server came
when I upsized a popular event management system written in Access 97. Since
Access Data Projects (ADPs) had not been released yet, we had to use Enterprise
Manager and SQL Query Analyzer to maintain the database schema. With the advent
of ADPs, SQL Server objects may now be maintained from the Access environment
but there remains no convenient way to log individual changes to tables, views,
stored procs and other database objects.
Visual Source Safe is a good way to track changes but the
nature of Access (all objects being housed in a single file) makes using it a
tedious chore. We had abandoned VSS years before and were reduced to managing
versions of our SQL Server database with a folder full of SQL script text files.
I know, I know, we should have at least used Source Safe for
our SQL scripts but since it wasn't in place, and since there were only 2
developers, we forged ahead without any source control in place. That was a
mistake, of course, and soon we had multiple versions of production and
development databases lying around with no easy way to identify which one
contained what modifications.
Around that time, I was trolling the SQL Server newsgroups
for help with some T-SQL issues and I saw a post with a link to a utility for
comparing SQL Server databases. I went to http://www.red-gate.com and downloaded
a free beta copy of SQL Compare from Red Gate Software. I could not believe how
simple it was to use and how thoroughly it solved my database maintenance
problems. That was five years ago and I am still a fan. The only thing that has
changed is the product: it has gotten better.
Click for larger image
What is SQL Compare?
SQL Compare is like WinDiff for databases and it is very
simple to use. Select the two databases you wish to compare (which, by the way,
do not have to be on the same server) and click the Compare button. The
comparison process is very fast in the latest version and when it is finished,
you are presented with a screen like that shown in figure 1.
Do not look too closely at the image or you will begin to
wonder about my object naming conventions. Rest assured this is not my style. I
inherited this database from a new client and have been asked to support it, fixing
bugs and adding enhancements off site. For example, the lower half of the SQL
Compare window (see figure 1) shows how the tblRBIBidsPending table in the left
database contains fields that do not exist in the right database. That is
because I added them to my development copy.
The upper half of the window displays a list of all objects
compared, as well as their synchronization status. The Status column indicates
if the objects in the two databases are identical, if one is missing from the
other, or if the object exists in both but differs in content. The interface
allows you to filter for one or more of these conditions, which makes the list
more manageable. You may also filter for specific types of database objects,
such as tables, views, stored procedures and the like.
All of this is great for identifying differences, but the
real power lies in the synchronization process exposed by SQL Compare. Remember
I said that the changes shown above were applied to a local development copy of
the database. These changes now need to be applied to production. How can SQL
Compare assist with this process?
Notice in Figure 1 that the lower window includes two
additional tabs: one provides the SQL script to synchronize changes from the
left database to the right and the other tab shows the script that propagates
the changes the other way. From these tabs, you can copy and paste the change
script into a text file.
Alternatively, if you are on the network and attached to the
target database, you can simply click the Synchronize button and a wizard will walk
you through the process of migrating changes. The wizard even allows you to
load the change script into Microsoft SQL Query Analyzer where you can examine
the script, tweak it, save it to disk and run it yourself.
The SQL Compare interface also allows you to filter out
certain types of differences, such as white space and comments, which may not
be of interest to you when it comes to comparing objects. In fact, 22 different
settings in the compare options dialog allow you to control the change script
Another new feature that makes the latest version a valuable
resource is the ability to save snapshots of analyzed databases. Once a
snapshot has been created, it may be used in comparisons, though it cannot be
modified with a change script. These snapshots can be saved as a history of the
evolution of your databases. These snapshots can be stored in your development
environment and uses as a baseline to production. Since they are simple schema
scripts, they take up very little room and they are a great way to save a
historical record of database changes.
Why invest in SQL Compare?
The thing I like least about writing product reviews is the
part where I describe the product, as in the section above. Truth is, the help
file usually does a far better job of explaining all its features than I ever
could. What I like most about product reviews is describing why the product is
valuable and identifying the target audience, those who will derive the
greatest benefit from it.
To appreciate the value of this product, you need to know
that it is a moderately priced utility, selling for $195 for a single user
license. SQL Compare is also sold with multiple license packs, support
contracts and bundled with other great Red Gate software, like SQL Data
Compare. (Check out their web site for complete pricing information.) I say
moderately priced because, on the one hand, two hundred bucks represents a lot
of pizza and designer coffee but on the other hand, it will pay for itself in
only 2 to 4 hours of billable contractor time. It is a good deal for the money.
Ok, so that is what it costs, but what is its value? What
will it bring to your business that you do not already have? Consider this:
A tool for creating a version history of your databases, saved as
The ability to compare your production databases against frozen
snapshots to see if anyone has "tweaked" the production code.
A utility to quickly compile a script for documenting and migrating
batch changes from development to production.
To be fair, some of this functionality already exists in
other tools you may be using, such as Visual Source Safe, Query Analyzer and
SQL Enterprise manager. With VSS, you can document changes, save script files
and compare them in a text editor. Enterprise manager allows you to generate a
script snapshot of your database and Query Analyzer is where you tweak and
execute those scripts. So, what are the benefits of SQL Compare?
Speed! It's fast. The code behind this tool was recently rewritten
for the latest version and it is as fast as lightning.
Ease of use! The above-mentioned tools do what SQL Compare does,
but not in one place and certainly not in one fluid motion, as does this
Customization! Some of SQL Compare's customization is available
in those other tools, and some is not. Try, for example, to ignore white space
and comments when comparing files in VSS. It is just not there and it is one of
the most important features to me as a developer. I do not want to be chasing
down lines of code that differ by a space.
Yes, it is a great tool, but is it right for you? It may or
may not be, depending on your development environment. Consider these
If your databases remain relatively static, then you do not need
If your IT department has strict change control and religiously
uses a source control program, then you may not need it, though you could
benefit from it.
If your development team codes furiously in a RAD environment
then, as far as I'm concerned, you can't live without it.
Clearly, my bias is showing. Red Gate's SQL Compare is a
product I have used for years and I love the latest version. Above, I mentioned
a sister product called SQL Data Compare. It works in much the same way but
instead of comparing schemas, it compares table content. That will be the
subject of a future product review. Trial copies of these utilities are
available at the Red Gate web site and I encourage you to download and try them
See All Articles by Columnist Danny J. Lesandrini