Migrating an Oracle database with a C# application attached to it: How to manage database migration?

Question

I have a C# application which works with an Oracle database and has been already shipped. Now it's time to ship out a new release. The C# object model have been revised and had an impact on the table structure.

If I ship out the new release, I need to take care of existing data. Just dropping tables and recreate these tables wouldn’t make any customers happy.

To counter this problem I have collected SQL scripts, which alters the previously released database structure to the new database structure. In the course of this, the data are migrated too. The SQL scripts are committed to a repository like C# source code. The patching of the database is tested on regular basis with the help of CruiseControl.NET. NUnit tests are run against the patched database to uncover mismatches between database tables and C# object model.

The whole procedure does work, but I have the feeling that this could be done better. I regard database migration as very critical. A shipped application, which doesn't work with a wrongly patched database, has no value. Losing data is inacceptable. These horror scenarios might make me think not to change the database at all. So it’s very important for me to have full confidence in the tools and practices I use.

Last week I stumbled over LiquiBase and I asked myself -- and now in SO:

What tools or practices can help to do database migration with lesser risks and more confidence? Are there any good books or internet resources out there?

I am especially interested in specific solutions to C# and Oracle, which might fit in the development procedure I have outlined above.

Answer 1

Database upgrade scripts must be part of development process. Here is one way of keeping track about database schema upgrades:

• create VERSION table in database that contains one record with version number
• each time you make change to database schema of your application you should:
  • create SQL script for creating, altering or dropping database objects
  • create SQL script for managing data changes that must be done with new data schema (e.g. insert defaults in new fields, insert default records in new tables, create script for splitting or merging tables, ...)
  • increment database version number
    • For each change I usually create one script named DbVerXXXX.SQL that contains all necessary upgrades (XXXX is version number). Also, I do changes in small steps - change DB schema only for next change you will do in your application. Don't create database upgrade that will take weeks or months of work to upgrade your application.
• create script that will upgrade your user's database to new version:
  • script should check current version of database and then execute database upgrade scripts that will convert schema to required level
  • change version number in VERSION table

This process enables you to:

• put all database schema changes under source control, so you have complete history of changes
• try and test your upgrade scripts on test databases, before you ship it to customer
• automatically upgrade user databases with confidence