23

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! :) )

pearcewg
  • 9,545
  • 21
  • 79
  • 125
  • 1
    Why would you care if the clients can access the field? If they edit it directly, isn't it just going to mess up the application, and then it will be their own fault? – mellamokb Apr 17 '12 at 18:32
  • @mellamokb - great callout, but we need this specifically because folks on site are doing exactly that: mucking with the database. We want this to be for forensic investigations, so hiding it (in SQL Meta Data for example) just decreases the odds of alteration. – pearcewg Jan 31 '13 at 21:46
  • 2
    @pearcewg if you want forensics, then you're stepping into the field of security and then you're missing the point with your requirements. You should not try to protect the database from people who own it - that is called "overprotective design". If you think really deep, you'll find that there is absolutely no way for you to know whether or not a guy with SA password was messing with the database. That's the whole point. Making the solution "more difficult to find and manipulate" is just making you feel protected when you are not. – Zoran Horvat May 14 '13 at 12:30

4 Answers4

22

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!

David Atkinson
  • 5,759
  • 2
  • 28
  • 35
  • David: That's an excellent answer, and basically what I was thinking as a solution after a bit more research. Thanks! – pearcewg Apr 18 '12 at 12:59
  • 1
    No problem. Just glad to help! Of course this doesn't prevent the clients from making changes to the database, which will make it out of step with the version number you've labelled it with. This is known as database drift, and is a fairly common issue. – David Atkinson Apr 18 '12 at 19:37
7

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

Zoran Horvat
  • 10,924
  • 3
  • 31
  • 43
6

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.

kemiller2002
  • 113,795
  • 27
  • 197
  • 251
1

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.

Diego
  • 7,312
  • 5
  • 31
  • 38