Database programming presents plenty of challenges that have nothing to do with making code work correctly. Synchronizing a database among development, test, and deployment environments is a time consuming, daunting, and (alas) necessary task. You also have to coordinate data in a database before you perform database tests, so you can test from a known state, and then you have to reset the database after the tests are complete. Who wants to mess with all of that?

Red Gate Software's SQL Data Compare promises a solution for synchronizing data between instances of the same database in various stages of software development. I recently had to work on a large SQL Server based project, so I took SQL Data Compare out for a test drive. I liked it enough to drive this one home.

SQL Data Compare has all of the required elements of professional software: a professional installation, integrated help documentation, wizards to get things moving quickly, and an easy-to-follow user interface and, of course, the desired outcome.

In brief: SQL Data Compare compares and synchronizes two SQL Server databases having the same schema, and it wastes no time about it. The first step of the wizard gets down to business. You indicate the two databases you are interested in synchronizing. Next, SQL Data Compare shows you the tables and views available for synchronization. (I blacked out target databases and servers to protect the innocent; this was real data.)

Figure 1: Pick the tables or views to compare and synchronize.

Finally, you describe what you want to compare and synchronize. By default, SQL Data Compare does a complete comparison, but you can choose from a combination of missing, different, additional, and identical records.

Figure 2: Pick a combination of mismatched records to resolve.

SQL Data Compare does the rest. It clearly and concisely indicates the results of the comparison process; clicking the Synchronize button is all it takes to reconcile the differences.

A nice addition, in future editions, would be an undo feature that could rollback changes made during by synchronizing. While I found SQL Data Compare to be mature, stable, and reliable, you will still want to backup your databases before synchronizing to support a manual undo process.

You can use SQL Data Compare in conjunction with the company's SQL Compare, which synchronizes schemas; both are included in a bundle of Red Gate SQL tools or are available separately.

SQL Data Compare is designed for Microsoft's SQL Server 7.0 or higher. Version 3.3, which I tested, is project centric. As easy it was to compare and synchronize databases, the ability to save and reuse projects made repeating synchronizations even easier.

SQL Data Compare from Red Gate Software, www.red-gate.com is available as a 14-day free trial. There are a variety of licensing options, starting at $195 for a single-user license to $1,050 for 10-user licenses with a year of support and free upgrades. You will like the professional and easy-to-use wizard based user interface, but performance suffers slightly over wireless networks, making it a tad slower at the beach.

You can find other comparison and synchronization tools. However, at its $195 introductory price point, its 14-day free trial, and its ease of use, Red Gate Software is a challenging proposition to beat.

Paul Kimmel is the founder and chief architect for Software Conceptions, Inc. and a co-founder and President of the Greater Lansing Area .NET Users Group (glugnet.org). He has written several books on .NET and object oriented programming and is available to build software for your company. You may contact him at pkimmel@softconcepts.com.