Comparative Review: Database Schema Comparison Tools

A lot of tools are available for comparing database schemas and data. Over the years, I’ve worked with several comparison tools—however, I didn’t want to focus this review only on tools I’d used previously. I therefore reached out to the SQL Server community to get feedback on which comparison tools they’re using and would recommend. The resulting list of tools was pretty long. I decided to focus on four tools for comparing database schemas: Red Gate Software’s SQL Compare 9.0, ApexSQL’s ApexSQL Diff, xSQL Software’s xSQL Object, and Microsoft Visual Studio 2010’s Schema Compare tool.

Test Plan

I decided to use the AdventureWorks2008R2 database for the test runs because it makes effective use of a wide range of features that you don’t see in abundant use in most databases, such as multiple schemas, extended properties, Data Definition Language (DDL) triggers, XML schema collections, and full-text indexes. I added some twists of my own to make testing more interesting and to see if I could break the tools:

• I created a certificate in the database.
• I created a stored procedure and signed it with the certificate.
• I imported the certificate into the master database, created a login from it, and created a user in the AdventureWorks2008R2 database mapped to the certificate user.
• I created an unsafe CLR assembly and a CLR procedure.
• I changed the Trustworthy database property to allow the unsafe CLR assembly.
• I created a Service Broker queue, route, and service, plus an event notification.
• I chose an instance for comparing schemas without the full-text engine installed to force the full-text indexes to fail.

I created four empty databases on the second instance, one for each tool. I used the same source database for all tools to ensure that they were comparing identical source databases. I used each tool to inspect the database schema, output some scripts and reports as available, and synchronize the database objects. After synchronizing the database objects with each tool, I used each tool to inspect the databases that were synchronized by the other tools to see if any of the tools could find objects that the others didn’t.

SQL Compare 9.0

The first tool I tested was SQL Compare 9.0. Installation was fairly quick and easy. However, when I downloaded the tool, I felt that the company was trying too hard to push its other tools. There was a download option for the full SQL Toolbelt suite of tools, but I drilled into the SQL Compare page and opted to download just the single tool. Unfortunately, this didn’t help me get straight to installing SQL Compare. The installer offered all the tools in the SQL Toolbelt suite and defaulted to all applications selected for installation. I had to go through three pages of applications and deselect all but the one tool I wanted. Still, I wasn’t overly put off by the installer—the installation went fairly quickly, and if I had wanted to download several of the tools or even the entire SQL Toolbelt, I would have appreciated the way the installer works.

When I first launched SQL Compare 9.0, I was presented with a dialog box that was intuitive and simple to use. I was able to immediately start comparing databases, with no steep learning curve to overcome. The results of the comparison seemed straightforward and easy to understand at first. Figure 1 shows the comparison results.

As I got further into the process, I had difficulty figuring out how to perform certain actions. One problem I had was getting past the errors for the full-text indexes. Full-text indexes weren’t listed in the object list in the output. I removed full-text catalogs from the output via the filters, but this had no effect on the output. I eventually figured out that you can select to ignore full-text indexes as a project option and that there’s no other way to remove full-text indexes from the results. Changing the project options means rerunning the comparison to get a new set of results.

SQL Compare’s usability was impressive for running the comparison, but the usability and functionality suffered after the comparison was executed. SQL Compare supports comparing databases, database backups, and database schema scripts stored in a folder or source control. It has an add-in for SQL Server Management Studio (SSMS) to add SQL Compare functionality directly into SSMS. On the downside, if you discover that you need to filter certain objects out of the results, there’s no direct way to remove them. Removing an object such as filtered indexes requires changing the project options, which then requires rerunning the comparison. Furthermore, the synchronization process fails to synchronize certain object types, such as CLR objects, signed procedures, and certificates. One of my biggest disappointments was that the tool lacks support for including existence checks in the generated scripts.