SQL Compare Review

Red Gate Technologies

SQL Compare by Red Gate Technologies is a handy little program to compare Microsoft SQL 7 Databases. It shows the structural differences in tables, views, stored procedures, triggers, users, rules, roles and user defined types and allows you to generate scripts to synchronize the databases.

Installation of SQL Compare was easy. I installed it on a Pentium III 600 with 256MB of RAM, but the requirements are much less, with only a Pentium 166, 32 MB of RAM (64 MB recommended) and 10 MB of free hard drive space required.

SQL Compare doesn’t use ODBC, so you’ll have to have the SQL Server Client Tools installed on any PC you wish to run it on. Comparing databases is simple. When you open the program, a pop-up window asks you if you wish to open a previously saved workspace or compare two databases. You can then register both databases at once of you wish to run a comparison, or just one if you want to save a snapshot to work with at a later time.

Once the database is registered, you can save the definition to a workspace file, which is named by date and time. This is useful because you can make changes to database schema and then look at your saved definitions over time and create change scripts as necessary. To compare two databases, you click on the “compare” toolbar and a window opens which allows you to choose your databases from a list of saved workspaces.

Scroll down to the saved workspace definitions you want to compare and click Ok. SQL Compare runs scripts to compare the two databases and opens another window to show you the differences between the two.

The arrows show you which objects either exist in one database but not the other or are different from each other but exist in both databases. It couldn’t be simpler to see if all your database objects are equal in both databases. To see the actual SQL behind the objects, just right click and choose view SQL. A screen pops up which gives you the option of viewing the variances or creating change scripts to synchronize the databases.

In the first version of SQL Compare, options were limited. With this latest version, you can include or exclude several options in your comparisons- including triggers and permissions. If you wish to generate change scripts to make the two databases match, you select the migration tab you must return to the compare screen and choose which objects you wish to script and then hit the run button. The scripts are generated for you automatically. This is a little awkward and it would be nice if future versions allowed for a way to just click on the migration window (perhaps a split screen?) and choose objects from there. You then can copy and paste them into Query Analyzer to bring your databases into sync.

SQL Compare also gives you several options in deciding what to compare between databases. You can:

Ignore comments.
Ignore CR/LF.
Ignore tabs.

Ignore spaces.

Ignore object permissions.

Ignore script case.

Ignore set statements.

Include indexes.

Include fill factors.

Include keys.

Include checks.

Include triggers.

Ignore object order.

For a simple, inexpensive tool for quick comparison of databases, SQL Compare is a powerful utility. A single user license is only $50, with a site license available for $300. (Red Gate offers a discount for PASS members; see the
PASS Member Promotions Page for details.) Red Gate may include the ability to compare data as well as schema in future releases — which would make this tool a must have. Red Gate is now also offering SQL Compare as part of a bundle (optionally) which includes SQL Data Compare (to compare the data in database tables) and COMpare (to compare ActiveX interfaces). A free 14-day demo version of SQL Compare is available from
Red Gate Technologies.

Latest Articles