SQL Compare 1.14

Compare differences between databases

Whether you're a SQL Server DBA or developer, you'll eventually need to scan code, line by line, searching for differences between one database and another. If you're lucky, you won't be doing this comparison after a vendor-supplied database update has slowed the application to a crawl because of a poorly designed view. As a DBA, I wasn't so lucky recently, and I spent hours perusing scripts and trace results. If I had had Red Gate Software's SQL Compare 1.14, a database-comparison application for SQL Server 7.0, I could have registered both databases, compared them, and narrowed the differences down to a few key objects—the wayward view being one of them.

As a developer, I sometimes need to compare development and production databases, which is an arduous and time-consuming task, especially when I'm dealing with databases that contain several hundred objects. To test SQL Compare, I used two large transactional databases, each containing approximately 500 tables.

Installing and using SQL Compare are intuitive procedures, which is fortunate because I found that the online documentation was sparse. SQL Compare uses SQL-Distributed Management Objects (SQL-DMO) for SQL Server 7.0 and Microsoft Desktop Edition (MSDE), so you'll at least need to have the SQL Server 7.0 Enterprise Manager installed on the same machine as SQL Compare. I loaded SQL Compare on a dual-Pentium II 350MHz system with 128MB of RAM running Windows 2000 Server and SQL Server 7.0 with Service Pack 2 (SP2). Because of the length of time that the initial database registration process takes, having a fast machine with plenty of RAM is an important consideration. Red Gate's Web site states that the registration process "can take quite a long time." In my test, fully registering both databases took more than 30 minutes.

The registration process scripts the structure of each database object—including tables, views, stored procedures, triggers, users, roles, and user-defined types—which SQL Compare can then use for the comparison. As an alternative, you can choose to save the workspace to a file so that registering the databases is generally a one-time process. Any subsequent change to the databases, however, requires you to re-register, and SQL Compare doesn't yet provide a way to reload only the modified objects. (Red Gate representatives told me that the company will add partial reloading capability in a future release.) SQL Compare uses a combination of database name, date, and time to name the databases as it registers them, which is an advantage when you need to compare multiple database versions.

I used the Register and Compare feature and let SQL Compare "whir away" (that is the description the online Help used). When the process finally finished, I had a list of every object in the two databases. The interface is similar to Windiff (a file-comparison utility included in the Win2K support tools) in that it displays color-coded differences between the objects that you're comparing. In SQL Compare, you can easily see which objects are identical to one another, which ones exist only in one database, and which ones exist in both databases but have differences in structure. You can also modify the view, for example, to hide all the identical objects. Figure 1, page 62, shows an example of database names and object comparisons. Or, you can select any criteria that you would like to include in or exclude from the comparison. Some of these options are ignore comments, ignore tabs, ignore case, and—most useful when comparing triggers—ignore spaces, line feeds, and carriage returns.

Comparing the databases is a much faster process than registering them; within a few minutes, I compared the same databases several times using different filter criteria. I narrowed my view to show only objects that existed in both databases but had some differences, primarily to make sure that the product would catch field additions and modifications to any triggers or indexes. The product found every change I made to the development databases and even scripted the permissions for users. Because I wasn't concerned with these permissions, I filtered them out, too.

After I discovered which tables had differences, I drilled into the scripts to see the differences. SQL Compare provides an excellent drill-down utility that incorporates a split-screen view of the scripts from each object, in this case a modified table, which Figure 2 shows. The utility displays each line with a unique color code to highlight differences—green for modified lines and blue for new lines. As you scroll through the text, both panes move simultaneously, which makes reviewing easy. Again, you can apply a filter to see only particular parts of the script. For example, clearing the Show Permissions check box instantly removes any code that grants permissions to users. A useful feature that Red Gate might consider adding to SQL Compare is the ability to include window titles that match the database names.

Although the detailed list of differences is useful, it works with only one object at a time. In this release of SQL Compare, Red Gate added browser-based reporting that provides extensive HTML reports for one or multiple objects. These reports are useful for DBAs who can't control database structure changes but want to document the changes from one version to the next.

At the heart of SQL Compare lies its most powerful feature: the ability to script all the changes to make the two databases identical. You can use this feature to make changes or to undo changes to the production database. For example, if an insert trigger exists in the development database but not in the production database, SQL Compare will generate two scripts, one to drop the trigger from the development database and one to add the trigger to the production database. SQL Compare also generates transaction-oriented scripts, so if one piece of the script fails for any reason, then all the modifications that the scripts make are rolled back except changes outside transactions, such as adding users and roles. Because SQL Compare doesn't directly execute the scripts it generates, you need to copy scripts into Query Analyzer or another utility. I tested one script to add a stored procedure, add a field to an existing table that had several thousand rows of data, and create three new tables. The SQL Compare documentation states that the scripts aren't perfect and can fail under certain circumstances, so Red Gate highly recommends backing up your database before you run the transaction-oriented scripts. The script that I tested executed successfully.

SQL Compare is a relatively new product. Red Gate initially released it in December 1999 and has produced several updates that provide cosmetic changes and several bug fixes. The company has demonstrated its commitment to making a better product by responding rapidly to several issues that might have detracted from SQL Compare's overall usefulness. For example, the company recently provided an update that corrected a problem with scripting permissions. I had a small problem with the toolbar control that SQL Compare loaded, Data Dynamics' ActiveBar Control, which was a different version of the same control used by another application. I couldn't load both applications simultaneously, and the toolbar stopped functioning in my other application. I remedied this problem by using a Win2K feature that lets you run multiple versions of the same controls and DLLs on the same system.

SQL Compare is straightforward and affordable. Despite its shortcoming—namely, that it takes so long to load the databases—it has a powerful set of features that go beyond providing developers and DBAs useful information about the databases they work with. But even if you used SQL Compare entirely for informational purposes, the product would be worth the relatively small investment of $50 because it can find the subtlest database differences in minutes rather than hours.