Software Review: SQLCompare

By Bill Graziano on 27 April 2004 | Tags: Tools

I've reached that horrible stage in a project. We're still doing development but the users are in an extended alpha/beta test of the software. And they don't like to lose the data they've entered. That means as I fix bugs and add new features I need to keep our development database in sync with our test database. I've been using Red Gate's SQLCompare to manage this and I thought I'd tell you a little about how it's going.

SQLCompare is designed to find schema differences between databases and make the changes to keep them in sync. Red Gate has two other SQL Server tools: SQLDataCompare and DTSCompare. SQLDataCompare is used to keep the data in two databases in sync. DTSCompare compares DTS packages, jobs and logins. You can buy all three together in a SQL Bundle for US$350. An individual license of SQLCompare is US$195. They have discounted options on both for multiple licenses.

Red Gate has a SQL Comparison and Synchronization Toolkit for sale. This appears to be the API for their tools. This should let you write your own customized front-ends using their comparison engines. These tools all require the .NET framework to be installed on your computer (but not the SQL Servers you're comparing). They also have a free 14 day trial you can download.

When you first launch the application you're presented with a dialog asking which two servers you'd like to compare.

I tested this earlier today on a larger system. It compared roughly 800 tables, 1300 stored procedures and a few hundred views and user-defined functions in about 45 seconds. After the comparison is completed you get the following screen:

This screen lists all the differences between the two servers. At the top is a list of all the objects in the databases. Each object has an icon indicating whether the two objects are identical or not. Using the filter menu it was easy to filter out all the items that were identical and only show changes. You can also filter on the type of object to display.

The two panes at the bottom show the differences in a specific table. You can see from the row highlighted in green that my CredentialID column is smallint in one database and int in the other. There are two additional tabs on this screen. One shows you the script to make the left database look like the right and the other tab shows you the script to change the right database. These individual scripts can be copied with a single click if you just want to run them in Query Analyzer.

In my situation I filtered this down to just tables. I have a script that generates all my procedures, views and functions in the proper order. After that I clicked on the Synchronize button. The first screen asks which server I want to update. Then you get to the following screen:

This dialog box shows the actual script that will be run. You also have the option to save the script to file if you choose. The Summary tab will display a text description of what the script does. In my case it consisted of a series of DROP CONSTRAINTS followed by a few ALTER TABLES and a bunch more ADD CONSTRAINTS. It was SO MUCH EASIER to figure out exactly what the script was doing by looking through the text description than trying to decipher the SQL. That's a very, very handy feature.

When I first started using the product I always saved the script at this point, cancelled out of the wizard and opened the script in Query Analyzer. That was kind of cumbersome but I really wanted to run the scripts through QA to see any problems that might occur. I also liked to look through the script and see just exactly what was being done to my database. The other day I decided to let SQLCompare run the script so I continued on to the next screen:

Choosing the second option launches QA on the right server in the right database with the script in the window. How cool! I guess I shouldn't be surprised that if I wanted the feature someone else did too. Choosing the first option runs the script against the proper database. It first confirms that you really want to make permanent changes to the database.

That gives you an overview of what SQLCompare can do. It's very straightforward and easy to use yet also very powerful. At another client, we were having difficulty with constraint names. We generate a script from a modeling tool and then build the database. On a regular basis we rebuild the database from the model but in the interim we just make changes to the model and the development database. Surprisingly enough we don't always get all the changes into the model :) When we first used the tool it generated all kinds of false differences that were really just different constraint names. SQL Server was auto-generating constraint names as it built the database.

SQLCompare had an option to ignore these constraint names. After setting that (and a few more of the 20+ options you can use to configure the comparison) we got a clean comparison that just highlighted the true differences. We used SQLCompare to generate an HTML report showing the differences and got them all tracked down.

Overall I've been very happy with SQLCompare. It's a great little tool that is easy to use but can generate complicated scripts to synchronize databases.

Full Disclaimer: Red Gate is an advertiser on this site. I figured if I'm going to use a tool I should probably start with one of my advertisers.