CODEWITHSUNDEEP
sql-serverdatabase-schema
pearcewg
pearcewg

Reputation: 9613

Best Practices for storing a database schema version in SQL Server?

I have an app which will be deployed on production PCs with SQL Server. I want to be able to store and retrieve a version of the schema in my database. I'm interested in best practices to be able to accomplish this, with the following main goals:

  1. Able to store and easily retrieve a version number of the database.
  2. Hidden or more difficult to find and manipulate by clients.
  3. Able to be edited/changed when we create a new version.
  4. Backing up the DB or detacting the DB keeps the version # for forensics.

I wish there were a way to store a "version" in the metadata or not a normal table, which could be accessed/set through a system stored procedure.

Any ideas or best practices?

EDIT: One option I found which may be promising is to use SQL Server Extended Properties, to put a key|value assigned to the DB with "Schema_Version" and the version number. It isn't encrypted (but the value could be), and isn't hidden, but at least is removed from the actual DB structure which some of our users and field personnel browse (to my frustration! :) )

Upvotes: 24

Views: 15250

Answers (4)

Zoran Horvat
Zoran Horvat

Reputation: 11301

I've implemented a solution which is based on one table which tracks four-segment schema version (major, minor, build, revision) with timestamps when each version begins and ends its validity. Only one row has NULL for ending timestamp, and that is the current version.

In addition, there is a bunch of stored procedures which support this versioning system, and all database alters must use these procedures to test and update schema version as soon as they make any change to the database schema (e.g. add a table, drop a column, etc.).

Note that complete history of changes is stored in the table that tracks database versions. Solution is very flexible when anything goes wrong. For example, if an alter breaks in the middle of execution, all changes it has successfully made are remembered in the version table because after each step it has increased the revision number. You can improve the alter and run it again and it will automatically skip the successfully completed steps and continue from the first step it didn't make last time.

There is one more (optional) trick - I've used odd build numbers for intermediate versions and even build numbers for completed releases. In that way, as soon as an alter begins, it first changes the build number to the next odd value and then does what it wants to. If at any time you see an odd build number (third segment in the version number), then you're sure that some alter did not complete! Once the alter completes its last step, it simply changes the schema version once again, this time to the next even build number, just to notify everyone that it has completed.

You can view the whole source code, manual and examples in this article: How to Maintain SQL Server Database Schema Version

Upvotes: 7

David Atkinson
David Atkinson

Reputation: 5899

I'm the product manager for SQL Source Control and SQL Compare at Red Gate. We had to solve this very same problem as our tool needs to know which version the databases were at in order to select the appropriate migration scripts to build the full deployment script.

We considered a version table, which is the most commonly devised home-grown solution. However, from our research we learnt that users wanted to keep the set of database objects 'unpolluted' so we opted for the database level extended property. We append this to the scripts as follows:

IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
  EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL

When the database is loaded into SQL Compare, it performs a check to ensure that the version that it claims to be corresponds to the version as stored in source control.

Hope this helps!

Upvotes: 22

Diego
Diego

Reputation: 7562

What I did in a previous company was storing the version in a table with a few fields (major version, minor version, build and date applied), so that I could have a history of the updates. Setting proper permissions on the table was sufficient to prevent tampering.

If you really would like to make it hard to read for DBAs as well, you could store those values in the table as encrypted strings. This way only you would now how to decode them.

Upvotes: 1

kemiller2002
kemiller2002

Reputation: 115488

Truthfully, we just store the schema number each of our databases. We have a table in the database which is only used by the Software Configuration Management team, which tells us the current version so we can quickly see across environments which version is where. I wouldn't worry about putting it somewhere outside the db as that only complicates things.

I suppose if you really want to be secure you could always create a stored procedure with the value hard coded in it. Then you can encrypt the stored procedure so they can't view it/tamper with it without you knowing. You can just update the sp when you change the version for them. You could also go into the system and delete the stored procedure code out of the system tables after it is compiled, but I really wouldn't do this. It only leads to problems.

Upvotes: 6

Related Questions