Versioning the Database

 

Versioning the Database

If you write apps that hit a database that you own, then you probably want version control on the database itself. I always script out the initial database, and version that script. I also script the changes, and version those, and then script the entire DB again and version that as well. That allows me to upgrade a DB schema or build a new one from scratch.

But that doesn't do anything once the DB is deployed - how DO you know what version the database is in once it is in the field? I've used two approaches, and I'd love to hear the ones you're using in your shops.

The first method is to create a "version" table in the database and record the number there. I like this approach because I can see the "trail" of how many times (and when) the database has been versioned, and even who did it:

   1: USE AdventureWorks;

   2: GO

   3:  

   4: /* First Method - create table */

   5: CREATE TABLE [dbo].[DBA_Version]

   6: (    [DBA_VersionPK] [int] IDENTITY(1,1) NOT NULL,

   7:     [Version] [varchar](50) NOT NULL,

   8:     [DateAssigned] [datetime] NULL,

   9:     [AssignedBy] [varchar](50) NULL,

  10:     [Notes] [varchar](255) NULL,

  11:  CONSTRAINT [PK_DBA_Version] PRIMARY KEY CLUSTERED 

  12: ([DBA_VersionPK] ASC)

  13: WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) 

  14: ON [PRIMARY]

  15: GO

  16: SET ANSI_PADDING ON

  17: GO

  18: ALTER TABLE [dbo].[DBA_Version] ADD  CONSTRAINT [DF_DBA_Version_DateAssigned]  DEFAULT (getdate()) FOR [DateAssigned]

  19: GO

  20: ALTER TABLE [dbo].[DBA_Version] ADD  CONSTRAINT [DF_DBA_Version_AssignedBy]  DEFAULT (user_name()) FOR [AssignedBy]

  21: GO

  22:  

  23: /* Insert some data */

  24: INSERT INTO [AdventureWorks].[dbo].[DBA_Version]

  25:            ([Version]

  26:            ,[Notes])

  27:      VALUES

  28:            ('1.0.1.3'

  29:            ,'Changed major schema' )

  30: GO

  31:  

  32: /* Read it */

  33: SELECT * 

  34: FROM [AdventureWorks].[dbo].[DBA_Version]

  35: ORDER BY DateAssigned DESC;

  36: GO

There's another way, if you want to just track the latest number. For this you can use an "extended property". You could even use this for other objects in the database, such as stored procs and so forth, but I just use it for the database itself:

   1: USE AdventureWorks;

   2: GO

   3:  

   4: /* Method 2 - Add an extended property */

   5: EXEC sys.sp_addextendedproperty 

   6: @name = N'version', 

   7: @value = N'1.0.1.3';

   8: GO

   9:  

  10: /* To view an extended property */

  11: SELECT name, value

  12: FROM fn_listextendedproperty(default, default, default, default, default, default, default)

  13: WHERE name = 'version';

  14: GO

  15: -- or

  16: SELECT name, value 

  17: FROM sys.extended_properties 

  18: WHERE class_desc = 'DATABASE'

  19: AND

  20: name = 'version';

  21: GO

  22:  

  23:  

  24: /* Update an extended property */

  25: EXEC sp_updateextendedproperty

  26: @name = N'version', 

  27: @value = N'1.0.1.4';

  28: GO

  29:  

  30: /* Delete an extended property */

  31: EXEC sp_dropextendedproperty

  32: @name = N'version'; 

  33: GO

Of course, both of these have issues - the user can just change things underneath you and you would think the database is at one level when it isn't. Also, the user might tinker with the version itself.

To handle that, you could use DDL triggers, or you could do a checksum on the table. Lately I've been playing around with using the Change Data Capture feature in SQL Server 2008 to track DDL changes. That, combined with these version numbers, gives me a good feel for the database version.

Published 27 January 09 09:13 by Buck Woody

Carpe Datum : Versioning the Database