sql - Do you put your database static data into source-control ? How?

Question

I'm using SQL-Server 2008 with Visual Studio Database Edition.

With this setup, keeping your schema in sync is very easy. Basically, there's a 'compare schema' tool that allow me to sync the schema of two databases and/or a database schema with a source-controlled creation script folder.

However, the situation is less clear when it comes to data, which can be of three different kind :

• static data referenced in the code. typical example : my users can change their setting, and their configuration is stored on the server. However, there's a system-wide default value for each setting that is used in case the user didn't override it. The table containing those default settings grows as more options are added to the program. This means that when a new feature/option is checked in, the system-wide default setting is usually created in the database as well.

• static data. eg. a product list populating a dropdown list. The program doesn't rely on the existence of a specific product in the list to work. This can be for example a list of unicode-encoded products that should be deployed in production when the new "unicode version" of the program is deployed.

• other data, ie everything else (logs, user accounts, user data, etc.)

It seems obvious to me that my third item shouldn't be source-controlled (of course, it should be backuped on a regular basis)

But regarding the static data, I'm wondering what to do.

• Should I append the insert scripts to the creation scripts? or maybe use separate scripts?

• How do I (as a developer) warn the people doing the deployment that they should execute an insert statement ?

• Should I differentiate my two kind of data? (the first one being usually created by a dev, while the second one is usually created by a non-dev)

How do you manage your DB static data ?

Answer 1

I have explained the technique I used in my blog Version Control and Your Database. I use database metadata (in this case SQL Server extended properties) to store the deployed application version. I only have scripts that upgrade from version to version. At startup the application reads the deployed version from the database metadata (lack of metadata is interpreted as version 0, ie. nothing is yet deployed). For each version there is an application function that upgrades to the next version. Usually this function runs an internal resource T-SQL script that does the upgrade, but it can be something else, like deploying a CLR assembly in the database.

There is no script to deploy the 'current' database schema. New installments iterate trough all intermediate versions, from version 1 to current version.

There are several advantages I enjoy by this technique:

• Is easy for me to test a new version. I have a backup of the previous version, I apply the upgrade script, then I can revert to the previous version, change the script, try again, until I'm happy with the result.
• My application can be deployed on top of any previous version. Various clients have various deployed version. When they upgrade, my application supports upgrade from any previous version.
• There is no difference between a fresh install and an upgrade, it runs the same code, so I have fewer code paths to maintain and test.
• There is no difference between DML and DDL changes (your original question). they all treated the same way, as script run to change from one version to next. When I need to make a change like you describe (change a default), I actually increase the schema version even if no other DDL change occurs. So at version 5.1 the default was 'foo', in 5.2 the default is 'bar' and that is the only difference between the two versions, and the 'upgrade' step is simply an UPDATE statement (followed of course by the version metadata change, ie. sp_updateextendedproperty).
• All changes are in source control, part of the application sources (T-SQL scripts mostly).
• I can easily get to any previous schema version, eg. to repro a customer complaint, simply by running the upgrade sequence and stopping at the version I'm interested in.

This approach saved my skin a number of times and I'm a true believer now. There is only one disadvantage: there is no obvious place to look in source to find 'what is the current form of procedure foo?'. Because the latest version of foo might have been upgraded 2 or 3 versions ago and it wasn't changed since, I need to look at the upgrade script for that version. I usually resort to just looking into the database and see what's in there, rather than searching through the upgrade scripts.

One final note: this is actually not my invention. This is modeled exactly after how SQL Server itself upgrades the database metadata (mssqlsystemresource).