SQL Server Comparison and Synchronization Tools

Change is integral to modern business. Managing the migration of database objects, code, and data from development to staging and then from staging to production environments can be a tall order that consumes a lot of time, energy, and effort. Given how painful change management can be, a market has developed to provide tools and solutions that make detection and synchronization of change easier to tackle.

Several solutions are available to provide differencing and synchronization of SQL Server objects, code, and data. In this article, I compare Red Gate Software’s SQL Compare 8.1 and SQL Data Compare 8.1, The Australian Software Company’s SQL Delta 5.0, and ApexSQL's ApexSQL Comparison Studio 2008. Additional comparison and synchronization solutions not discussed here include Quest Software’s Toad for SQL Server and Idera’s SQL comparison toolset 1.2.

I truly enjoyed evaluating the solutions from Red Gate, The Australian Software Company, and ApexSQL. Pitting these solutions against one another was a bit like test driving different Lamborghini models to determine which one I liked best. All three solutions have definite strengths, with no real flaws or weaknesses to speak of. Which solution is best is purely subjective and depends on your business needs and preferences.

Putting the Solutions Through the Paces

To put these tools to the test, I created a copy of one of my existing databases and made some deliberate changes to structure, code, indexes, and data. Then I turned each of the tools loose on a copy of the modified database and the original to see how well each would do in terms of discovering differences, letting me slice and dice those differences, and, of course, how each of them fared in terms of managing synchronization of those differences. I was also concerned about accuracy—the ability of each solution to catch the changes I’d made. I was happy to see that all three solutions nailed that requirement right out of the gate. They all also fared well on the other key criteria I was looking at, which you can see in Table 1.

SQL Compare 8.1 and SQL Data Compare 8.1

For many people, especially developers, Red Gate is almost synonymous with database comparison and synchronization. The company is considered the de facto leader within this space. I’ve been using SQL Compare and SQL Data Compare for over five years, so I enjoyed putting Red Gate’s solutions to the test from a competitive standpoint—not only to see how they stacked up against other offerings, but also to make sure I haven’t been missing something significant that another solution offers.

The way SQL Compare and SQL Data Compare both save comparison details as a project is a big time saver and makes it easy to save connection details and comparison preferences for easy reuse in databases that are subject to frequent change and modification. SQL Compare and SQL Data Compare both also benefit from an intuitive UI that’s easy to interact with. (Figure 1 shows SQL Compare’s UI.) In addition, both products provide a great approach to synchronizing changes in the sense that they let you either generate a script to implement the changes or just run that script directly against the target database (or both). I also love that both SQL Compare and SQL Data Compare provide the option to recompare databases after synchronizing or pushing changes—I use this sanity feature frequently, to make sure my changes went as planned and to ensure that I didn’t miss something.

SQL Compare is probably the quickest of all the tools I evaluated. Although the other solutions weren’t far behind, I really noticed in a few cases just how fast SQL Compare is. SQL Data Compare seemed to be about as fast as the other data-comparison solutions I evaluated; I didn’t notice any big differences during comparisons.

Overall, SQL Compare and SQL Data Compare both work as advertised; they are solid, robust, cost-effective tools. Both applications have well-polished UIs that let you quickly slice and dice differences to easily evaluate and isolate changes from one database to another. In addition to working with SQL Server databases, SQL Compare and SQL Data Compare can both work with SQL Server backups, which expands the tools’ change auditing and granular data recovery abilities in case of a disaster.

Another strength that both tools offer (and that often goes overlooked) is the Pro versions’ support for command-line interactions. Command-line capabilities let you create scripts or batch files that business users can use to push metadata (e.g., pricing information for products that are going on sale) from a test or quality assurance server out to production servers without requiring DBA interaction. All DBAs need to do is set up the types of data changes allowed or targeted, then set up scripts for business users or QA folks to run when the need arises (and after data has been validated). In addition to highlighting how versatile these tools can be in the war on herding data, the benefits of this feature alone can easily pay for the cost of SQL Data Compare.

The only drawback I noticed with SQL Compare and SQL Data Compare is that they’re two separate tools. Ideally, I wish the two tools were combined into a single application. Having them both be part of the same application would make sense, as in SQL Delta.

SQL Delta 5.0

Out of the box, one of the things that helps set SQL Delta apart from the competition is its great UI—which is especially impressive because neither of its competitors are slouches in this regard. Initially, I was a bit skeptical of the fact that SQL Delta uses the new Microsoft Office Ribbon (see Figure 2) to drive user interaction and manage options, navigation, examination, and synchronization. But it took only a few minutes of using SQL Delta to become hooked on its well thought out and intuitive use of Ribbon functionality. In fact, in addition to allowing very logical interaction with objects and filtering options, SQL Delta also leverages Ribbon functionality to intelligently and seamlessly allow object and data comparisons to exist within the same application.

As with Red Gate’s approach, SQL Delta organizes connection details and preferences (or comparison settings) into projects or project files. But because these projects already contain connection details and comparison preferences, the SQL Delta UI makes it easy to take comparisons to the next step by allowing you to quickly and intuitively define comparison settings for additional data you want to evaluate.

I was impressed with SQL Delta’s user experience regarding synchronization. I’ve always liked Red Gate’s synchronization wizard, but SQL Delta seems to magically accomplish the same task with fewer mouse-clicks—while still providing all the features and options I’ve grown to require from Red Gate.

