I first saw a reference to SQL Compare in a SQL Server newsgroup post back around
the summer of 2000. We were in the process of converting hundreds of MS Access queries
into SQL Server stored procs, and development was fast and furious. Although we tried
to consistently document schema changes, we invariably missed things. After using
the trial version of Red-Gate’s SQL Compare
for only a few days, we ordered the full version. This article explains why.
What is SQL Compare?
The concept is simple: Compare everything in database A with everything in database B.
Find all the objects (Tables, Views, Procs, etc.) with differences and generate scripts to
synchronize the database objects.
Fact is, most SQL Server DBAs know how to extract this kind of schema information from
the SQL Server system tables, and there are scripts available out there (even here at
SWYNK) that will get you at least part way there. So, why even consider spending money
for a tool like this? Why did we decide to shell out the
$195 for a single user license?
The answers are simple:
- Ease of use
- Automatic script generation
- HTML Reporting
As we consider how SQL Compare delivers on these points, you’ll notice numerous links
to corresponding screen shots. I’ve separated them off from the main text of the article
as it was necessary to make rather large images in order to accurately illustrate what SQL Compare can do. A picture really is worth a thousand words and the screen shots show how simple this tool is to use, so I encourage you to spend the bandwidth and give
them a look.
Ease of use
The utility has only one wizard, which is used to select databases for comparison. While you
can register databases separately, the wizard provides a “one-stop shopping” approach to
simplify the process, as all good wizards should.
Select Servers, Databases and Objects to Register
Simply supply the server, database and login credentials for the “First” database, and
then do the same for the “Second”. There’s an Advanced button that allows you to filter for
specific database objects if you want to speed up the registration process. In previous
versions, some users complained that the registration process was slow. It never bothered
me because I realize that analyzing a large database will take time. All the same, the folks
at Red-Gate listened to their users and the process is now much faster in the latest release.
Once you’ve registered at least two databases (you can register as many as your available disk
space allows for), you’re ready to perform the compare. You can launch a compare by clicking on
the Compare Databases button or by selecting Compare from the Database menu. You are then
prompted to select two of your pre-registered databases.
Launch Comparison of Registered Databases
The compare takes considerably less time than the registration, and once it’s finished, you are
presented with a list of all database objects. The Action column shows the comparison status for
each object. An equal sign (=) indicates that they are identical. A single right arrow or single
left arrow indicates that the object exists in one database or the other, but not both. Finally,
the most important status is a double arrow, as shown in the next screen shot.
Overview of Complete Comparison Results
The type of object is also listed, be it a table, view, stored procedure or other object. You
can filter the list to remove items you’re not interested in inspecting, greatly reducing the
list at times. Another way to reduce the list of differences is to modify the options used
when registering and comparing objects. The link below displays the screen shot of the options
dialog. I recommend that you opt to ignore spaces, tabs and Cr/Lf white space.
Settable Properties in Options Dialog Box
Automatic script generation
The next step is to examine the script for the object itself. Once you have filtered your list
to the object of interest, simply double-click on a list entry to bring up the script window.
The interface is similar to most file comparison tools, such as the one that comes with Microsoft
Visual Source Save or the WinDiff utility. Text is color-coded to indicate if it has been added,
removed or changed. The interface includes checkboxes for further filtering the output, removing
for example, permission scripts, indexes, triggers, defaults and more.
Results of Stored Proc Comparison
The second tab of this interface displays the SQL script required to convert the object in the
first database to the state of its corresponding object in the second, or, if preferred, you can
elect to generate the script to do the reverse. If you want to take a more “hands-on” approach,
as I usually do, you can simply cut and paste the script into your own SQL document of pending
changes. The interface permits copying the entire script to the clipboard with the press of a
single button, saving the script as a file or selectively copying only the text you need as you
would from any text document.
I have to say that my favorite improvement to SQL Compare is the HTML Reporting function. Though
launching it is a little confusing (it’s a submenu of the Database|Print main menu), the output
generated is of great use. As shown in the screen shot link below, you get the same intuitive comparison
information available in the UI, but in a format that can be published. Since I was the only DBA
with a copy of SQL Compare, I could run the comparison and publish the output to HTML files on my
local web server, thus exposing the latest compare info to the entire DBA team. Now that’s cool.
HTML-XML Comparison Report
Although there are several features I haven’t touched upon, I can’t conclude without mentioning
the Compare Workspace itself. After registering databases, you can examine the objects of any
registered database by simply walking through the tree of objects in the workspace window. (See link below)
After selecting an object, you can choose to view its dependencies from the right-click menu.
SQL Compare Results Workspace
What’s the verdict?
No doubt my bias in favor of this utility is obvious, but it’s well deserved. This simple comparison
tool has saved me hours of debugging time and has helped me to catch irregularities in my schema.
If you are developing SQL Server databases in a “fast and furious” environment (and who isn’t these days?), then I strongly
recommend that you take a look at Red-Gate’s SQL Compare.