Walk into a pub in Australia and you might hear customers order a "middy" (9 oz.) or "schooner" (15 oz.) of the "amber fluid." Don't be confused; it's all just beer, and consuming it could be considered a national pastime.

Australia has one of the highest per capita beer consumption rates in the world, a distinction that Alex Thomas, director of Didg Information Systems (http://www.didg.com) and an Aussie himself, knows about first-hand.

"Making beer is considered by many Australians to be a public service," he says.

That is why Thomas was so heavily invested in his contract as the Senior Analyst Programmer for a project involving one of Australia's largest brewing companies. Thomas was part of a team that structured, implemented and managed the B2B supply chain extranet that connects the brewery to its suppliers throughout Australia. He maintained and updated five SQL server databases used to coordinate product forecasts, schedules and inventories.

From hours to minutes

Making structural changes and taking databases from development to production used to take Thomas hours of manual scripting and comparing code. But after reading a forum posting about SQL Compare software from Red Gate, he purchased the simple tool that automatically compares and synchronizes SQL databases.

"The process of synchronizing databases used to be unbelievably difficult," says Thomas. "I made comparisons by running two instances of SQL Enterprise Manager side-by-side and looking at lots of printed lists. Sometimes the changes would have to be painstakingly entered by hand without generating a script at all."

"Ultimately, it would take me more than three hours to do a set of changes that SQL Compare scripts can do in only a few minutes. More importantly, SQL Compare can do all this more efficiently, reliably, and securely than I ever could by hand."

Ch-Ch-Changes

Major database changes across the project's five servers – two development, one staging and two production – were made every one to two months. There were also frequent changes to fix production issues. With a large feature set in place, the entire process might take a day or two for comparisons and verifications, but the actual database change now takes three minutes at most.

Didg Information Systems' process starts with functional verification, followed by labeling and lodging the ASP and T-SQL source code into MS SourceSafe. Then SQL Compare is used to run a schema comparison against the development and staging servers. Typically, the team de-selects all the equivalences and deals only with variations. Each variation is visually inspected to verify the nature and extent of the change.

Next, Thomas selects specific sets of changes to reduce the complexity of the task. His selection is based on his knowledge of the relationships in the data model and the feature sets being deployed. A script is generated for the set of changes, and is run against the staging server using Query Analyzer and a web deploy from SourceSafe. Finally, checks are made to ensure that the change occurred without breakdowns in existing service.

Overcoming obstacles

In addition to ensuring service availability during the process, Thomas and his team faced other challenges, including database corruption, violating data integrity, and risking that changes could be irreversible.

"SQL Compare helped me overcome these obstacles by visually indicating the scope of the change, creating planning checklists, automating thorough change scripts, and providing an audit trail of all changes," he says. "The software made my job less frustrating, and improved the reliability and precision of my services."

Yet for all the improvements in efficiency and cost-effectiveness that Thomas has introduced to the Australian beer market, he has no illusions of being an honored public servant.

"Someone had to handle the dirty, difficult and dangerous job of managing these SQL databases," he says. "It's Red Gate's SQL Compare that stands behind some of Australia's favorite beers and has earned the status of 'legend' in my book."

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.