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
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.
Screen Shot: 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.
Screen Shot: 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.
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.
Screen Shot: 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.
Screen Shot: 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.
Screen Shot: 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.
Screen Shot: 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.
Danny Lesandrini's latest product review takes a look at a useful comparison tool that has saved the author hours of debugging time on many occasions. If you are developing SQL Server databases in a 'fast and furious' environment (and who isn't these days?), Red-Gate's SQL Compare is highly recommended.