|How To > MS SQL Schema Versioning|
How to Maintain SQL Server Database Schema Version
While developing a software we typically have no special requirements to meet regarding versioning. Even when multiple programmers work on the same software, they are often not required to keep strict record of intermediate software versions. We are happy with any software that executes correctly on the development environment. But once the time comes to release the package, we need to mark it with version number, because that is the point in time when suddenly two packages begin to exist in parallel - one is the development version which keeps evolving in-house; the other one is released version which begins its life at customer's environment.
There is a long history of versioning of files that are deployed to the customer. Executable files and extension files (DLLs) normally contain version information. It is available when looking at file's properties in Windows system. Picture below shows properties of CALC.EXE which ships with Windows operating system. This particular instance has file version 6.1.7600.16385, which happens to be the same as product version (difference in interpreting the two is beyond scope of this text).
Other resources, like XML files or databases, must also be versioned. But in this case, it is not content of the resource that is tagged with version number, but rather its structure - XML schema and database schema. File or database content changes as time progresses, but schema is remains constant - all until new version of the application is deployed, the one that relies on a different data schema and requires underlying schema to be changed as part of the software updating process. In terms of XML or similar files, software update might require complete XML file to be rebuilt in order to meet new schema. In database terms, this might require adding new tables or altering existing ones. In either case, we need a way to determine current schema so that we can decide which operations are appropriate to make current version evolve into target version of the package being installed.
One more thing must be kept on mind. We are typically not informed in advance which is the version of client's system. One client might have been so good as to install every intermediate patch that was released after, say, version 2.0 of the software was release. His current version might be something like 18.104.22.1687. Some other client was supposedly not interested in patching anything, and he might be living along with original version 22.214.171.1242 for many months. But now suppose that a brand new version 126.96.36.199 was released and they both want to have it. There is a very simple scheme that makes this possible: we make installation package 188.8.131.52 such that it is applicable to any installation from generation 2 - 184.108.40.2062, 2.1.x.x or 220.127.116.117 regardless. It is installation process that decides what particular actions to make in order to transform system from actual version into target version 18.104.22.168.
This specific installation scheme requires that we keep track of all intermediate versions. The idea is then to upgrade each intermediate version into the next one. Namely, version 22.214.171.1242 is transformed, possibly through many steps, into something like 2.1.x.x, then into 2.2.x.x and so on until, by pure coincidence, version 126.96.36.1997 is reached. At the same time, the system that already was in this version would simply skip all the intermediate steps. Finally, both client systems can now be treated the same, as they have been brought to the same base version. This version is then further transformed, e.g. through 2.5.x.x, 2.6.x.x and so on, until all patches have been applied and ultimate target 188.8.131.52 is reached.
The only open question remaining is how to keep track of database schema version. In the remaining text, we will first outline the solution. We will then provide reusable implementation in T-SQL. Finally, a simple demonstration will be provided.
SQL Server Database Versioning Design
Solution is based on a single table which tracks schema version changes. A couple of stored procedures are provided to back up required scenarios. The following picture shows table definition for schema version tracking. Each schema version is associated with time interval during which it is valid. Only one version can have ValidTo field set to NULL, and that is the current version.
And here is the typical content of this table:
Additional procedures required to operate with this table are:
How to Use
These procedures are then used in the following way. Suppose that there is an installation step which can be applied to the database, but only if its schema version is one particular value. Database alter calls versioning.AssertSchemaVersion stored procedure to ensure that installation is applicable to the database. Should this procedure fail, that would be the indication that previous installation steps were not applied to the database and hence current installation cannot proceed. Otherwise, value 0 returned from the procedure indicates that this is the perfect moment to continue with installation, not forgetting to call versioning.ChangeSchemaVersion on successful end. Finally, if non-zero value is obtained from the procedure, we conclude that this installation has already been applied to the database and do nothing further.
For example, suppose that there is a database alter that is applicable to database schema version 184.108.40.206. Once complete, we say that database schema will be 220.127.116.11. Correct order of steps is to call versioning.AssertSchemaVersion('18.104.22.168'). Once it returns 0 result, database alter proceeds with changes to database schema and finally calls versioning.ChangeDatabaseSchema('22.214.171.124', '126.96.36.199') to commit to new database schema. Any further database alter, which is applicable to schema 188.8.131.52 would now have its prerequisites filled and could be started as soon as previous alter completes. On the bitter end, suppose that current schema version is 184.108.40.206 - this is way too old database schema and current alter cannot proceed before previous alters are all applied. This is ensured by versioning.AssertSchemaVersion procedure which would generate fatal error as soon as it finds out that current schema version 220.127.116.11 is smaller than expected version 18.104.22.168. With this addition, we have tied all loose ends.
Conventions and Guidelines
Installing solution described above to an existing database results in creation of versioning schema which will be home to versioning.SchemaVersion table and accompanying procedures. In order to use its full potential, we need to stick with some conventions. That will ensure that database is always in a good shape.
Database alters should be prepared for failures and misfortunes that often strike them when applied to the database. Each alter may consist of number of steps and some of them may simply fail, possibly due to an error in the alter itself. Alters must be coded in such way that they are repeatable - multiple runs must not cause harm to the database. But since some steps are hard to repeat multiple times (e.g. adding a table column), alter must have an transparent test which would tell whether one step should be executed now, or it was already completed in the past. Simple solution is to assign each step a version number - if that version is already reached in the database, then step must have been executed in the past; otherwise, it has not been executed and should be performed now, followed by version update.
This analysis naturally leads to a notion of incomplete alter executions. Some version numbers indicate partially executed database alters. When system remains in such state, it should not be used, but rather installation should be repeated to rectify the problem (possibly receiving a helpful hand from a human expert). One simple solution is to assign odd numbers in versions to intermediate states, and even numbers to final versions, those that are result of successful installations.
After all, we can formulate these guidelines for those who code database alters:
The following T-SQL code is the complete script required to add versioning schema to any SQL Server database. After this script is executed, database will be equipped with versioning solution and initialized to version 22.214.171.124. Further alters should be written to use versioning procedures and to maintain schema version upon completion.
-- Create script for database schema versioning -- Version: 1.0 -- Released: February 24, 2013 -- Author: Zoran Horvat IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='versioning') EXEC sp_executesql N'CREATE SCHEMA [versioning]' GO IF OBJECT_ID('versioning.SchemaVersion') IS NULL CREATE TABLE [versioning].[SchemaVersion] CREATE TABLE [versioning].[SchemaVersion] ( [SchemaVersionID] INT PRIMARY KEY IDENTITY, [ValidFrom] DATETIME NOT NULL, [ValidTo] DATETIME NULL, [Major] INT NOT NULL, [Minor] INT NOT NULL, [Build] INT NOT NULL, [Revision] INT NOT NULL ) GO IF NOT EXISTS (SELECT TOP 1 * FROM [versioning].[SchemaVersion]) INSERT INTO [versioning].[SchemaVersion]([ValidFrom], [ValidTo], [Major], [Minor], [Build], [Revision]) VALUES (GETDATE(), NULL, 1, 0, 0, 0) IF OBJECT_ID('[versioning].[GetSchemaVersion]') IS NULL EXEC sp_executesql N'-- Reads current database schema version. -- Parameters: -- @version - on output contains current schema version CREATE PROCEDURE [versioning].[GetSchemaVersion] @version VARCHAR(50) OUT AS BEGIN DECLARE @major INT DECLARE @minor INT DECLARE @build INT DECLARE @revision INT SET @version='''' SELECT @major=Major, @minor=Minor, @revision=Revision, @build=Build FROM [versioning].[SchemaVersion] WHERE [ValidTo] IS NULL IF @major IS NOT NULL SET @version = CAST(@major AS VARCHAR(50)) + ''.'' + CAST(@minor AS VARCHAR(50)) + ''.'' + CAST(@build AS VARCHAR(50)) + ''.'' + CAST(@revision AS VARCHAR(50)) END' GO IF OBJECT_ID('[versioning].[GetVersionParts]') IS NULL EXEC sp_executesql N'-- Breaks version into four integers, representing major version number, minor version number, -- revision number and build number. -- Parameters: -- @version - version specified as dot-separated series of segments (e.g. 126.96.36.199) -- @major - on output contains major version number extracted from @version -- @minor - on output contains minor version number extracted from @version -- @build - on output contains build number extracted from @version -- @revision - on output contains revision number extracted from @version CREATE PROCEDURE [versioning].[GetVersionParts] @version VARCHAR(50), @major INT OUT, @minor INT OUT, @build INT OUT, @revision INT OUT AS BEGIN DECLARE @startPos INT = 1 DECLARE @endPos INT SET @endPos = CHARINDEX(''.'', @version, @startPos) SET @major = CAST(SUBSTRING(@version, @startPos, @endPos - @startPos) AS INT) SET @startPos = @endPos + 1 SET @endPos = CHARINDEX(''.'', @version, @startPos) SET @minor = CAST(SUBSTRING(@version, @startPos, @endPos - @startPos) AS INT) SET @startPos = @endPos + 1 SET @endPos = CHARINDEX(''.'', @version, @startPos) SET @build = CAST(SUBSTRING(@version, @startPos, @endPos - @startPos) AS INT) SET @startPos = @endPos + 1 SET @endPos = LEN(@version) + 1 SET @revision = CAST(SUBSTRING(@version, @startPos, @endPos - @startPos) AS INT) END' GO IF OBJECT_ID('[versioning].[ChangeSchemaVersion]') IS NULL EXEC sp_executesql N'-- Changes schema version from @prevVersion to @nextVersion and returns status -- indicating whether version was updated successfully or not -- Parameters: -- @prevVersion - version number which should be changes -- @nextVersion - new version number, which should be set after procedure completes -- Return value: -- 0 - version was successfully changed -- 1 - @prevVersion did not match current schema version; no changes were made to the system -- 2 - @prevVersion is larger or equal to @nextVersion CREATE PROCEDURE [versioning].[ChangeSchemaVersion] @prevVersion VARCHAR(50), @nextVersion VARCHAR(50) AS BEGIN DECLARE @res INT = 0 DECLARE @major INT DECLARE @minor INT DECLARE @build INT DECLARE @revision INT DECLARE @prevMajor INT DECLARE @prevMinor INT DECLARE @prevBuild INT DECLARE @prevRevision INT DECLARE @curVersion VARCHAR(50) EXEC [versioning].[GetVersionParts] @nextVersion, @major OUT, @minor OUT, @build OUT, @revision OUT EXEC [versioning].[GetSchemaVersion] @curVersion OUT IF @curVersion <> @prevVersion SET @res = 1 ELSE BEGIN EXEC [versioning].[GetVersionParts] @prevVersion, @prevMajor OUT, @prevMinor OUT, @prevBuild OUT, @prevRevision OUT IF @prevMajor > @major OR (@prevMajor = @major AND @prevMinor > @minor) OR (@prevMajor = @major AND @prevMinor = @minor AND @prevBuild > @build) OR (@prevMajor = @major AND @prevMinor = @minor AND @prevBuild = @build AND @prevRevision > @revision) OR (@prevMajor = @major AND @prevMinor = @minor AND @prevBuild = @build AND @prevRevision = @revision) SET @res = 2 END IF @res = 0 BEGIN UPDATE [versioning].[SchemaVersion] SET [ValidTo] = GETDATE() WHERE [ValidTo] IS NULL INSERT INTO [versioning].[SchemaVersion]([ValidFrom], [ValidTo], [Major], [Minor], [Build], [Revision]) VALUES (GETDATE(), NULL, @major, @minor, @build, @revision) END RETURN @res END' GO IF OBJECT_ID('[versioning].[CompareSchemaVersions]') IS NULL EXEC sp_executesql N'-- Compares two schema versions and returns an integer number indicating the comparison result. -- Parameters: -- @ver1 - first version to compare -- @ver2 - second version to compare -- Result: -- -1 - @ver1 is smaller than @ver2 -- 0 - @ver1 and @ver2 are equal -- +1 - @ver1 is larger than @ver2 CREATE PROCEDURE [versioning].[CompareSchemaVersions] @ver1 VARCHAR(50), @ver2 VARCHAR(50) AS BEGIN DECLARE @major1 INT DECLARE @minor1 INT DECLARE @build1 INT DECLARE @revision1 INT DECLARE @major2 INT DECLARE @minor2 INT DECLARE @build2 INT DECLARE @revision2 INT DECLARE @res INT EXEC [versioning].[GetVersionParts] @ver1, @major1 OUT, @minor1 OUT, @build1 OUT, @revision1 OUT EXEC [versioning].[GetVersionParts] @ver2, @major2 OUT, @minor2 OUT, @build2 OUT, @revision2 OUT SET @res = 0 IF @major1 < @major2 SET @res = -1 ELSE IF @major1 > @major2 SET @res = 1 ELSE IF @minor1 < @minor2 SET @res = -1 ELSE IF @minor1 > @minor2 SET @res = 1 ELSE IF @build1 < @build2 SET @res = -1 ELSE IF @build1 > @build2 SET @res = 1 ELSE IF @revision1 < @revision2 SET @res = -1 ELSE IF @revision1 > @revision2 SET @res = 1 RETURN @res END' GO IF OBJECT_ID('[versioning].[CompareWithCurrentSchemaVersion]') IS NULL EXEC sp_executesql N'-- Compares specified schema version with current schema version and returns -- an integer number indicating the comparison result. -- Parameters: -- @ver - version which should be compared with current schema version. -- Result: -- -1 - @ver is smaller than current schema version. -- 0 - @ver is equal to current schema version. -- +1 - @ver is larger than current schema version. CREATE PROCEDURE [versioning].[CompareWithCurrentSchemaVersion] @ver VARCHAR(50) AS BEGIN DECLARE @cur VARCHAR(50) DECLARE @res INT EXEC [versioning].[GetSchemaVersion] @cur OUT EXEC @res = [versioning].[CompareSchemaVersions] @ver, @cur RETURN @res END' GO IF OBJECT_ID('[versioning].[AssertSchemaVersion]') IS NULL EXEC sp_executesql N'-- Compares specified schema version with current schema version and if -- current schema version is smaller, terminates execution with fatal error. -- Otherwise continues and returns a number indicating comparison between -- @ver and current schema version. -- Parameters: -- @ver - version which must be asserted. Procedure fails if current schema -- version is smaller than this version. -- Result: -- 0 - current schema version is equal to @ver CREATE PROCEDURE [versioning].[AssertSchemaVersion] @ver VARCHAR(50) AS BEGIN DECLARE @cmp INT DECLARE @msg VARCHAR(200) DECLARE @curVersion VARCHAR(50) EXEC @cmp = [versioning].[CompareWithCurrentSchemaVersion] @ver IF @cmp > 0 BEGIN EXEC [versioning].[GetSchemaVersion] @curVersion OUT SET @msg = ''Error executing on schema version '' + @curVersion + '' when expecting '' + @ver + ''.'' RAISERROR(@msg, 20, 0) WITH LOG END RETURN @cmp END' GO
After versioning create script has been executed, database has schema version 188.8.131.52. Into such fresh database we wish to install a new schema which would contain data about customers, all of them tracked through a single table named Person. Here is the database alter which installs customers schema and Person table in the database.
Here is the complete alter:
-- ALTER 01 -- Applies to version: 184.108.40.206 -- Produces version: 220.127.116.11 DECLARE @cmp INT EXEC @cmp = [versioning].[AssertSchemaVersion] '18.104.22.168' IF @cmp = 0 EXEC [versioning].[ChangeSchemaVersion] '22.214.171.124', '126.96.36.199' GO DECLARE @cmp INT EXEC @cmp = [versioning].[AssertSchemaVersion] '188.8.131.52' IF @cmp = 0 BEGIN IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='customers') EXEC sp_executesql N'CREATE SCHEMA [customers]' EXEC [versioning].[ChangeSchemaVersion] '184.108.40.206', '220.127.116.11' END GO DECLARE @cmp INT EXEC @cmp = [versioning].[AssertSchemaVersion] '18.104.22.168' IF @cmp = 0 BEGIN IF OBJECT_ID('[customers].[Person]') IS NULL CREATE TABLE [customers].[Person] ( [PersonID] INT NOT NULL IDENTITY PRIMARY KEY, [Name] NVARCHAR(50) NOT NULL, [Address] NVARCHAR(500) NOT NULL ) EXEC [versioning].[ChangeSchemaVersion] '22.214.171.124', '126.96.36.199' END GO
And here is the SchemaVersion table content after the script has been executed:
According to conventions given earlier, we are assigning odd build numbers to intermediate versions, i.e. versions reached while alter execution is in progress. Should we ever encounter a database with odd build number, we would know that some installation did not complete.
Now suppose that at some point in the future we decide to make changes to the Person table. Say, the site owner has found that some names of rather aristocratic origin could not fit in common 50 characters field width. Since lengthy names often came with higher revenues, we have been kindly asked to increase length of the Name field. On the other hand, long addresses were regarded as an unwanted storage burden: we need to reduce Address field length to a moderate 200 characters and trim any existing data that might breach that boundary. In addition, year of birth should be assigned to each person (nullable, off course, since existing persons will not have an associated information to fill in). Below is the complete alter script.
-- ALTER 02 -- Applies to version: 188.8.131.52 -- Produces version: 184.108.40.206 DECLARE @cmp INT EXEC @cmp = [versioning].[AssertSchemaVersion] '220.127.116.11' IF @cmp = 0 EXEC [versioning].[ChangeSchemaVersion] '18.104.22.168', '22.214.171.124' GO DECLARE @cmp INT EXEC @cmp = [versioning].[AssertSchemaVersion] '126.96.36.199' IF @cmp = 0 BEGIN ALTER TABLE [customers].[Person] ALTER COLUMN [Name] NVARCHAR(100) NOT NULL EXEC [versioning].[ChangeSchemaVersion] '188.8.131.52', '184.108.40.206' END GO DECLARE @cmp INT EXEC @cmp = [versioning].[AssertSchemaVersion] '220.127.116.11' IF @cmp = 0 BEGIN UPDATE [customers].[Person] SET [Address] = LEFT([Address], 200) WHERE LEN([Address]) > 200 ALTER TABLE [customers].[Person] ALTER COLUMN [Address] NVARCHAR(200) NOT NULL EXEC [versioning].[ChangeSchemaVersion] '18.104.22.168', '22.214.171.124' END GO DECLARE @cmp INT EXEC @cmp = [versioning].[AssertSchemaVersion] '126.96.36.199' IF @cmp = 0 BEGIN ALTER TABLE [customers].[Person] ADD [YearOfBirth] INT NULL EXEC [versioning].[ChangeSchemaVersion] '188.8.131.52', '184.108.40.206' END GO
After executing this script, here is the contents of the versioning.SchemaVersion table:
In this article we have demonstrated that keeping clear track of database schema versions is not complicated. It is rather a matter of consistency. With only a moderate amount of work we can build a sequence of alters that offer high level of confidence with none the less flexibility offered by plain, unconstrained T-SQL programming. Keeping track of schema changes pays off quickly. Hence, instead of a conclusion, we can only give a piece of advice, and that is to use a formal schema versioning method on each database instance.
Published: Feb 24, 2013; Modified: Mar 23, 2013