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.