Implementing Database Versioning

Introduction

When developing and supporting enterprise wide applications, it's very common to see different stages of the application being run as different versions. Or even different clients may be running different versions of the application in the production environment. This article provides a template for enforcing database versioning in such a scenario.

Implementing DB Versioning

In a multiple user environment it's very common to see that some objects are created or deleted in the database for some testing and data fixing issues (like views, constraints, triggers or indexes). By using the script below, all the objects in the database are dropped except for the table and then created again. So the developers have to add the necessary objects in the script at one place and thus it ensures that when the script is run, the objects in the database confirm to what they are supposed to be.

This script doesn't drop the table as the data in the table will be critical to the application. Instead the script checks all the tables as expected by the application are existing and also the columns defined in them are in proper order. Also we can add a table in the database called DBVersion with appropriate fields and check the version number before running the script. For example: if the application is upgraded from release 1 to 2, before running it, check if the current value in the version field is 1, and after running the script, update that to 2. (This can be extended even to minor releases like 1.1.01 etc..) This will ensure that the script runs only on the database with the appropriate version.

Template Code for enforcing DB Versioning

BEGIN Transaction
--
-- Insert Change script here
--
PRINT 'Drop Constraints...'
GO


DECLARE @SQL nvarchar(4000)
DECLARE DropCursor CURSOR FOR
 SELECT 'ALETR TABLE [' + so1.name + '] 
        DROP CONSTRAINT [' + so2.name + ']'
 FROM sysconstraints sc   
 JOIN sysobjects so1 ON sc.ID = so1.ID   
 JOIN sysobjects so2 ON sc.constid = so2.ID   
 WHERE so2.xtype IN ('PK', 'F', 'D', 'UQ', 'C') 
 AND ObjectProperty(so2.ID, 'IsMSShipped') = 0    
 ORDER BY so2.xtype
OPEN DropCursor
FETCH NEXT FROM DropCursor INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
 Execute sp_ExecuteSQL @SQL
 FETCH NEXT FROM DropCursor INTO @SQL
END
CLOSE DropCursor
Deallocate DropCursor
GO
PRINT 'Drop Indexes & Statistics...'
GO
DECLARE @SQL nvarchar(4000)
DECLARE DropCursor Cursor For
 SELECT CASE
   WHEN IndexProperty(so.id, si.name, 
            'IsStatistics') = 1 THEN 
    'Drop Statistics ['
   ELSE  'Drop Index ['    
  END + so.name + '].[' + si.name + ']'
 FROM sysindexes si
 JOIN sysobjects so On si.id = so.id
 WHERE si.indid Not In (0,255) 
 AND so.xtype = 'u' 
 AND ObjectProperty(so.id, 'IsMSShipped') = 0
OPEN DropCursor
FETCH NEXT FROM DropCursor Into @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
 EXECUTE sp_ExecuteSQL @SQL
 FETCH NEXT FROM DropCursor Into @SQL
END
CLOSE DropCursor
Deallocate DropCursor
GO
Print 'Drop Triggers...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Drop Trigger [' + so1.name + ']'
 From sysobjects so1
 Join sysobjects so2 On so1.parent_obj = so2.id
 Where so1.xtype = 'TR'
 And ObjectProperty(so2.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Calculated Fields...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Alter Table [' + so.name + '] 
        Drop Column [' + sc.name + ']'
 From syscolumns sc 
 Join sysobjects so On sc.id = so.id
 Where sc.IsComputed = 1
 And so.xtype = 'U'
 And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Procedures...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Drop Procedure [' + so.name + ']'
 From sysobjects so
 Where so.xtype = 'P'
 And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Views...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Drop View [' + so.name + ']'
 From sysobjects so
 Where so.xtype = 'V'
 And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Functions...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Drop Function [' + so.name + ']'
 From sysobjects so
 Where xtype in ('FN', 'IF', 'TF')
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Create Independent Functions...'
GO
Print 'Create Base Tables...'
GO
If Not dbo.TableExists('Table1')
--(Get from sysobjects table)
Create Table Table1 (
 [ID] int NOT NULL ,
) ON [PRIMARY]
GO
If Not dbo.ColumnExists('Table1', 'Column1')
--(Get from sysobjects/syscolumns table)
 Alter Table dbo.Table1 Add
  Column1 int NOT NULL
GO
Print 'Create Base Table Dependent Procedures...'
GO
Print 'Create Application Tables...'
GO
--Checking if the Column of specified data type is defined
If  (Select SysColumns.Type from Sysobjects  
 Join  Syscolumns 
   On Sysobjects.ID = Syscolumns.ID
 Where  Sysobjects.Name = 'Table1' And Syscolumns.Name = 'Column1') = '111'
 Begin
  Alter Table Table1 Drop Column Column1
  Alter Table Table1 Add Column1 varchar(50) Null
 End
GO
Print 'Create Functions That Depend On Tables...'
GO
Print 'Create Calculated Fields...'
GO
Alter Table Table1 Add
 Column1 AS dbo.Function1(param)
 
Print 'Create Constraints...'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
 IX_Table1 UNIQUE NONCLUSTERED 
 (
 Column1,
 Column2
 ) ON [PRIMARY]
GO
Alter Table dbo.Table1 ADD 
 CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED 
 (
  [ID]
 ) ON [PRIMARY]
GO
Print 'Create Foreign Keys...'
GO
Alter Table Table1 ADD 
 CONSTRAINT FK_Table1_Table2 FOREIGN KEY 
 (
  [ID]
 ) REFERENCES Table2 (
  [FKID]
 ) ON DELETE CASCADE  ON UPDATE CASCADE ,
 
Print 'Create Views...'
GO
Print 'Create Functions That Depend On Views...'
GO
Print 'Create Indexes...'
GO
CREATE INDEX [IX_Table1_Column1Column2] 
       ON [dbo].[Table1]([Column1], [Column2]) ON [PRIMARY]
GO
CREATE UNIQUE  INDEX [IX_Column1Column2] 
       ON [Table1]([Column1], [Column2]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Column1] ON 
       dbo.VENDOR_MASTER(Column1) ON [PRIMARY]
GO
Print 'Create Procedures...'
GO
Print 'Create Triggers...'
GO
Print 'Set Database version to current number'
GO
Print 'Data Updates...'
GO
--
-- Data Updates Go here...
--
GO
Commit
GO

The above script defines the sequence in which the objects in the database can be dropped and recreated. This template script needs to be maintained for the different releases of the application (with the appropriate changes). Even if there is not much change from one version to another it will be a good practice to maintain separate versioning for the template script as that will provide a better way of enforcing proper versioning of the database.