Click here to Skip to main content
15,353,757 members
Articles / Programming Languages / SQL
Posted 19 Jan 2005


1 bookmarked

SCOPE iT uses SQL Compare to improve internal SQL update processes

19 Jan 20054 min read
It's a high-tech case of "physician, heal thyself."

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.


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.

Image 1
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."

Image 2

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 (

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


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Redgate Software
Red Gate Software Ltd.
United Kingdom United Kingdom
Redgate makes ingeniously simple software used by 804,745 IT professionals and counting, and is the leading Microsoft SQL Server tools vendor. Our philosophy is to design highly usable, reliable tools which elegantly solve the problems developers and DBAs face every day, and help them adopt database DevOps. As a result, more than 100,000 companies use products in the Redgate SQL Toolbelt, including 91% of those in the Fortune 100.
Group type: Organisation

1 members

Comments and Discussions

-- There are no messages in this forum --