Database change managing on software patch/update/upgrade

Question

It's been few weeks I'm trying to find a more detailed explanation with examples how to manage my database change on software patch.

Situation:

• I deploy wpf application release to user. App uses MSSQL data file for storing app's data (Deploying with Client Profile and SqlServer Compact 3.5 prerequisites)
• In further development database schema changes I add some extra data too
• I'm trying to run a patch/minor/major upgrade with Visual Studio 2008 publishing and msi

I want to update users app and database file, but not touch the data which was stored in that database. Seems easy, but i fail to find on web how to get this done with Visual Studio publishing

What I've collected so far are 2 choices:

• Create sql update script for each release and try update database on 1st program launch after patching.
• Create database setup project and try to script from there (no idea how to do this).

I'd be glad to hear what some of you are using or would use such case. Some link to examples/detailed explanation how todo would be awesome. I'm not very good at this deployment/database stuff...yet...:)

Thanks in advance.

Answer 1

This article on "Rails Style Database Migrations in .Net" might be useful. Since the rise of Rails there has been a lot of work on best practices for database migrations, which can be leveraged in other languages and platforms.

Answer 2

In my current project we use a technique called Continuous Database Integration.

We collect all sql change scripts (that can be creating / altering tables, stored procedures, but also migrating data, etc...), into a c# project and deploy them at the same time as the rest of the software.

Using this technique we can ensure that all developer databases are up-to-date as well, as it is integrated in the visual studio build system.

We run this MSBuild script for local builds and for deployment. It checks a version number in the datebase, and runs any scripts with a higher number.

I'm not so fond of MSBuild, but it gets the job done.

Martin Fowler has an article on evolutionary database design which has some background on this.

If you're using SQL Server management studio to make changes, you can use the "Generate Change Script" to let it script the changes for you.

Answer 3

If you add Installer class to your DLL or EXE it's Install method will be invoked during the setup (check our MSDN for details, VS can make a mini setup proj for you and then you can just copy the skeleton and attributes). Then you can run SQL commands against CE and backup existing CE files and even do a rollback if something goes wrong (Installer class has methods to follow full transnational steps on the level of the whole setup - the piece of mind thing :-)