Including a version number with your database schema

Last modified: 2010-11-13

How does a dba/developer quickly check that the database schema version they are working on is correct?

Before each deployment is there away to you automatically test that your environments are using the correct database version?

This blog will show how including a version number in the database schema can help do this.

Overview of the overall process

  • Build version can be auto generated/incremented by build tools.
  • Table is created in the schema to store this.
  • First script of each deployment checks the last build version is as expected.
  • Last script updates with the new build version.

Use a build tool to auto generate the build version

Most build tools include functionality to automatically create build versions.

For example, Nant has a task (function) Version that does this.

I follow a four part build version convention – Major.Minor.Maintenance.Build

The first three parts are manually maintained by editing the appropriate Nant build file.

The 4th part – ‘.Build’ – number is auto incremented with each build.

e.g. the first production release of a database might have a build version of ‘1.0.0.234’

It is major release ‘1’ and gone through ‘234’ builds.

Nant token replacement

It is possible to include tokens in scripts that can be automatically replaced by variables created in the build process.

For example, a token such as‘@BUILD_VERSION_NUMBER@’ can be replaced by the latest build version.

This can then be inserted into SQL scripts on each build.

Storing the build version in the database

By default I include a table – dbo.BuildVersion in all new databases.

This is used to store build version data (the checksum part will be explained in a later blog).

A script similar to the following can be used to create this table:

CREATE TABLE dbo.BuildVersion
(
	BuildLabel varchar(20) NOT NULL,
	BuildDate datetime CONSTRAINT [df_BuildVersion_BuildDate] DEFAULT getdate() NOT NULL,
	CheckSumConstraint bigint NOT NULL,
	CheckSumTableViewColumn bigint NOT NULL,
	CheckSumRoutine bigint NOT NULL
)

ALTER TABLE dbo.BuildVersion
ADD CONSTRAINT [pk_BuildVersion] PRIMARY KEY CLUSTERED (BuildLabel)

Carrying out an ad hoc check

Each production deployment updates the dbo.BuildVersion table with the latest build version.

The latest production schema is used as the baseline database for all environments (see Agile database development 101). Therefore, it’s last entry is the target/expected build version for all environments and can be used to ensure all copies of the schema are correct.

A dba/dev can quickly run a query similar to the following to check that their copy of the schema is correct.

SELECT BuildLabel
FROM dbo.BuildVersion
WHERE BuildDate = (SELECT MAX(BuildDate) FROM dbo.BuildVersion)

Automatically testing before deploying

The continuous deployment process can also automatically test that the expected base line database schema is as expected in all environments.

Two scripts are provided to help do this (see ‘Source Code’ section at the end).

These are placed in the ‘WorkingDirectory’ (a blog later in the series will explain the solution/folder structure used).

000_DataSelect_BuildVersion_CheckBeforeBuild.sql

– the first script to be run in a deployment and automatically checks the build version

999_DataInsert_BuildVersion_EndOfBuild.sql

– the last script to be run in a deployment and automatically updates the table with the new build version

In the first script a variable is manually updated after each production deployment with the latest build version.

For example it might be:

SET @expectedBuildVersion = '1.0.0.234'

On deployment the script uses a query similar to the one above to check the actual value of the schema being worked on.

If the actual value is different to @expectedBuildVersion then an error is raised and the team is notified.

Updating with the latest build version

The last script file – 999_DataInsert_BuildVersion_EndOfBuild.sql – contains the following text:

SET @buildVersion = '@BUILD_VERSION_NUMBER@'

On creating the deployment files, this token will be replaced by the new build version created by the build tool – e.g.:

SET @buildVersion = '2.0.0.235'

When this is deployed into production it will insert the new value into dbo.BuildVersion table

And the cycle will begin again.

Source code

All example scripts are provided in the database testing framework which can be downloaded from here.

What next?

This has shown a relatively simple way to version a database schema.

But, whilst this is a good start, having the expected/correct build version label on a database schema doesn’t actually indicate that:

  • All of the expected SQL objects are present.
  • None of the SQL objects have had ‘unexpected’ changes made to them.

The next blog in this series will show how a simple test using checksums can help to do this.
What do you think?

Do you use a similar/different method to version your database schema?

Advertisements

One Response to Including a version number with your database schema

  1. Ben says:

    Hi Michael,

    Are you aware that there is a commercial packages available that achieves what you are doing (and more)?
    See http://www.dbmaestro.com

    If you have questions, feel free to contact me,

    Regards, Ben

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: