SCOPE iT uses SQL Compare to improve internal SQL update processes

Introduction

SCOPE iT, a web-based cost estimation and project planning company, has expertise in improving business processes for companies ranging from small businesses to Fortune 100 companies. SCOPE iT decided to put that expertise to use internally after facing repeated delays when updating the company's SQL databases.

Managing secure internal processes

SCOPE iT's eight SQL servers are equally divided between development and production. Each server houses six to eight databases depending on their use in the updating process. The databases store customer data as well as infrastructure-related configuration and content management for SCOPE iT's public web presence.

The company has one dedicated SQL Server DBA who manages the servers. This entails coordinating as many as 12 developers who could be making changes to the same databases at the same time.

SCOPE iT's sensitive customer data is secured by military-grade encryption to meet the strict security requirements of its customers. The employees who work with the information have varying levels of access depending on the security requirements, and each database is accessed from various locations.

Any changes to data, new features and applications require updating the database schema. SCOPE iT has very detailed processes in place for development and production databases to help keep synchronization under control and ensure that the entire process is repeatable.

But even with such steps in place, the DBA and SQL developers encountered roadblocks when updating the databases. Each change was scripted at the time of the change and was housed in a central location within SCOPE iT's source control. Once the new version of the application and databases were ready for testing, SCOPE iT's DBA had to combine the change scripts and make sure the database object dependencies were accounted for in the proper order. On average, it took up to two weeks to analyze, code and test synchronization scripts from the database developers.

"This process resulted in huge bottleneck when moving from development to production," says Keith Harvey, SCOPE iT's CTO. "Our development and testing cycle is fairly short, and often we were faced with change scripts that were flawed or incomplete, delaying the entire process."

An impressive ROI

Looking to speed the cycle and decrease the possibility of human error, SCOPE iT began evaluating tools designed to automatically compare SQL database and create synchronization scripts. The company came across Red Gate's SQL Compare database synchronization tool in its search.

SCOPE iT now runs a more simplified process for database structure changes. The team still communicates closely when database structure changes are needed, but the change scripts are built by SQL Compare instead of the developers.

Setting up SQL Compare to run a comparison.

SQL Compare examines the actual databases, rather than basing a change script upon a list recorded by developers. The program makes a SQL request to the database server for structural information about the development database, and then accesses the other database server for structural information about the RC and QA databases. The tool sorts and manipulates this information internally and presents comparisons of the database structures to the DBA. The DBA chooses the changes he wishes to make, and SQL Compare creates the necessary scripts and runs them on the database server.

"The speed and accuracy of SQL Compare are just unmatched," Harvey says. "Our databases are quite complex, but SQL Compare can do a full analysis and script generation in less than a minute."

These automatically generated scripts have reduced the development and testing cycle from weeks to less than a day. Harvey's team can now focus on improving its internal application and the end-user experience for the client. SCOPE iT's DBA has more free time to improve SQL performance and write scripts that modify and migrate customer data.

"The modification and migration scripts our developers create are some of the company's most valuable resources," says Harvey. "I continue to be impressed by the magnitude of this return on investment."

At the end of the process, SQL Compare shows Scope iT's databases in synch.

Further information

Further information is available at Red Gate's website (www.red-gate.com).

To see how well SQL Compare will work for you, download a fully functional, fully supported free trial.