SQL bundles from Red Gate

Fortune 500 bank embraces change with SQL database comparison

by Erin Hatfield

While most people spend their weekends sleeping late and running errands, database administrators (DBAs) at a Fortune 500 bank update more than 50 Microsoft SQL databases and ensure that the changes are accurate. The work might sound trivial, but it's critical in environments where errors in data and structure can cost millions of dollars.

Although it's never a joy to work on the weekends, Kevin Maselow, manager of the SQL Server support group at the bank, has found a way to dramatically reduce the time and anxiety that used to accompany the updating process. Maselow has simplified the work using Red Gate Software's SQL server database comparison and synchronization tools.

The data behind the service

Maselow's institution uses its SQL databases to keep track of savings and loan-casing information, case modeling, stock market statistics, and real-time trading. He supervises eight certified SQL DBAs who oversee nearly 50 SQL servers in 12 states. The information stored in the databases needs to be accessible every minute of every working day – either for stock market purchases, procedures, reports or investment modeling – and all of the data needs to be consistent throughout each database.

Changes to the company's databases are done over the weekend and must be approved by start of business Monday morning. Each change is done in the development phase, then tested using Red Gate's SQL tools. Once the development and testing databases are changed, the changes are combined into one script that is rolled into the production database that is used by bank customers.

Most of the bank's databases are mature, so changes to data or procedures result in new code that has to roll out to approximately 3,000 users. These "full-phase" changes are normally upgrades that are scheduled months in advance. A minor glitch in updating a database change can delay the new databases' validation and result in major delays in moving to the production environment.

"With each change, we have to prove that all of the environment changes, new code drops, new development products, and new database schemas actually carried over to the production version of the database," says Maselow. "If we have to call back an entire weekend of changes because we missed one mistake, we'd have a lot of questions to answer."

In addition to the routine weekly revisions, major changes to a stored procedure or a complete database conversion are required every other week. Large conversions occur about once a month, with a complete release about once a year.

Automated validation

One of the most dramatic areas of improvement for the bank has been the verification process.

Maselow and his colleagues used to manually sample and compare the data to ensure that it was consistent in each of the environments, a time-consuming and inexact procedure. Sampling cannot uncover every disparity, especially with the bank's databases, each of which can contain hundreds of tables, many with millions of rows of data, hundreds of stored procedures, and hundreds of views within each table.

Using the manual processes, bank DBAs often discovered errors and caught problems with the synchronization scripts. Once these were found, however, the DBAs had to spend days comparing data and generating new code.

Now that the group uses Red Gate's SQL tools, Maselow and his colleagues are able to quickly verify an entire database, seeing where changes need to be made and doing a one-minute fix. At times, this new procedure eliminates a 20- to 30-hour manual process.

"We have eliminated having to look at data and procedure problems," says Maselow. "With SQL Compare^®, we can narrow each problem down to the data itself. Then, we can use SQL Data Compare to catch errant data."

Better quality, less time

In addition to the time savings, SQL Compare allows Maselow's department to be consistent with changes and procedures. His group has different requirements for CMM (capabilities maturity model) and Sigma, both means of ensuring a repeatable process method. Using SQL Compare to validate the processes helps Maselow prove that the methodology remains the same each time.

"When we turn over a database to QA (quality assurance), it has to be perfect," says Maselow. "A database change that takes 15 hours can be difficult to validate manually due to the time involved. With the reports from SQL Compare, we can ensure that each change was done in the same manner and is repeatable."

One of Maselow's only regrets is that he didn't have this kind of tool sooner. "I've been working with SQL databases for more than 10 years," he says. "If these tools had been around a couple of years ago when I was a consultant, I would have been considered a god."

###

Erin Hatfield is a freelance writer specializing in IT and other technology topics. She works for Cramblitt & Company in Cary, N.C.