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 ‘220.127.116.11’
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).
– the first script to be run in a deployment and automatically checks the build version
– 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 = '18.104.22.168'
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 = '22.214.171.124'
When this is deployed into production it will insert the new value into dbo.BuildVersion table
And the cycle will begin again.
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?