Introduction
In Part 1 of this series I discussed the principles of test-driven database development (TD3) along with Try/Catch and SqlCmd functionality. In Part 2, I discussed Solutions as defined in Microsoft Integrated Development Environments (IDEs), deployment, and different types of tests. In Part 3, I demonstrated refactoring and automating existence tests. In Part 4, we looked at database deployment and deployment artifacts. In this article, I examine database versioning.
The WeatherData database is real – I built it to hold data collected by my weather station.
Open the Existing Solution
Our existing Database Solution is named WeatherData and it’s a Database Project we created in SQL Server Management Studio. Once open, click ViewàSolution Explorer to display the solution artifacts:
More Evidence
There are several other ways to store evidence of database deployments. We’ll look at three here:
- A table in the database;
- Extended properties;
- A script to detect the version.
Exhibit A: The Table
I like to create a table in the dbo schema of the database and name it something obvious like dbo.DBVersion. It usually looks something like this:
Use WeatherData
go
If Not Exists(Select name
From sys.tables
Where name= 'DBVersion')
begin
Print ' > Creating DBVersion table'
Create Table DBVersion
(
ID int Identity(1,1)Not Null Constraint PK_DBVersion Primary Key Clustered(ID)
,DeploymentDateTime datetime Constraint DF_DBVersion_DeploymentDateTime Default(GetDate())
,Version varchar(20)
,VersionDescription varchar(255)
,DeploymentStatus char(1)
)
Print ' > DBVersion table created'
end
Else
Print ' > DBVersion table exists.'
go
If Exists(Select name
From sys.procedures
Where name = 'AddNewVersion')
begin
Print ' > Dropping AddNewVersion stored procedure'
Drop Procedure AddNewVersion
end
Print ' > Creating AddNewVersion stored procedure'
go
Create Procedure AddNewVersion
@Version varchar(20)
,@VersionDescription varchar(255)= Null
,@DeploymentStatus char(1)= 'R'
As
begin
Insert Into DBVersion
(Version
,VersionDescription
,DeploymentStatus)
Values
(@Version
,@VersionDescription
,@DeploymentStatus)
end
go
Print ' > AddNewVersion stored procedure created'
go
If Exists(Select name
From sys.procedures
Where name = 'UpdateLastVersion')
begin
Print ' > Dropping UpdateLastVersion stored procedure'
Drop Procedure UpdateLastVersion
end
Print ' > Creating UpdateLastVersion stored procedure'
go
Create Procedure UpdateLastVersion
@DeploymentStatus char(1)= 'S'
As
begin
declare @LastVersionID int
set @LastVersionID = (SelectMax(ID)
From DBVersion)
Update DBVersion
Set DeploymentStatus = @DeploymentStatus
Where ID = @LastVersionID
end
go
Print ' > UpdateLastVersion stored procedure created'
go
The easiest way to interact with this script is to create a couple stored procedures to interact with the table.
I added this script to the WeatherData project as Create_DBVersion.table.sql. Next, let’s modify the Deploy_V1.1 script, adding a call to this new Create script near the top:
In Deploy_V1, however, there is no database until the Create_WeatherData.db.sql script is executed (that creates the database), so the Create_DBVersion script has to run after there’s a database. There’s a little more to it than that, and this is a good time to talk about it.
“:r” is a command in SqlCmd. SqlCmd commands are executed immediately. So you read this and think “Cool, all will be fine so long as I modify the script as shown here:”
And you’re almost right.
While commands are executed immediately (and they are – I promise), this particular command is the Parse command. Parse accepts a filename as an argument, and adds the contents of that file to the statement cache. The statement cache is executed when the script hits a batch terminator, and “Go” is a batch terminator.
If we execute the script as shown above, it will fail. To make it succeed, we need to terminate the batch after the database is created but before the Create_DBVersion.table.sql script:
This script will now execute successfully.
One other thing: By tying this snippet to the deployment script I am intentionally coupling a database version (and its label) to an instance of script execution. That may not sound very important, but it can be if you’re attempting to trace why one instance of your database is performing differently from the others; or if you’re in an environment that requires intense auditing.
Next, add a call to AddNewVersion to each deployment script. For Deploy_V1.ver.sql, I’ll add the following statements just after the call to the Create_DBVersion.table.sql script:
Declare @Version varchar(20) Set @Version = '1.0'
Exec AddNewVersion @Version
For Deploy_V1.1.ver.sql, I modify the Set statement, setting the value of @Version to '1.1'.
At the bottom of the Deploy_V1.ver.sql and Deploy_V1.1.ver.sql scripts – just before the last of the Print statements – I add the following call:
Exec WeatherData.dbo.UpdateLastVersion
To test this new functionality, execute the script MasterDeploy_V1.ver.sql. Then execute the following script to view the results:
select * from WeatherData.dbo.DBVersion
My results look like this:
Exhibit B: Extended Properties
Extended properties rock. They allow you to annotate database objects in any number of ways. I’ve seen bunches of cool uses for extended properties, including a great presentation by Kevin Hazzard to the Richmond SQL Server Users Group a few months ago.
Extended properties are extremely flexible. To add an extended property, we use the sp_addextendedproperty system stored procedure. To update the values in an extended property, we use the sp_updateextendedproperty system stored procedure and we use sp_dropextendedproperty to delete the extended property. You can list extended properties and their values by querying fn_listextendedproperty.
Extended properties include a three-level hierarchy described as levels. According to Books Online (http://msdn.microsoft.com/en-us/library/ms190243.aspx), valid values for Level 0 “are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, and NULL.” Valid values for Level 1 “are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.” Valid values for Level 2 “are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.”
You can learn more about extended properties in Books Online. To apply an extended property to the database, simply pass in Nulls for the as shown here:
exec sp_addextendedproperty'Version', '1.0'
To view the values of extended properties on the database, query the function:
select *
from fn_listextendedproperty(Default
,Default
,Default
,Default
,Default
,Default
,Default)
The results look like this:
You can update the value of an extended property:
exec sp_updateextendedproperty 'Version','1.1'
You can remove an extended property:
exec sp_dropextendedproperty 'Version'
Open the script Deploy_V1.ver.sql and modify the script to read:
/*
Notes:
1. Turn on SQLCMD mode.
2. If you receive an error like "Incorrect syntax near ':'",
see Note 1.
3. Correct the file paths, replacing the default
with the full path to your files.
*/ Print '------------------------------------------------'
Print ' Deployment Log: WeatherData'
Print ' Script: Deploy_V1.ver.sql (V1)'
Print ' Version: 1.0'
Print ' Start DateTime: '+ convert(varchar,GetDate(), 101) + ' '+ convert(varchar,GetDate(), 108)
Print ' Server: '+ @@ServerName
Print ' Executed By: ' + Original_Login()
Print '------------------------------------------------'
Print ''
Print ' [V1] Deploying WeatherData v1.'
Print ''
Print ' [V1] Calling Create_WeatherData.test.sql...'
:r $(ScriptPath)Create_WeatherData.test.sql
Print ' [V1] Create_WeatherData.test.sql called.'
Print ''
Print ' [V1] Calling Create_WeatherData.db.sql...'
:r $(ScriptPath)Create_WeatherData.db.sql
Print ' [V1] Create_WeatherData.db.sql called.'
go
Print ''
Print ' [V1] Calling Create_DBVersion.table.sql...'
:r $(ScriptPath)Create_DBVersion.table.sql
Print ' [V1] Create_DBVersion.table.sql called.'
Declare @Version varchar(20)
Declare@VersionDescription varchar(255)
Set @Version = '1.0'
Set@VersionDescription = 'Deployed WeatherData database'
ExecAddNewVersion @Version, @VersionDescription
Print ' > Adding extended property'
Exec sp_addextendedproperty'Version', '1.0'
Print ''
Print ' [V1] Calling Create_WeatherData.test.sql...'
:r $(ScriptPath)Create_WeatherData.test.sql
Print ' [V1] Create_WeatherData.test.sql called.'
ExecWeatherData.dbo.UpdateLastVersion
Print ''
Print '------------------------------------------------'
Print ' [V1] WeatherData v1 deployed.'
Print '------------------------------------------------'
As you can see, extended properties allow us to label (or “stripe”) the database with version information. We can also label every object in the database. The database is the only object we deploy in version 1.0. In the deployment script for version 1.1, we deploy the Raw schema, StageTemperature table, and GetLatestAvgTemp stored procedure.
Open the Create_Raw.schema.sql and modify the script to read:
Use WeatherData
go
If Not Exists(Select name
From sys.schemas
Where name= 'raw')
begin
Print ' > Creating Raw schema.'
Declare @Sql varchar(50)
Set @Sql = 'Create Schema raw'
Exec(@Sql)
exec sp_addextendedproperty 'Version','1.1', 'SCHEMA','Raw'
end
Else
Print ' > Raw schema exists.'
Make similar modifications to Create_StageTemperature.table.sql and Create_GetLatestAvgTemp.proc.sql; adding the following lines of code, respectively:
exec sp_addextendedproperty 'Version','1.1', 'SCHEMA','Raw', 'TABLE','StageTemperature' execsp_addextendedproperty'Version', '1.1','SCHEMA', 'Raw','PROCEDURE', 'GetLatestAvgTemp'
Let’s test it! Drop the database and then re-execute MasterDeploy_V1.ver.sql. To query the values, execute the following queries:
select *
from fn_listextendedproperty(Default
,'SCHEMA'
,'raw'
,Default
,Default
,Default
,Default)
select *
from fn_listextendedproperty(Default
,'SCHEMA'
,'raw'
,'TABLE'
,'StageTemperature'
,Default
,Default)
select *
from fn_listextendedproperty(Default
,'SCHEMA'
,'raw'
,'PROCEDURE'
,'GetLatestAvgTemp'
,Default
,Default)
Exhibit C: A Script
One last method we’ll examine is using a script to detect the version. This is the least invasive method – you simply check the database to see if the objects from the latest change exist in the database. Depending on the change type, the detection scripts can get interesting. Also, as the total number of changes increases over time, you may need to modify portions of the script to detect earlier versions. In the worst-case, it may simply be impossible to detect certain changes (for instance, when all changes in a particular revision have been backed out of a future version).
Here’s a script to detect our current database version:
Print '------------------------------------------------'
Print ' Version Test: WeatherData'
Print ' Script: VersionTests.sql'
Print ' Version: 1.0, 1.1'
Print 'Start DateTime: '+ convert(varchar,GetDate(), 101) + ' '+ convert(varchar,GetDate(), 108)
Print ' Server: '+ @@ServerName
Print ' Executed By: ' + Original_Login()
Print '------------------------------------------------'
Print ''
Use WeatherData
go
If Exists(Select s.name +'.' + t.name
From sys.tables t
Inner Join sys.schemas s On s.Schema_Id= t.Schema_Id
Where s.name = 'raw'
And t.name = 'StageTemperature')
Print ' > Version 1.1 changes detected [V1.1]'
Use master
go
If Exists(Select name
From sys.databases
Where name ='WeatherData')
Print' > Version 1.0 changes detected [V1]'
Conclusion
In this article, we’ve looked at three ways to create, collect, and maintain database version information. I use all three in practice. My first choice is to use a DBVersion table, my second choice is a script to detect changes. Most of the time I use these together. Although they work well, I use extended properties the least. I have no real reason for this, it’s just a preference.
:{> Andy
The Series
The articles in this series:
