Click here to monitor SSC
  • Av rating:
  • Total votes: 9
  • Total comments: 4
Phil Factor

Database Deployment: The Bits - Versioning

04 February 2013

Although databases have no inherent way of recording their version numbers, SQL Server provides the means of doing so, and much more besides. This is a great advantage to anyone faced with the task of deploying databases without errors.

Sometimes, when discussing deployment of databases you may talk about ‘versioning’  a database. Doing this versioning isn’t entirely straightforward, so it helps avoid having to go into the detail if you airily wave the hand at this point as if to signal that it is a trivial matter.  

There are a number of good reasons for having a version attached to a database.

  • It can prevent problems due to a version-mismatch. Where you have a simple one-to-one correspondence between an application and a database, then it helps the resilience of the application to be able to detect what version of the database it is working with. Where many applications are running against a database, it is better practice for each defined application-interface to report its own version number independently of the database because these don’t necessarily keep in step with the database.
  • It helps to monitor what’s out there. If you synchronise your development work with source control, it makes things easier to manage if the version number of the live database is kept  in sync with the version number  of the version in source control that was used to create it. You then know what is installed without having to compare it, or look it up in a separate record.
  • It helps with database deployment. When you are deploying a new version of a database to a number of machines, and you need to preserve the data that is on them, you can do it in several ways. The quickest way is to have your upgrade scripts and any data-migration scripts already prepared and tested for every permutation of upgrade between versions that you are likely to meet. To do this, you need to  know what versions are out there.  If you don’t have a record of the current version, then you have to sniff out each database with a SQL Comparison tool, and generate the upgrade scripts and data migration scripts.

There is no generally-accepted place to store a version number in a database schema. Databases don’t have version numbers by convention. There is no standard way of putting in this or any other information about an application into the database. I’ve seen many different methods for attaching version information to a database. Some shops adopt the practice of having a database of databases, with all the application interface in it. Others use special tables, often Entity-Attribute-Value (EAV) lashups. I dislike all methods except for the use of Extended Properties.

A Table has one clear advantage in that it is easy to access the information from SQL. However, this information does not get into an automatically-generated build script.  The data within a table like this isn’t  part of the database schema. The contents of a version table would need to be scripted separately.  With an extended property, on the other hand, the version information gets placed with the object when objects are automatically scripted out.  The information stays with the metadata. It seems better to attach the information directly to the object, and the mechanism to do that already exists whether one likes it or not.

By attaching a version directly to a database object, it solves other problems.  it is likely, for example, that each application interface will want a version number as well. Because it makes much more sense in implementing security if each application-interface is in its own schema, this boils down to being able to attach a version number to a schema. With so much going for the use of extended properties, it is baffling to see that Microsoft still haven't implemented Extended Properties in SQL Azure. If you are stuck with having to deploy to SQL Azure, then you will have to use a table to store the information and be stuck with the key disadvantage that the build and version information won't appear in your build scripts.

How to maintain a simple version number

