I need to put versions onto a SQL Server 2005 database and have these accessible from a .NET Application. What I was thinking is using an Extended Properties on the Database with a name of 'version' and of course the value would be the version of the database. I can then use SQL to get at this. My question is does this sound like a good plan or is there a better way for adding versions to a SQL Server database?
Lets assume I am unable to use a table for holding the Metadata.
I do this:
Create a schema table:
CREATE TABLE [dbo].[SchemaVersion](
[Major] [int] NOT NULL,
[Minor] [int] NOT NULL,
[Build] [int] NOT NULL,
[Revision] [int] NOT NULL,
[Applied] [datetime] NOT NULL,
[Comment] [text] NULL)
Update Schema:
INSERT INTO SchemaVersion(Major, Minor, Build, Revision, Applied, Comment)
VALUES (1, 9, 1, 0, getdate(), 'Add Table to track pay status')
Get database Schema Version:
SELECT TOP 1 Major, Minor, Build from SchemaVersion
ORDER BY Major DESC, Minor DESC, Build DESC, Revision DESC
Adapted from what I read on Coding Horror
We use the Extended Properties as you described it and it works really well.
I think having a table is overkill. If I want to track the differences in my databases I use source control and keep all the db generation scripts in it.
I've also used some ER diagram tools to help me keep track of changes in DB versions. This was outside the actual application but it allowed me to quickly see what changed.
I think it was CASEStudio, or something like that.
If I understand your question right (differentiating between internal database versions, like application build numbers), you could have some sort of SYSVERSION table that held a single row of data with this info.
Easier to query.
Could also contain multiple columns of useful info, or multiple rows that represent different times that copy of the database was upgraded.
Update: Well, if you can't use a table to hold the metadata, then either external info of some sort (an INFO file on the hard drive?) or extended properties would be the way to go.
I still like the table idea, though :) You could always use security to only make it accessable through a custom stored proc get_ db_version or something.
I am using dedicated table similar to Matt's solution. In addition to that, database alters must check current version before applying any changes to the schema. If current version is smaller than expected, then the script terminates with fatal error. If current version is larger than expected, then script skips current step because that step has already been performed sometimes in the past.
Here is the complete solution with examples and conventions in writing database alter scripts: How to Maintain SQL Server Database Schema Version
The best way to do is to have 2 procedures: one header to control what is being inserted and validations a footer to insert the data if the release is good or not. The body will contain your scripts.
You need a wrapper that will encapsulate your script and record all the info: as far release, script number been applied, applyby, applydate date, release outcome "failed or succeeded".
