LinkedIn | FaceBook | Twitter
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;
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]
16: SET ANSI_PADDING ON
18: ALTER TABLE [dbo].[DBA_Version] ADD CONSTRAINT [DF_DBA_Version_DateAssigned] DEFAULT (getdate()) FOR [DateAssigned]
20: ALTER TABLE [dbo].[DBA_Version] ADD CONSTRAINT [DF_DBA_Version_AssignedBy] DEFAULT (user_name()) FOR [AssignedBy]
23: /* Insert some data */
24: INSERT INTO [AdventureWorks].[dbo].[DBA_Version]
29: ,'Changed major schema' )
32: /* Read it */
33: SELECT *
34: FROM [AdventureWorks].[dbo].[DBA_Version]
35: ORDER BY DateAssigned DESC;
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:
4: /* Method 2 - Add an extended property */
5: EXEC sys.sp_addextendedproperty
6: @name = N'version',
7: @value = N'126.96.36.199';
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';
15: -- or
16: SELECT name, value
17: FROM sys.extended_properties
18: WHERE class_desc = 'DATABASE'
20: name = 'version';
24: /* Update an extended property */
25: EXEC sp_updateextendedproperty
26: @name = N'version',
27: @value = N'188.8.131.52';
30: /* Delete an extended property */
31: EXEC sp_dropextendedproperty
32: @name = N'version';
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.
Funny you should bring this topic up on the same day that I recieved my plaque for versioning database objects using source control systems from back in 1999. Check out http://tinyurl.com/dlynbv
I like that you are attempting to solve an age-old IT conundrum in a pragmatic way. I see a couple of downsides to your approach though, and you mentioned a few in your final paragraph. There is really no true control since the user can change objects without checking in.
A new, innovative software product called DBVS solves all of these problems in an elegant way. When a database object is checked in, the DBVS server connects to that database and extracts the object's metadata in an XML format and versions it in a SCM tool of your choice. That means there is no need to version SQL scripts at all.
Find out more about DBVS at http://www.dbvsys.com. Comments and suggestions are always welcome. I'd be interested to hear your thoughts.
Chief Technical Officer
Database Versioning Systems