What have we got to store? A version number is usually a string representation of a series of  three or four integers delimited by dots. These run from  left to right in diminishing importance, or maybe just a major and minor number with a code for the type of release (alpha, bets, Release Candidate (RC) and so on.

So as we don’t get to tied into fiddling with the complexities of Extended properties, we’ll  use William Brewer’s  excellent  sp_DbDoc.  (I use a version that is installed in Master as a system stored procedure so it can be used for database builds. It is available here)

If you want to attach a version ‘3.2.1’ to a database  this is achieved by …

Execute sp_DbDoc  'Database','version','3.2.1'

…or you can change it by ...

Execute sp_DbDoc  'Database','version','3.2.2'

Or even append each version number…

Execute sp_DbDoc  'Database','version','3.2.3 (removed incomprehensible CTEs)','append'

... and delete it

Execute sp_DbDoc  'Database','version','','Delete'

You can’ obviously, use the raw  procedures and functions but I wouldn’t go that route because you’ll find it so tedious that you’d stop using extended properties, which would be sad.

Adding a property to a database is easy using the stored procedures Microsoft provides, of course, because it is the default object for these system stored procedures

EXEC sp_addextendedproperty  @name = N'Version',  @value = '3.2.1';

But if it exists you get an error

Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37
Property cannot be added. Property 'Version' already exists for 'object specified'.

… so you have to use…

EXEC sp_updateextendedproperty  @name = N'Version', @value = '3.2.1';

…. or delete it first

execute sp_dropExtendedProperty = N'Version'

As sp_DbDoc lets you see the extended properties of whatever you want to see, as well, I’d stick with it. Otherwise, you can always use 

SELECT  name, value fn_listextendedproperty ('version', default, default, default, default, default, default);

Maintaining a version number for a schema

You might want to add versions to schemas. This may seem odd but it is a handy way of recording the version of an application interface, if you have a number of applications, each with their own interface based on views, functions and procedures, having separate interfaces. At any one time, your interfaces may be at different versions, depending on the needs of each application and their development releases. A sensible way of handling interfaces is to put each in their own schema. This makes it far easier to engineer the security of each  one. Because of this, the schema becomes the obvious place to lodge the versioning interface of each interface. 

Let’s just show this by creating a schema

Create schema Test

And adding an Extended property called ‘version’ with the value ‘2.5.7’

sp_DbDoc 'schema','test.version','2.5.7'

Then delete it,

sp_DbDoc 'schema','test.version','','delete'

... recreate it,

sp_DbDoc 'schema','test.version','2.5.7'

... and update it

sp_DbDoc 'schema','test.version','2.5.8','update'

 

Version numbers for tables or any other object you want

Having got this far, you might  want to use a similar device in order to attach version numbers to Tables, since the build scripts to tables are not held in the database and all table, columns and constraint comments are therefore lost if you auto-generate your build scripts.

Lets create a sample table

CREATE TABLE dbo.Employees(
     
EmployeeID int IDENTITY(1,1) NOT NULL primary key,
     
ManagerID int NULL,
     
PayGradeCode char(1) NOT NULL,
     
FirstName varchar(30) NOT NULL,
     
LastName varchar(30) NOT NULL)

Then we attach a version number to it

sp_DbDoc 'schema.table','dbo.Employees.version','1.2.5'

delete it just to show we can

sp_DbDoc 'schema.table','dbo.Employees.version','','delete'

add another one in

sp_DbDoc 'schema.table','dbo.Employees.version','1.2.6'

and change it

sp_DbDoc 'schema.table','dbo.Employees.version','2.5.8','update'

 

Storing more complex information about versions

I like to store a date for when a version change happened and who did it: and why they did it. I know that a lot of this information is in Source Control, but sometimes it is hard to find.  It makes sense to copy this information from source control  into the database object on deployment. It is convenient to have this as an extended property because it is easy to add to the build script, even when it is automatically generated, and so you can see who did what, and when, whilst perusing the scripts.  It is no use having all this stored in version control only when it is 3AM, and I’m struggling with a problem with a sick production server where a database deployment has Gang aft agley.

The answer is to put it in a structured document. You can, of course use XML if you like, but you’re much likely to read raw JSON or YAML without error.  I tend to choose JSON because it is possible to interpret with TSQL whereas YAML requires an external application.

By using PowerShell v3, it is easy now to convert to and from JSON.

@{Version="3.2.1";

   Date=get-date -format "d MMM yyyy";

   Reason="Fixed issue with linked database"} | ConvertTo-Json

Which gives

{

    "Date":  "25 Jan 2013",

    "Reason":  "Fixed issue with linked database",

    "Version":  "3.2.1"

}

..so you can read several variables out of one extended property, or even have hierarchical data. In your case, you might actually want to add a version to a list of versions the database has been upgraded to.

 {

    "Versions":  [

                     {

                         "Version":  "3.2.0",

                         "Date":  "1 Jan 2012",

                         "Reason":  "enabled all disabled constraints"

                     },

                     {

                         "Version":  "3.2.1",

                         "Date":  "12 Jan 2012",

                         "Reason":  "Fixed issue with linked database"

                     }

                 ]

}

By converting this from  JSON  in PowerShell, adding the revision, and then converting back to JSON, then the revision is added.

$version=@"

{

    "Versions":  [

                     {

                         "Version":  "3.2.0",

                         "Date":  "1 Jan 2012",

                         "Reason":  "enabled all disabled constraints"

                     },

                     {

                         "Version":  "3.2.1",

                         "Date":  "12 Jan 2012",

                         "Reason":  "Fixed issue with linked database"

                     }

                 ]

}

"@

$($version | ConvertFrom-JSON).versions += [PSCustomObject]@{Version = "3.2.2"; Date = '15 Jan 2012'; Reason="Added support for REST queries"} |

convertTo-JSON

... which gives ...

{

    "Versions":  [

                     {

                         "Version":  "3.2.0",

                         "Date":  "1 Jan 2012",

                         "Reason":  "enabled all disabled constraints"

                     },

                     {

                         "Version":  "3.2.1",

                         "Date":  "12 Jan 2012",

                         "Reason":  "Fixed issue with linked database"

                     },

                     {

                         "Version":  "3.2.2",

                         "Date":  "15 Jan 2012",

                         "Reason":  "Added support for REST queries"

                     }

                 ]

}

Beware that you are limited to a string length of 7500 so you should not go mad with structured documents.  If you are repeatedly building a list, you’d do well to remove the most aged items in the list if the resulting JSON is over the limit.  It is also a help that there is a –Compress option that takes out the  white space and indented formatting in the output string, in order to save space.

This is all well and good, but you are making things more difficult to access the information from SQL. It is rare for a SQL routine to need to know the current version but it happens.

If you are sensible enough to use my  parseJSON utility procedure, then it is simple to do. Once you know the format of your JSON version record and put it in place, you just execute this to get the information as a simple table. Here it is as a view, with the JSON Parsing routine already installed in master. (Watch that in a production system!)

Create view DataBaseVersion as

Select parent_ID,

       max(case when name='version' then StringValue else '' end) as version,

       convert(DateTime,max(case when name='date' then StringValue else '' end)) as Date,

       max(case when name='reason' then StringValue else '' end) as Reason

from master..sp_parseJSON(

      convert(nvarchar(4000),

               (SELECT value FROM fn_listextendedproperty

                 ('version', default, default, default, default, default, default)

             )

      )

  )

where ValueType = 'string'

group by parent_ID

go

if you now try it out on our sample database…

Select * from DatabaseVersion order by date

…you’ll get ….

Now you can get the current version number easily.

Select top 1 version from DatabaseVersion order by date desc

Conclusions

 The more complex a particular database deployment task gets, the more important it becomes to attach version numbers to databases, schemas  and other database objects.  At the same time, the developer must never be required to enter this information in more than one place.  This means understanding how the development process should be working and automating work such as this by scripting it.  There will be a number of places where a read-only copy of this information is kept, but only one updatable source, preferably in your source-control system.

Whatever system you are using to develop databases, it makes deployment and general admin  smoother  if you  know what versions an installation is using. By using extended properties, you can attach this sort of information firmly to the object so that it can be interrogated by scripts, viewed in the source code, inspected via TSQL or checked within the running application.

Phil Factor

Author profile:

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Google + To translate this article...

Search for other articles by Phil Factor

Rate this article:   Avg rating: from a total of 9 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Extended properties in Azure
Posted by: Anonymous (not signed in)
Posted on: Monday, February 18, 2013 at 1:59 AM
Message: SQL Azure just doesn't have extended properties, sadly.

Subject: Another way to include versions
Posted by: Gregmac (not signed in)
Posted on: Tuesday, February 19, 2013 at 8:26 AM
Message: I've been doing this for a long time using a table-valued function:

CREATE FUNCTION [dbo].[appVersion]() RETURNS TABLE AS RETURN (
SELECT N'3.2.1.12345' as [Version], 3 as [Major], 2 as [Minor], 1 as [Release], 12345 as [Build]
,N'Release' as [BuildConfiguration], CAST('2013-02-19 06:01:04' as datetime) as [BuildDate]
)

This is saved in my source control using 0's, and then overwritten using a build script with actual values prior to packaging the DB schema.

This works exceedingly well since it's actually part of the database schema. Tools such as SQL Compare pick this up no problem -- I'm not sure if that's true or not of the metadata.

You can include additional information, or even history of versions if you want (I do think this is a bit misleading if all of those versions have not actually been installed on this server though).

At startup time, your app can do SELECT version FROM appVersion() and if it doesn't match, show a warning or exit. I actually compare the first 3 numbers to ensure match, and check the last either matches or is 0 which allows development versions to work.

Subject: Another way to include versions
Posted by: Phil Factor (view profile)
Posted on: Friday, February 22, 2013 at 2:22 AM
Message: Yes, this or a similar view or stored procedure works fine and is perfectly OK for self-contained projects. I should have mentioned this.
My only worry is that changing the version number would then be considered a source code change (and it would appear as such in any auditing software), whereas an EP-based version would be just the change of a value.
I like to avoid code changes to database objects in order to insert a version into a database due to zealous Production or DevOps people objecting to a potentionally recursive change. I've been stung by this in a corporate setting. Another reason I don't generally recommend this approach is that I usually 'version' tables and schemas too when a team of developers is working on a database. That starts getting a bit ragged when one has to have extra routines to do this. The point I make in the article is that if you use an EP, the version is attached directly to the object being versioned. This is important for automating deployment because the version is easily visible in SMO as an attribute of the object.

Subject: I think this is a neat idea, but how does the info get there?
Posted by: nportelli (view profile)
Posted on: Monday, March 11, 2013 at 12:53 PM
Message: We use SQL Source control and as far as I know it doesn't allow for hooks. So there goes the only entering info in one place.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.