Database deployment best practices

Question

Mostly we change existing database tables, stored procedures, functions or parameters in tables for software upgrades/bugfixes. And when it's time to deploy our changes to another environment like production or preproduction, some parts of our db changes are forgotten.

In our company, some developers use a database difference analysis application to find out the differences between test and production environment. Some developers store t-sql of every change they made on db, like me.

I wonder what are you doing to deploy db changes to production environment. Why you choose that way ? Or what must be done ?

Thanks for the replies !

Answer 1

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

Answer 2

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://www.codinghorror.com/blog/archives/001050.html.

Depending on your setup and database, the Database Publishing Wizard - http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard - can be really useful.

Answer 3

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.

Answer 4

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.

Answer 5

Hey All,

Check out TeamWork from dbMaestro. TeamWork is a dedicated point-solution for database source and change control. It provides exclusive object (and data) locking, full history, versioning, deployment and impact-analysis for Oracle (and MS-SQL coming soon). The product addresses many of the concerns mentioned in this thread AND it deals with the schema objects directly (you don't have to mess with importing/exporting the DDL). TeamWork integrates with industry leading SCM products to allow you to synchronize your database development changes with your source-code changes and deploy the changes in lock-step.

Hope this helps, Tal N!