Media corporation uses SQL Comparison SDK to bring databases into continuous integration fold

As an international media corporation branches out into auxiliary products such as toys and DVDs to enhance profits, it faces the challenges of maintaining both its internal IT systems and a fast-growing web enterprise.

The company's business applications team consists of around 60 people, including 20 full-time development staff. Having so many people working on projects simultaneously creates a number of difficult integration problems. Marc Holmes, head of business applications, uses a concept called "Continuous Integration" to solve these problems.

Continuous Integration (CI) aims to create a repeatable and automated build process. Builds are automatically carried out at least every day. Whenever developers check in source code or modify build scripts, the product being worked on is rebuilt and automated tests are run. Changes are generally small, and builds frequent, so integration problems are highlighted early on and can be fixed quickly. Although continuous integration is commonly used in extreme programming, it has a much wider appeal.

"We are fans of agile development, but our business culture does not necessarily allow us to fully implement disciplines such as extreme programming," says Holmes. "Instead, we use features of the 'agile SDK' to achieve some or all of the benefits they support. In particular, CI's approach to risk reduction and confidence are a big plus."

One of Holmes' key goals is to "decouple the number of systems from the number of people required to cover releases of those systems." In other words, to keep headcount the same as databases and systems grow.

Stretching CI boundaries

By the standards of software development, continuous integration is a fairly mature concept; there are a number of tools, especially in the Java environment, to help in its implementation.

Continuous integration has commonly been used for compilation of programs, but not for database development. Although there are several books on the subject, the practical problems of continuously integrated database development are thorny enough to resist easy, practical resolution.

The media company's business applications team is using a combination of Cruise Control .NET, NAnt, and Red Gate Software's SQL Comparison SDK to unpick the knots in agile database development. Holmes says it was a logical step to use the Red Gate APIs in the SQL Comparison SDK within the NAnt/CI process.

"The problem is typically the lack of a 'rollback'," says Holmes. "Whereas code is compiled from scratch, database integration is performed against the previous version, so the incremental history and its maintenance are vital. In the event of an integration failure, database integration is likely still valid and so needs to be maintained as part of this history."

In the team's CI implementation, when a build is started, a NAnt script carries out a five-step process:

• It cleans the build environment. This involves removing directories and general initialization work.
• It compiles the source code.
• It tests the new build using automated NUnit scripts.
• It analyzes the source code, using FXCop to find common security and coding issues.
• It publishes the build by zipping up the compiled executables.

This process is a fairly common example of continuous integration, but only deals with code changes. Database changes are notoriously difficult to manage and track. To deal with this problem, the business applications team has introduced some extra steps.

The development team works on a common development database. In addition to the development database, there are integration and live databases. A number of NAnt scripts slot into the standard build process.

Before the compilation, a NAnt script uses Red Gate's SQL Comparison and Synchronization SDK to script the entire development database. This script is then stored in a source control system, giving an entire history of the database, build by build and day by day. The script is stored even if the build subsequently fails.

After program compilation and NUnit tests have been run on the development database, a NAnt task is run to compare the development and integration databases and create an ALTER script. This is a T-SQL script containing changes made by all of the developers since the previous build. Again, the scripts are stored in a source control system for a complete history of database changes.

Next, the ALTER scripts are run on the integration database to update it with the changes made to the development database. Lastly, the unit tests are run on the integration database.

Speed, confidence, quality

The CI process gives Holmes and his team a complete history of the database and all the changes made to it. At each major milestone, they can roll out all the historic changes made since the last milestone to the production database.

By using Red Gate's SQL Comparison SDK to apply continuous integration to database development, the business applications team has managed to reduce integration problems, while increasing speed, confidence and the quality of releases.

"A typical release now takes minutes instead of hours," says Holmes. "All releases occur in largely the same way across dozens of projects."

Holmes is confident that the organization will not require further staffing as the number of projects increase. The automated tools also reduce the pressure of instilling disciplined processes across a diverse and busy organization, a difficult challenge according to Holmes.

"Most papers describe discipline as a major driver for success," he says. "I agree with these views, but ideally tools such as Red Gate's can provide automation to lower the level of discipline required for a busy shop."

###

Resources:

Marc Holmes' book, Expert .NET Delivery Using NAnt and CruiseControl.NET, is a guide to the implementation of good delivery processes in a .NET environment. It is available from apress.com. Additional information on agile modeling and continuous integration can be found at:
www.agilemodeling.com
www.martinfowler.com