Database deployment best practices

Every database object shall be stored in a separate file in version control system. Version control system could contain files like in this example:

|- tables
     |- employees.sql
     |- contracts.sql
|- packages
     |- contract_api.sql
|- functions
     |- get_employee_name.sql
...etc...

Whenever you modify some DB object, then you must also modify the appropriate SQL (DDL) file in the version control system. For example, if you modify package contract_api, then you update file contract_api.sql. As this file has been modified - it can be installed, say, by a continuous integration engine.

BUT, as you know, there are DDL scripts, that can't be executed twice. For example 'CREATE TABLE mytable...' script can be executed only once. And if your system is already in production, then you can't afford 'DROP TABLE mytable' statement in the header of your 'CREATE TABLE...' script. Therefore for production systems you need to create so called delta scripts that will deliver only changes. In this case you could simply create a new file called employees_upd01.sql that contains statement 'ALTER TABLE mytable ADD COLUMN...'.

After some time your repository could look like this:

|- tables
     |- employees.sql
     |- employees_upgr20091001.sql
     |- employees_upgr20091004.sql
     |- contracts.sql
|- packages
     |- contract_api.sql
|- functions
     |- get_employee_name.sql
...etc...

And this is OK, because: 1) when you need to deliver todays' incremental changes to database - you deploy files that were modified today 2) if you need to deploy a clean installation of your system - you run all scripts in order, e.g. first employees.sql, then employees_upgr20091001.sql, etc.

As each DB object is in a separate file in version control system, you have a good control over all changes.

In one project, I have all my DB changes in DDL scripts. Those scripts contain the SQL statements which are necessary to upgrade the DB to a certain version. The filename of the script also contains the version-number of the DB to which the DB will be upgraded (_versionnumber.sql)

Next to that, I've a small application which upgrades the DB to the latest version, by executing those script files in the correct order (from current versionnr of the DB to the last script-file).

For new projects, I now use Migrator.NET. This framework lets you write your DB changes in C# classes. The framework has a console application with which you can execute the DB changes, and it is also possible to use it with msbuild.

We have our db under Source Control. Any changes are tracked that way. Anything else would be a nightmare.

Jeff has an article on it too - http://blog.codinghorror.com/get-your-database-under-version-control/

SQL Server has the Generate and Publish Scripts Wizard, which can be really useful if you want to put an existing database into source control.

Scripting and storing every change you made in SQL is the best way IMO.