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.
Figure 1: SQL Compare’s 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.
|
SQL Compare 9.0 PROS: Intuitive; easy to use; robust functionality CONS: Unwieldy object handling RATING: 3 out of 5 PRICE: Starts at $395; $595 for SQL Compare Professional; volume discounts available RECOMMENDATION: SQL Compare offers some unique features, but the other tools' overall feature sets are superior. I recommend using one of the other tools. CONTACT: Red Gate Software • 866-997-0397 • www.red-gate.com\ |
ApexSQL Diff
ApexSQL Diff is another tool that I found easy to use straight out of the gate. The software opened up to a start dialog box that was simple and intuitive to use. The comparison results were more complex than the other tools’ results and gave me a lot of control over exactly which aspects of the objects were scripted and synchronized. The software even let me filter the results for every property of every object and apply it to the results without rerunning the comparison.
When I first viewed the comparison results, I was a little disappointed to see that the results were grouped by difference type, which lumps all objects into three groups. This approach makes it more difficult to find specific objects in the results. I quickly found the View options and discovered that there were multiple views I could choose, including grouping by object type. This gave me exactly the display I wanted. Figure 2 shows the ApexSQL Diff comparison results grouped by difference type.
Figure 2: ApexSQL Diff’s comparison results
ApexSQL Diff has a bit of a bigger learning curve than the other tools, but its many features and level of customizability more than make up for it. The best part of the robust filters is that they can all be applied dynamically, which let me script out existence checks and gave me the option to stop or continue the synchronization process after it encountered an error. One of my favorite features, which the other tools lack, is the Differences by Type pane that tells you exactly what’s different about an object that you select, rather than having to figure it out based on the script. This feature resulted in a very user-friendly experience throughout my test.
The synchronization process generated a lot of errors because of user-defined objects and schemas being created in the wrong order. Dependent objects weren’t always reported or scripted correctly. For example, a procedure with only 2 dependencies was reported as having 81 dependencies, all of which were scripted if I selected to include dependent objects. I love the abundance of features in the tool, but they also make the work area seem crowded. There are too many little panes, some of which create redundant functionality of another pane in the work area.
|
ApexSQL Diff |
xSQL Object
The only tool that I was completely new to in this test was xSQL Object. I received an intriguing recommendation for this tool and decided to include it in the test even though it wasn’t widely recommended, as the other three tools were.
When I first opened the tool, I was a little dismayed. I was presented with an almost empty application screen, with no prompt on how to proceed. I had to poke around to figure out how to perform a comparison. I manually added my databases and started the comparison. I eventually found the option to run the comparison wizard, but I had already been running it manually for so long that I continued using the manual option.
After I conquered the task of running a comparison, I was pleasantly surprised with the comparison results. xSQL Object’s results are by far the easiest to work with of all the tools and are the most visually appealing. The objects are separated by object type, making it easy to find what you want. The results summary has many levels that you can expand to drill into the various aspects of the object and properties. In addition, you can deselect any objects or properties. The tool can be tedious to work with if you need to deselect a large number of objects, because the objects can be deselected only individually. There’s no option to deselect an entire group of objects. The results pane is simple to figure out and easy to use. Figure 3 shows the comparison results for xSQL Object.
Figure 3: xSQL Object’s comparison results
One of the features that stood out for me was the report option. All the tools have a report option, but xSQL Object’s report is much easier to read and more visually appealing than the other tools’ reports. I didn’t like xSQL Object’s interface when I first opened the tool, but it has a feature reminiscent of SSMS’s Server Explorer that I really like. I was able to register my servers and databases for use in the comparisons. This feature would be a game changer for me if I needed to perform comparisons frequently for many different servers and databases.
|
xSQL Object |
Visual Studio 2010’s Schema Compare
The final database comparison tool I tested was Schema Compare in Visual Studio 2010 Ultimate. Visual Studio’s comparison tool was originally released as part of Visual Studio 2005 Team Edition for Database Professionals and is now available in Visual Studio 2010 Premium or Ultimate. It isn’t available as a standalone tool. Performing a full installation of Visual Studio is a much longer process than installing a standalone tool, although the process is still pretty simple. Fortunately, most people who want to use the comparison tool are already using Visual Studio as their development platform. For those people, the tool is already installed and ready to go.
When you launch Schema Compare, you get an interface that’s intuitive and simple to use. When I selected the options to see what objects I could filter out, I was surprised to see how wide-reaching the tool is. It compares the objects in the database; the properties of the database, including the physical structure; and certain server objects that might affect the database, such as server triggers, server audits, and server role memberships.
The comparison output is simple and straightforward. The results pane isn’t packed with a lot of features, but it provides everything that’s really needed. Objects are grouped logically by object type and have multiple expandable levels so you can see every property or feature of the object that’s different or the same. Another aspect of the tool that makes the learning curve small is the fact that it’s in Visual Studio and therefore offers a familiar interface that users know well. Figure 4 shows the comparison results for Schema Compare.
Figure 4: Schema Compare’s comparison results
My favorite feature of Schema Compare is the depth to which the tool does its comparisons. It’s the only tool I tested that set the Trustworthy property and successfully created the CLR assembly and procedure without manual intervention. It’s also the only tool I tested that recognized that one of the procedures had been signed and tried to sign it with the synchronization process. After creating the certificate manually, the tool was able to successfully sign the procedure. Schema Compare was also the only tool I tested that had support for running scripts in SQLCmd.
|
Visual Studio 2010's Schema Compare |
Final Comparison
After testing all four database comparison tools, I couldn’t pick a clear winner. Each of the tools has some features or capabilities that make it unique. Visual Studio 2010’s Schema Compare is the one tool whose use might be based solely on development platform. From a pure usability standpoint, my favorite tool is xSQL Object because of its Object Explorer–like interface and simple design. If I made a choice based on the feature set, ApexSQL Diff comes out on top for me. The only tool that isn’t a clear winner in some category is SQL Compare 9.0. In selecting which tool to use, you should determine which features matter the most to you.
