SQL Compare®
Keep in synch with clients with SQL Compare
by Jill R. Aitoro
Maintaining an in-house database can often be a full-time job. Tack on duplicate databases strewn across client sites, all undergoing customized changes at varying times, and you have the potential for an administrative nightmare.
The System Shop Inc. needed to find an efficient way to routinely synch up its own SQL Server database structure across the seven databases it maintains for clients, all without risking the integrity of the data.
Modifications breed "monstrous chore"
The System Shop is a small software company whose application for the natural gas and liquids industry, C3, provides transaction capabilities such as contract and ticket entry, distribution, invoicing, risk management, daily market tracking, and exchange calculations. The application relies on SQL Server 2000 databases running at the client sites, which are maintained by the fewer than 10 developers employed at The System Shop.
"When I started, a year and a half ago, every database at all of the client sites had a different structure," says Tim Cartwright, Senior Systems Engineer at The System Shop.
The databases had been modified throughout the years without any synchronization. Objects in the client databases weren't included in the master database at The System Shop, and there was no consistency among client sites. Because the databases are accessed on a daily basis, they were periodically tweaked according to the needs of the client.
"The lack of synchronization was a source of much confusion, and seemed to be an insurmountable task that there was never enough time to work on," Cartwright says.
The System Shop had no reliable modus operandi in place to deal with the problem. The company's standard procedure involved moving database objects manually when a release was made to a particular client. When updates did occur, changes to the database schema often led to added headaches, due primarily to referral integrity and NULL versus NOT NULL scenarios. Loss of existing data was a serious concern as well.
"The process was piecemeal and haphazard at best," says Cartwright. "Keeping each one of the databases in synch was a monstrous chore that was either not getting done properly, or not getting done at all."
Synching up the situation
In 2002, The System Shop came across Red Gate Software's SQL Compare, which compares and synchronizes such SQL Server database structures as tables, stored procedures, views and user-defined functions.
"We considered doing something manually," Cartwright says. "But once we found SQL Compare, we realized that the cost of our writing a program internally would have been significantly more than the cost of the tool. And being a small shop, we all wear different hats. Our time is extremely valuable."
The System Shop now uses G7X (www.g7x.com) to compile its Visual Basic front end and middle tier, and SQL Compare to match its local production database to a local version of the last release that went to clients.
SQL Compare creates files based upon object type – tables in one file, views in another, and so on. After the comparison runs, a listing of objects appears with an icon indicating the object status (Same, Changed, Exists on Source Server Only, or Exists on Destination Server Only). After selecting the object types to view and then the objects to script, the change script is generated automatically.
"I generate a compare-and-synchronize operation between two databases in less than an hour," Cartwright says.
After the local databases are synchronized, the generated scripts are run at the clients' sites. The System Shop then updates middle tier and client files with its own auto updater, which pulls all changed files from a shared folder and registers each before running the client application. Eventually, The System Shop intends to automate that part of the process, using Red Gate's SQL Comparison and Synchronization Toolkit.
Keeping up with change
In addition to the scheduled changes made to the database every month, the main application is undergoing several 2-tier to 3-tier refactoring changes, which have required many enhancements to the database. The biggest single job to date involved switching several data types – from numeric to currency or numeric to bit, for example – across every one of the tables in all of the databases The System Shop manages.
"We would not have been able to deploy those changes without SQL Compare," Cartwright says. "It has made our releases exponentially easier, and has helped us towards our goal of keeping our databases in synch. Without SQL Compare, I doubt we could have accomplished that goal in a reasonable time frame without several DBAs devoted to that task alone."
Here are some screenshots of SQL Compare in action at The System Shop.
Jill R. Aitoro is a freelance writer specializing in technology subjects. She can be reached at cramco@cramco.com.
