Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DB Change Management: An Automated Approach - Part 3

By Darren Fuller,

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


Click image for a larger version

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.

Total article views: 9315 | Views in the last 30 days: 6
 
Related Articles
ARTICLE

DB Change Management - An Automated Approach - Part 4

The final installment in Darren Fuller's series on change management in SQL Server. If you haven't r...

ARTICLE

DB Change Management - An Automated Approach - Part 2

The second part of the series by Darren Fuller on SQL Server change management, version control, and...

ARTICLE

Database Change Management: An Automated Approach

SQL Server change management is tough. The tools don't integrate with version control systems, there...

SCRIPT

Change Database Collation

A stored procedure to automate database collation change

ARTICLE

Improving database development with SQL Source Control

A webinar sponsored by Red Gate Software showing you how easy it can be to source control your datab...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones