Versioning individual SQL objects

How can you quickly check which build version a SQL object in your database was created/last altered in?

How can you correlate changes to individual SQL objects with the overall application build/deployment?

A previous post has shown how to include a build version with the overall database schema.

This blog will look at:

• How this concept can also be used for individual SQL objects.
• How extended properties in MS SQL Server can be used to help with versioning.

Overview

• A build version can be auto generated/incremented by build tools such as Nant.
• Each create/alter script has a token in it. On each build this is replaced with the auto generated version number .
• Extended properties can be used to store metadata about individual SQL objects.
• A sproc is provided that makes it easier to insert/update extended properties.
• Each create/alter script also includes a section where the build version extended property is inserted/updated.

Using a build tool to auto generate the build version

See this section in previous post.

Nant token replacement

See this section in previous post.

How it works

All create/alter scripts have a section similar to that shown below – this example is for a sproc:

CREATE Procedure dbo.p_SprocName
AS
/*********************************************
* PURPOSE: See sys.extended_properties
* LAST CHANGED IN BUILD: @BUILD_VERSION_NUMBER@
*********************************************/

A token @BUILD_VERSION_NUMBER@ is included in the comments section.

During a build this is replaced with an auto generated build number.

For example, to something like the following:

/*********************************************
* PURPOSE: See sys.extended_properties
* LAST CHANGED IN BUILD: 1.0.0.234
*********************************************/

In this way, each individual script file can be labelled with the latest build number.

As the build version is now inserted into the comments section of the script, it allows a dba/dev a number of ways to quickly check the build an individual SQL object was created/altered in. For example:

• Viewing the script that was last deployed. See later blog on how to manage SQL script and using source control.
• If automatic schema tracking is being used then the last log entry for an object in the dbo.EventLog table will contain the ‘build version comment’ in the T-SQL statement.
• Viewing the ‘create script’ in tools such as MS SQL Management Studio.
• Viewing definitions in internal system views. For example:

  SELECT VIEW_DEFINITION 
  FROM INFORMATION_SCHEMA.VIEWS
  
  SELECT ROUTINE_DEFINITION 
  FROM INFORMATION_SCHEMA.ROUTINES
  

  This method can also be useful for checking the SQL objects changed in a previous build. For example, if the last build was ’1.0.0.234′, then all the sprocs/functions that were created/altered in that build will have the text – ’1.0.0.234′ – in their definitions. Therefore the following query will find them:

  SELECT * 
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_DEFINITION LIKE '%1.0.0.234%'
  

*Worth noting the last two methods won’t work for base tables.

Extended properties

The process outlined above can be used for any SQL dbms – as it only involves token replacement in script files.

The following two sections are specific to MS SQL Server showing how extended properties can be used.

Extended properties allow metadata to be added to SQL objects as name/value pairs.

Data that is added can be viewed in Management Studio – as shown in the image below:

MS extended properties example

It can also be viewed by running queries on the system view sys.extended_properties similar to those below:

--SQL object that are not columns or child objects
SELECT s.name as 'Schema',o.name as 'ObjectName', p.name, p.value
FROM sys.extended_properties as p
INNER JOIN sys.objects as o on p.major_id=o.object_id
INNER JOIN sys.schemas as s on o.schema_id=s.schema_id
WHERE p.minor_id = 0 -- columns of an object <> 0
ORDER BY s.name, o.name, p.name

--SQL columns
SELECT s.name as 'Schema',o.name as 'ObjectName', c.name as 'ColumnName', p.name, p.value
FROM sys.schemas as s
INNER JOIN sys.objects as o ON o.schema_id=s.schema_id
INNER JOIN sys.columns as c ON c.object_id = o.object_id
INNER JOIN sys.extended_properties as p on p.minor_id = c.column_id AND p.major_id=c.object_id
ORDER BY s.name, o.name, c.name, p.name

MS also provides a function – fn_listextendedproperty – that does a similar thing.

Using extended properties

MS provides 2 sprocs

• sys.sp_addextendedproperty for inserting new values.
• sys.sp_updateextendedproperty for updating existing values.

The example below shows how to carry out an insert using sys.sp_addextendedproperty:

EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Minimum inventory quantity.',
@level0type = N'SCHEMA', @level0name = Production,
@level1type = N'TABLE',  @level1name = Product,
@level2type = N'COLUMN', @level2name = SafetyStockLevel;

For versioning builds, working with these sprocs can be difficult:

• A dba/dev needs to know whether to use an insert or an update. Choose incorrectly and an error will occur. This makes it difficult to add a similar ‘script snippet’ to both create/alter scripts.
• The API is quite verbose – having to add the SQL object type eg @level1type = N’TABLE’.
• The sproc has to be executed for each individual name/value pair being entered.

To make it easier to work with a ‘wrapper sproc’ – 008_CreateProc_p_ExtendedProperty_UpSert.sql – is provided. See Source Code section below.

The following example shows how this sproc can be used:

EXEC dbo.p_ExtendedProperty_UpSert
@schemaName='dbo'
,@objectNameLevel01='ObjectName'
,@lastUpdatedBy='mbaylon' -- developers name
,@desc='put any notes in here'
,@buildVersion='@BUILD_VERSION_NUMBER@'

For a ‘child object’ – a ‘@level2′ in sys.sp_addextendedproperty terms – such as a column, the following parameter would need to be added:

,@objectNameLevel02='ColumnName'

This sproc does a number of things:

• It checks if a property already exists and therefore can be used for both creates and alters scripts.
• Creates four predefined properties – ‘Description’, ‘LastUpdatedBuildVersion’, ‘LastUpdatedBy’, ‘LastUpdatedDate’.
• On each build the token @BUILD_VERSION_NUMBER@ is automatically replaced with an autogenerated build number on each build. In this way, the extended property for a changed SQL object is updated on each build.

The following image shows the extended properties for the dbo.BuildVersion table:

MS extended properties including a build version

A ‘script snippet’, that executes this sproc, is included at the end of all create/alter scripts and in this way the extended properties for each SQL object are kept up to date.

The default SQL objects provided in the latest version of the database testing framework have many examples of how to use this.

Advantages

• Each individual SQL object is labelled with a build version allowing it to be correlated with the overall application build/deployment.
• It is relatively easy to use with relatively little overhead – just include in a script templates.
• As outlined above, there are a number of different ways in which the build version can quickly checked.

Gotchas

• The sproc has 4 predefined attributes. These might not suit everyone’s needs. Though it would be relatively straight forward to change the sproc.
• The sproc provided only covers the following level01 SQL objects – table, view, procedure, function, synonym and level02 SQL objects – constraint, column, parameter, trigger.

Source code

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

The current version of the database testing framework does not currently include the Nant build files that are used for token replacement. If anyone would like to start using this then feel free to contact me and I will look at providing these files.

References and links

Michael Coles’ – Easy extended properties – provides a good overview and includes a really good generic sproc for working with extended properties.

What next

This has shown how individual SQL objects can be included in the overall ‘build versioning’ of a database schema.

The next blog in this series will look at how database unit testing can be used as the ultimate check in ensuring that a database schema is exactly as required.

What do you think?

How do you include ‘build versioning’ for SQL objects?

Automatically tracking database schema changes

Last modified: 2010-11-14

How can you quickly find out what changes have recently been made to your database schema?

Previous blogs have shown how to include version numbers with a database and how checksums can help ensure a schema is as expected.

But, what do you do if you find that the schema has been ‘unexpectedly’* changed?

This blog will look at how DDL triggers and logging can be used to track changes in your schema and help trouble shoot if there are issues.

* Someone has changed it outside of the standard deployment process.

Overview

• DDL triggers can be used to automatically track schema changes.
• Table is created in the schema to log these changes
• This table can be queried to find recent changes.

Note
Thank-you to those who pointed out that MS SQL 2K8 enterprise edition and Oracle have schema auditing functionality built in – see LinkedIn discussion from a previous blog

The MS sample database, AdventureWorks, contains similar functionality to that outlined in this blog.

DDL triggers

DDL triggers were introduced in MS SQL 2K5 and can be used to track schema changes.

They fire primarily in response to TSQL statements that start with the keywords CREATE, ALTER, DROP and GRANT.

They give access to the EVENTDATA function which has a whole host of useful info – including the TSQL statement that was executed.

A script – 007_CreateTrigger_tr_DDL_Event.sql – is provided which includes an example trigger that will capture DDL events and logs them.

The following ‘snippets’ highlight and explain some of the key sections in this trigger:

CREATE TRIGGER tr_DDL_Event
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS

‘DDL_DATABASE_LEVEL_EVENTS’ capturing DDL events for this database.

SET @data = EVENTDATA()

SET @eventName = COALESCE(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),'')
SET @eventMsg = 'SchemaName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'),'')
SET @eventMsg = @eventMsg + ', ObjectName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),'')
SET @eventMsg = @eventMsg + ', TSQLCommand: ' + COALESCE(@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),'')
SET @eventMsg = @eventMsg + ', LoginName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),'')
SET @eventMsg = @eventMsg + ', UserName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'),'')

‘SET @data = EVENTDATA()’ - capturing the DDL event data (of type XML).

XQueries are run to extract different elements eg ‘TSQL command’ and putting them into a variable @eventMsg.

EXEC dbo.p_EventLog_CreateRecord
@eventType='INFORMATION'
,@programName='tr_DDL_Event'
,@eventName=@eventName
,@eventMsg=@eventMsg

This data is then logged to a table dbo.EventLog (see details below) using a sproc dbo.p_EventLog_CreateRecord.

Note the parameter – ‘,@programName=’tr_DDL_Event’- this is used later as a query filter on dbo.Eventlog.

Storing the changes

By default I include a table – dbo.EventLog and associated sprocs – in all new databases.

This is used to store DDL event logs and for other general logging purposes.

This table can be created using a script similar to that shown below:

 CREATE TABLE dbo.EventLog
 (
	EventLogId int identity(1, 1) NOT NULL,
	EventType nvarchar(15) NOT NULL,
	ProgramName nvarchar(128) NOT NULL,
	CallingProcedure nvarchar(128) NOT NULL,
	EventMsg nvarchar(max) NOT NULL,
	ProgramSectionName nvarchar(128) NOT NULL,
	EventName nvarchar(128) NOT NULL,
	CreatedDate datetime NOT NULL,
	BatchId nvarchar(128) NOT NULL,
	CreatedBy nvarchar(128) NOT NULL
 )
 ALTER TABLE dbo.EventLog

ADD CONSTRAINT  pk_EventLog PRIMARY KEY CLUSTERED ( EventLogId ),

ADD CONSTRAINT ck_EventLog_EventType CHECK (EventType IN ('Success','Error','Information','Warning'))

How this works

If the following script – to create a sproc – is run on the database:

CREATE PROCEDURE dbo.p_TestSproc
AS
BEGIN
	PRINT 'Test sproc'
END
GO

Then this create event will be logged via the DDL trigger and stored in dbo.EventLog

The following query will return the schema changes that have just been made.

SELECT EventName, EventMsg, CreateDate, UserName
FROM dbo.EventLog
WHERE ProgramName='tr_DDL_Event'
ORDER BY EventLogId desc

The first row returned is as follows (note only showing first two columns):

“CREATE_PROCEDURE”,
“SchemaName: dbo, ObjectName: p_TestSproc, TSQLCommand: CREATE PROCEDURE dbo.p_TestSproc AS BEGIN PRINT ‘Test sproc’ END , LoginName: Login, UserName: User”

If a SQL user is then given exec permissions on this sproc – the following row is returned:

“GRANT_DATABASE”,
“SchemaName: dbo, ObjectName: p_TestSproc, TSQLCommand: GRANT EXECUTE ON [dbo].[p_TestSproc] TO [aspnet_Roles_BasicAccess], LoginName: Login, UserName: User”

If the sproc is dropped – the following:

“DROP_PROCEDURE”,
“SchemaName: dbo, ObjectName: p_TestSproc, TSQLCommand: DROP PROCEDURE dbo.p_TestSproc, LoginName: Login, UserName: User”

What do you do if your database has changed?

You want to refresh you dev and testing environments with the latest copy of the production database.

You’ve received a copy. Initial checks show that the the version number is as expected – but the checksums are not, indicating the schema has had some ‘unexpected’ changes.

What do you do?

Firstly, you probably want of find what unexpected changes have been made to the production system.

But, perhaps more importantly, there is a need to review the current release process and to improve on it so that there is less chance of unexpected changes occurring in the future.

Tracking database schema changes can provide the – what, when, and potentially who – to help in improving the process.

Finding out the differences and tracking down the changes.

To find the unexpected changes a tool such as RedGate SQL Compare can be used.

The production copy will need to be compared to a copy of the database that has the correct/expected schema*.

This process will outline the differences – and the unexpected changes made to the production database. These differences can then be correlated with entries in the dbo.EventLog table. This information can then be used to help in improving the deployment/change control process.

*A future blog will show how database unit testing can help ensure that you have a correct copy of the database schema.

Advantages of this approach

• Help in trouble shooting. As outlined above, this can be used to track unexpected changes. But, it can also help if there are issues with official deployments as operational/support staff can quickly identify recent changes to a database as part of their overall trouble shooting procedures.
• An aid in development. The log table can be used to quickly check what changes have been made and the order they have been applied – including security/permission changes.
• Documentation. This is similar to the point above. DBAs often want to know what is going to be changed in a deployment. This can quickly provide this information.

Gotchas

• The example DDL trigger provided does not log all the available event data – see EVENTDATA function. It could be easily extended to do so eg add something like the following to the script:

  SET @eventMsg = @eventMsg + convert(nvarchar(max),@data);
  

• This is being used as an aid to the SDLC/database development. It is not intended to be part of a ‘formal’ security audit on a database.

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

What next

This blog has shown how schema changes can be tracked and how this can provide value especially when troubleshooting unexpected changes.

The next blog in this series will look how individual SQL objects can be labelled with build version numbers making it easier to:

• Find out when a SQL object was last changed.
• Correlate these changes with builds/deployments.

What do you think?

How do you track database changes?

Using checksums to test for unexpected database schema changes

Last modified: 2010-11-13

How does a dba/developer quickly check that the baseline database schema they are using has not had any ‘unexpected’ changes made to it?

How can these checks be automated so that they are part of a continuous deployment process for all environments ?

The previous blog in this series showed how to include a version number with your database schema*. This on its own provides value, but, even if the build version label is correct, the schema might have changed. A SQL object dropped or altered by accident – a change made outside of standard deployment procedures!

This blog shows how using checksums can test that the baseline schema is as expected.

* This blog assumes you have read the previous one in the series.

Overview

• Checksums can be used to detect changes in schema.
• Checksums values can be computed using queries on INFORMATION_SCHEMA.
• dbo.BuildVersion table stores these values.
• First script of each deployment checks the checksum values are as expected.
• Last script updates with the new checksum values.

What are checksums?

A checksum is a computed value that is typically used to check/compare files. They can also be used to detect changes in the values in tables/views. MS SQL includes a number of functions that can be used for this – e.g. CHECKSUM_AGG

How they can be used to detect changes in schema

The following checksum query was run on a copy of the AdventureWorks database – it returned: -1697427893

SELECT
CHECKSUM_AGG(CHECKSUM
(TABLE_NAME
,COLUMN_NAME
,CHARACTER_MAXIMUM_LENGTH))
FROM INFORMATION_SCHEMA.COLUMNS

A column was then added to a table using the following script:

ALTER TABLE HumanResources.Department ADD TestColumn nvarchar(50);

The query was run again – this time giving a value of: -326082783

As the schema was changed – the values in the INFORMATION_SCHEMA views changed – therefore the computed checksum changed.

What about the overall schema?

The query above only covers a small number of columns in the INFORMATION_SCHEMA views. If this technique is to be used – other columns e.g. DATA_TYPE need to be included. Also what about other types of SQL objects?

I have found that checking the following areas gives me a good enough indication if the schema has changed:

Table/view columns, constraints and sproc/functions.

But if required it would be relatively easy to extend the coverage – [see 'Gotchas' section below].

Three functions are provided to do this – (see ‘Source’ below):

dbo.fn_CheckSum_TableViewColumn – checksum based on join of tables/views and their columns
dbo.fn_CheckSum_TableConstraint - checksum based on table/view constraints e.g. fk, unique, defaults
dbo.fn_CheckSum_Routine - checksum based on sproc/function – includes full routine definition