SQL Delta also gets bonus points for making it patently obvious that my databases were running in compatibility mode—something that neither Red Gate nor ApexSQL make abundantly clear. Likewise, while testing filtering logic, I was impressed that even when I told SQL Delta to ignore differences in indexes, it still reported that my primary keys (on one table with a composite, clustered, primary key) were different. Red Gate and ApexSQL both missed this fact, which kind of floored me. (It’s true that I told Red Gate and ApexSQL to ignore indexes, but indexes are hardly the same as primary keys.)

SQL Delta also did a great job with the size of database I was working with. Like its competitors, the application churned through an 8GB table fairly quickly, and it easily managed memory without ballooning up. In terms of both features and performance, the product can definitely hold its own.

Unfortunately, SQL Delta is missing some of the features that both of its competitors sport, such as support for interrogating source control, using backups, or creating and using snapshots. In that regard, it falls behind both of its competitors.

However, SQL Data’s price more than makes up for the features it lacks—the application is about half the price of the other solutions. If you don’t need the additional features or capabilities, then SQL Delta is an attractively priced option that packs a powerful engine, a great UI, and some excellent capabilities into a single, tidy package.

Overall, I’m impressed with SQL Delta. Its pricing, feature set, and capabilities make it very competitive.

ApexSQL Comparison Studio 2008

ApexSQL has been in the comparison and synchronization game for a while. I’ve used their products in the past, and I have several colleagues who swear by ApexSQL Comparison Studio. Consequently, I expected them to be a strong performer—and I wasn’t disappointed.

ApexSQL takes the same approach as Red Gate, in which separate applications are used instead of rolling everything into a single application like SQL Delta does. Also similar to Red Gate’s model, you can purchase ApexSQL Comparison components separately or as part of a bundle. Unlike either of its competitors, ApexSQL provides APIs that allow developers to fold comparison and synchronization capabilities into their applications—offering some powerful capabilities and options for common or recurring change management.

In terms of comparison and filtering capabilities, ApexSQL Comparison Studio 2008 and its components can definitely hold their own when compared with SQL Delta and the Red Gate products. As you can see in Figure 3, ApexSQL solutions take a different approach to displaying results than either Red Gate or SQL Delta. I’ve used Red Gate for several years, so I initially struggled a bit with how ApexSQL handles the display of differences. However, I’m not sure ApexSQL’s approach is any better or worse—it’s just different.

In terms of the overall UI, ApexSQL’s interface is more Spartan than the other solutions. Because I tend to prefer a flashier UI, I wasn’t as drawn to this one as I was to the others. However, in a couple of cases I noticed that ApexSQL’s more basic approach made it much easier for me to visually notice differences. Even when the same functionality existed in the other apps, the visual clues weren’t as obvious because they blended in better with the glitzier UIs.

ApexSQL’s approach to setting up and storing project details was the least intuitive of the solutions I evaluated. In fact, this feature borders on being a liability. In addition, ApexSQL Diff’s and ApexSQL Data Diff’s synchronization interfaces weren’t as robust as their competitors’. Although ApexSQL’s synchronization process contains all the necessary elements for successful synchronization (including warnings and validations), it’s less intuitive and more cumbersome than the other solutions’ synchronization experiences. I do prefer the fact that ApexSQL lets you change scripting options on a per synchronization basis (as opposed to per project); this option gives you more granularity in managing synchronization efforts.

One area in which ApexSQL Comparison Studio 2008 leaves its competitors in the dust is comparison of SSIS packages. ApexSQL SSIS Compare lets you dig into every facet of your SSIS packages and enumerate every possible setting or configuration detail. Although ApexSQL SSIS Compare doesn’t synchronize packages, it’s still a great feature for organizations that depend heavily on SSIS and extraction, transformation, and loading (ETL), and it can be a lifesaver when troubleshooting packages and validating changes. Both of ApexSQL’s competitors lack this powerful option.

ApexSQL Comparison Studio 2008 offers compelling strengths and benefits. It’s a strong competitor for both Red Gate and SQL Delta.

Eenie, Meenie, Miney, Mo...

All three of the solutions I reviewed are competitive in terms of features, capabilities, performance, and ability to improve productivity. Consequently, pricing and bundling of solutions play a big role in determining which solution might best meet your needs.

Red Gate’s SQL Compare and SQL Data Compare are $395 each. Pro versions of each product are $595, providing command-line interactions, support for source control, and other benefits. You can also buy both tools bundled together with SQL Dependency Tracker (a phenomenal tool in its own right) and SQL Packager for $695. (This bundle doesn’t include the Pro versions of SQL Compare and SQL Data Compare, which makes me wish that Red Gate offered a Pro Comparison Bundle for $895.)

ApexSQL’s products are similarly priced. ApexSQL’s Comparison Studio is $699 and includes core differencing components, a packaging solution, APIs, and ApexSQL SSIS Compare. You can also purchase components separately, at $299 each.

Red Gate’s and ApexSQL’s bundles are great values, but SQL Delta is also competitively priced. It’s a cheaper overall solution, albeit with fewer features—but it’s a steal if you don’t need some of Red Gate’s or ApexSQL’s more advanced features and capabilities.

All three solutions will help you save significant time, so they’ll quickly pay for themselves. If you don’t already have a comparison and synchronization solution, you should get one. Each of the solutions I reviewed is available for a minimum 14-day trial. Because each solution has strengths and benefits, I recommend that you try them all to determine which one best meets your organization’s long-term needs. If you’ve been managing differences manually or through scripts, any one of these solutions will be a huge improvement.