Thank this author by sharing:
By Andy Warren,
2005/01/14 (first published: 2001/06/06)
SQL Compare by Red-Gate is a product to let you compare two SQL Server databases and generate a
script to sync any differences in objects (tables, procedures, even users and roles). This review
is based on v1.51, running in the evaluation mode (no license key entered). My goal in this review is
to give you a good overview of how the product works so that you can decide whether to invest
your time in evaluating it.
Red-Gate offers a 14-day evaluation download, all they require is your name and email address -
pretty quick and easy. The download itself is available in both MSI and non-MSI versions, I chose
the just over 4 meg MSI. I always like to see MSI packages (the Windows installer technology) rather
than the older setup.exe, it's a good sign that the author has kept up with technology. The
install went smoothly.
To use the product, you choose two databases to compare - which can be on
different servers. For my
testing I compared a local copy of the SQLServerCentral database with the live one running on our
host server. This was done over a RoadRunner connection and performance was good. It offers a
number of options before you begin the compare to let you reduce the number of objects - a great
feature, since there is no reason to look at ALL the objects in a db if you know that you just
need to sync a change to a stored procedures (or whatever!).
The program works by scripting out the objects in each db, then first presenting a high level
view of which objects do not match (either because the text of the script for that object is
different) or because it exists on one server and not the other. This screenshot shows this
You can then either select the objects you want to sync (the checkbox in the left most column)
and generate a script, or you can drill down into each object by double clicking - see the next
pic for an example:
As you look at the two scripts side by side, you may see differences that are not really
differences. For instance, it may be just a difference in the case of the text, or it could be
the comments that are different. In some cases those may matter, in others you may not care. SQL
Compare offers quite a few settings to let you tailor how the compare is made. One option I
thought was pretty nice was one to exclude fill factor from index compares - in most cases I
would not want to change my index if this was the only difference.
Once you've adjusted your options and maybe excluded items for the object that you don't need
(permissions, triggers, indexes, more..), you can click a tab to look at the migration SQL - the
change script. This gives you a chance to see exactly what you'll be executing for this
particular object. The header comment is superb. It tells you which is the source and destination
server, recommends that you review before running and that you run the script in pieces - fair
diligence considering what the scripts will be doing!
I was able to come up with only a few complaints during my evaluation. The biggest is that when you're
running the program, it shows up in the taskbar but not in the Alt-Tab sequence. Being a big
Alt-Tab user, this was a major inconvenience! Another is that while the comments in the migration
script encourage you to run the changes a piece at a time, the program doesn't give you an easy
way to generate scripts to match this advice. It's either one big script, or you have to generate
them one at a time.
Overall I found the program to be robust AND easy to use. A single user license is $95, a site
license is $450. Is it worth it? Yes, without a doubt. There is no way I could come close to
building this tool for $95. And at that price, the savings from one to two uses would easily pay
for the purchase. This is a good tool for the toolbox! If you're a current user of SQL Compare or
evaluated the program after reading this review, click the tab below to add your comments - share
DISCLOSURE: Red-Gate provided me with a licensed copy of the product to evaluate and they are
also donating a licensed copy to SQLServerCentral.com to be given away to a reader. At the time
this was written Red-Gate was not an advertiser on SQLServerCentral.com.
Scripting Objects Including Permissions
Automated SQL script for comparing two tables.
Compare the logins and users on two instances and generate a TSQL script to make them the same.
Script object Design Script does not open up script editor
Compare two databases and create script to use to update one database
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.