Health insurance companies strive to quickly provide worried patients with details about medical benefits, financial records, locations of affiliated care providers, and other factors that could influence health care decisions.

Storing and quickly gaining access to all of this information, while keeping it up to date and accurate, is not an easy task. Just ask John Allman, senior database administrator (DBA) with American Medical Security Group (AMS).

Managing duplicate databases

AMS is an insurance company with about 1,400 employees that provides health benefit plans for individuals, families, and small businesses. It is Allman's job to manage the company's SQL databases, which hold various corporate data used by AMS agents, financial personnel, case managers, and others.

Allman and the server administration group manage four servers in two cluster groups, several databases on dedicated application servers for production, and four servers in a test/pre-production group. Though AMS operates subsidiaries in 32 states, all the company's servers are in Green Bay, Wisconsin.

Since the data on these servers must be accessed daily by AMS employees, Allman and the other DBAs use a system in which databases containing agent information are duplicated and alternated between live and inactive. Each day, one copy of the database is updated while a second copy is being accessed online. After the update is successfully completed, the databases are switched and the recently revised database becomes the primary.

Out of synch

In January 2002, a problem surfaced in which a stored procedure in one database was different from its equivalent in the duplicate database.

"One day the load would take only an hour to complete," Allman says, "but on alternate days the duplicate database load was taking six to eight hours. This huge discrepancy was making it nearly impossible for us to meet our production schedule on time."

Allman thought he and his co-workers might have to go through the databases by hand to compare the objects, then write a code change from scratch. But before beginning that time-consuming process, Allman attended a SQL Pass conference in Denver where he saw a demonstration of Red Gate Software's SQL Compare tool. SQL Compare graphically displays differences between databases and automatically creates a script to synchronize all database elements.

Allman received a trial copy of SQL Compare at the conference and took it home to run a comparison of the two databases. He immediately found the problem: one of the databases used in the load process had an outer join, while the duplicate version of the stored procedure had an inner join.

This seemingly minor difference caused the database with the outer join to have thousands of extra rows, making its load run approximately seven times longer than it should. Armed with this data from SQL Compare, AMS application developers were able to quickly correct the problem.

"Our first use of SQL Compare saved us weeks of manual comparison," says Allman. "The demo copy more than justified AMS purchasing additional copies of the software without further discussion."

Greater accuracy in less time

After Allman's initial success with SQL Compare, AMS has continued to use the software tool for its in-house database updates and revisions. During this process, all database objects are first constructed on a test system. Then DBAs use SQL Compare to evaluate and compare all objects in the test system with those in the pre-production system. Scripts generated by SQL Compare are then run to move any changes from the test system to the pre-production system.

Next, testing continues on the newly migrated pre-production system. If further changes are necessary, DBAs restore the pre-production databases from a backup created prior to the move, make the required changes to the test system, and repeat the process to move the changes to the pre-production system. After final verification, DBAs run the same scripts on the production system, ensuring that the database objects that were successfully tested on the pre-production system are transferred intact.

Allman estimates that SQL Compare shaves hours from AMS's database object promotion process each month, but he says that saving workers time is only part of the benefit.

Plans for the future

"Since we began using SQL Compare for promoting database objects from test through production, we have been able to run one script for each database to migrate all changes to the next level," he says. "In using the same script for each level of movement, we can achieve greater accuracy in less time, better guaranteeing our results."

"It can take months working as a DBA to understand SQL Server well enough to be considered a trained expert," Allman says. "SQL Compare is usable right out of the box to a person familiar with SQL Server. It has really given us a method for creating a repeatable process to migrate changes that greatly simplifies our job as DBAs."

Kathleen Wheatley (cramco@cramco.com) is a freelance writer specializing in IT and other technology topics. She works for Cramblitt & Company in Cary, N.C.