Database Change Management Best Practices: Achieving an Automated Approach and Version Control Part 3

In part 2 Darren Fuller examined an automated approach to database change management and outlined the essential elements of an automated methodology. In this third article of a 4 part series, he lists the requirements to implement an automated approach and highlights some of the benefits.

Implementing an Automated Approach

There are probably numerous ways to implement the specific tasks of an automated approach. This is due to the complexities and uniqueness of each environment. But a typical implementation of this methodology would involve the following components (see Figure 2):

a. As a one time process, script out your entire production database’s schema and static data into individual scripts and lodge these into your version control system (VCS). Instruct the development team to use them in the same manner that they would VB, ASP, C++ or Java code.

b. Decide whether each developer will use a local development database, which is a subset of production, or use a central, shared development repository.

c. Provide each developer with the ability to perform an ad hoc or scheduled update of their local database with the change control tool.  Or setup a similar scheduled update if using a shared environment.

d. From that point on, in order to make a change, a developer would:

• check out the relevant scripts
• make the required amendments
• execute it against their local (or shared) development database
• perform unit testing and bug fixing
• use the change control tool to extract the latest version of scripts from the VCS (which incorporates all other developers changes)
• run the change control tool to build a database locally, using the scripts from the VCS and the developer’s own changes (to verify that nothing else has been broken)
• then check the relevant scripts back into the VCS

e. Use the change control tool to execute a scheduled (hourly or daily) build of the entire schema and code to a “production-sized” build database to verify successful compilation. Label (or snapshot) the source code at these regular intervals to have a named, point-in-time view of your entire database.

f. To propagate and deploy changes to your various environments, you connect the change control tool to the labelled scripts within the VCS and to your target database. The change control tool will then verify that all the scripts compile, with no dependency errors, and make all necessary changes to the target database to ensure that it matches the scripts.

Figure 2: An Automated Database Change Management Solution – Logical Diagram

Benefits of an Automated Approach

1. Improved Quality and Faster Software Delivery

Local Configuration

The use of a local development database means that no developer can be prevented from delivering their changes just because someone else has broken a shared database halfway through unit testing. A regular, automated update of this database from the base scripts in the VCS enables a thorough and swift examination of the developer’s changes. This configuration encourages the developer to deliver an improved quality of code and ensures that what ends up in the VCS

is of a high standard.

Scheduled Build Verification

The continual testing and verification of deployment via a scheduled build of the code base helps detect bugs, such as syntax, regression and dependency errors, much earlier and while fresh in the developer’s mind. The end result is faster software delivery, enhanced quality and integrity of code along with the avoidance of costly and stressful “fire-fighting” at release time.

Elimination of Manual Tasks

One of the main aims of an automated methodology is to systematise change propagation and deployment. Why perform mundane tasks such as table modifications that lend themselves so well to automation?  Why, in effect, re-invent the wheel every time a deployment is due? This point is highlighted even further, if you need to deploy to several environments, with possibly hundreds of databases, on the way to production release.

An automated methodology can deploy in a totally controlled, repeatable and reliable manner. Ultimately, elimination of manual tasks will:

• minimise the cost of development projects
• eradicate errors
• reduce the length of the development life cycle
• reduce anxiety within the project team

2. Auditable and Version Control

It is crucial to have the ability to label (snapshot) database code and thereby provide an audit trail and versioning of your database.  This provides reliable information on how, when and by whom the database code has changed. It also paves the way for the painless maintenance of static data, which can also break a database when data is lost, overwritten or out of synch.  An automated approach with version control enables you to leverage the benefits of your VCS for your database repositories.

3. Sarbanes Oxley Compliance

In the wake of recent high-profile financial scandals, companies today have become more accountable for the integrity of their financial records and practices. Recent introduction of new legislation, such as the Sarbanes Oxley Act (SOX) in the United States, affects the IT departments whose job it is to store a corporation's electronic records.  To satisfy the requirements of SOX, an IT department needs the ability to create and maintain a corporate record archive in a cost-effective manner. An automated methodology for database change will help make your systems auditable and assist efforts to meet corporate governance requirements.

Next Article

In the fourth and final article in this series, I will continue the list of benefits available by adopting an automated approach to database change management and discuss implementation considerations.

About the Author

Darren Fuller began his IT career in 1990. Much of his experience has been gained by providing database consultancy on mission-critical systems and development projects within large organisations, often within heterogeneous environments using multiple technologies.  Darren has specialised in database technologies with a particular interest in troubleshooting, optimisation and tuning, especially with Microsoft SQL Server (since 1994).  He has a passion for ensuring a database system is finely tuned and the processes within a development project help to achieve this aim in an efficient and cost-effective manner.

Darren holds a Bachelor of Business in Computing and a Microsoft MCSE certification. He welcomes any feedback and can be contacted at darren.fuller@innovartis.co.uk

© Copyright Innovartis Ltd 2004. All rights reserved.