This is an example of a checksum query used in dbo.fn_CheckSum_TableViewColumn:

SELECT CHECKSUM_AGG(CHECKSUM(
t.TABLE_SCHEMA
, t.TABLE_NAME
, t.TABLE_TYPE
, c.COLUMN_NAME
, c.ORDINAL_POSITION
, c.COLUMN_DEFAULT
, c.IS_NULLABLE
, c.DATA_TYPE
, c.CHARACTER_MAXIMUM_LENGTH
, c.CHARACTER_OCTET_LENGTH
, c.NUMERIC_PRECISION
, c.NUMERIC_PRECISION_RADIX
, c.NUMERIC_SCALE
, c.DATETIME_PRECISION
, c.CHARACTER_SET_CATALOG
, c.CHARACTER_SET_SCHEMA
, c.CHARACTER_SET_NAME
, c.COLLATION_CATALOG
, c.COLLATION_SCHEMA
, c.COLLATION_NAME
, c.DOMAIN_SCHEMA
, c.DOMAIN_NAME))
FROM INFORMATION_SCHEMA.TABLES AS t LEFT OUTER JOIN
INFORMATION_SCHEMA.COLUMNS AS c ON t.TABLE_NAME = c.TABLE_NAME

Storing the checksums in dbo.BuildVersion

See the previous post for details on the dbo.BuildVersion table.

Carrying out an ad hoc check

Each production deployment updates the dbo.BuildVersion table with the latest checksum values.

The latest production schema is used as the baseline database for all environments (see Agile database development 101). Therefore, its last entry contains the expected checksums 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 below to get the latest expected checksum values:

SELECT CheckSumTableViewColumn
, CheckSumConstraint
, CheckSumRoutine
FROM dbo.BuildVersion
WHERE BuildDate=(SELECT MAX(BuildDate) FROM dbo.BuildVersion)

These can then be compared to the actual/current values of the database schema – using the example functions provided:

SELECT dbo.fn_CheckSum_TableViewColumn()
SELECT dbo.fn_CheckSum_TableConstraint()
SELECT dbo.fn_CheckSum_Routine()

If they return different values then an unexpected change has been made to the baseline schema.

Automatically testing before deploying

As per the previous post the first script that runs is – 000_DataSelect_BuildVersion_CheckBeforeBuild.sql

In this script, checksum variables are manually updated after each production deployment – to give the ‘baseline expectations’.

SET @expectedBuildVersion = '1.0.0.234'
SET @expectedCheckSumTableViewColumn = 1980786926
SET @expectedCheckSumConstraints = 1866989356
SET @expectedCheckSumRoutine = 476203398

It then uses the query and functions outlined above to compare expected vs actual.

If they are different it raises an error and the team can be notified.

Updating with the latest checksums

As per the previous post the last script that runs, after all changes have been deployed, is – 999_DataInsert_BuildVersion_EndOfBuild.sql
This runs the functions above to calculate the latest schema checksum values. It then inserts these values into dbo.BuildVersion.

And so the cycle begins again.

Gotchas

• The example scripts provided do not cover all types of SQL objects e.g. triggers, indexes. Therefore, they will not ‘spot’ all potential changes to a schema.
• They do not cover all columns in the selected INFORMATION_SCHEMA views.
• If the above points are issues then it would be relatively straightforward to extend the example scripts provided to cover other types of objects/columns.
• Environmental variables changing scripts can cause issues.
  For example, using physical server names for linked servers and referencing these in sprocs/function. It is very likely, and desirable, that the server names change as you go from dev to production. If the physical names are referenced directly in scripts checksum values will change e.g. routine definitions are included in checksum query. There are a number of ways to get around this e.g. use synonyms or ‘abstract’ the physical server name away when creating the linked server. This has the added advantage of making deployments easier to manage as the name only has to change in one place not in multiple scripts.

Source code

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

What next

This has shown how checksums can be used to test if your schema has had unexpected changes made to it.

But, what do you do if you uncover a change?

The next blog in this series will look how we can automatically track schema changes and use this to help trouble shoot unexpected schema changes.

What do you think?

Do you use a similar/different method for checking for unexpected schema changes?

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?