How to automatically upgrade deployed database for end-users

Question

I've read a fair number of questions on how to upgrade a database for developer and such, and I'm leaning towards using migratordotnet or something similar, however it seems the the upgrades need to be run with something like MSBuild or NAnt. These are not things I should expect an end-user to have installed.

Ideally the process should be the user installs the new version of the app, launches it and it takes care of everything behind the scene. Depending on how long the process takes I may or may not show a progress bar, and then it's done, the use the app normally blissfully unaware that there's such as thing as sql, msbuild, relational database, or anything even remotely technical sounding.

Other misc info:

SQLCE that's xcopy installed.

Single user database that most likely shouldn't be too large.

Releases will be fairly frequent with the program evolving through user input on requested features.

Initial release isn't done yet, so don't need to worry about any current data, just need a painless process for initial and subsequent releases (for the end-user, preferably for me as well!).

Using LINQ to SQL as ORM. (Do I upgrade the database first then
run SQLMetal to regenerate the
classes?)

Doing TDD (for the first time) and wondering how to automate testing upgrades.

Visual C# Express so no VS plugins.

Edit:

I guess MSBuild comes with the .NET Framework and not just VS, so I guess this is a non-issue and I can just use migratordotnet and just shell out to msbuild. If needed I could probably redirect console output and do some naive string parsing to get progress info, but I'll resort to that if it looks like it's taking long enough to warrant displaying actual progress instead of just a marquee progress bar.

Answer 1

Generally, the way this stuff is usually done is that the database migration is done at installation time; during install, you have a whole bunch of code (usually completely custom) which detects the existing version of the database, and does all the operations involved in migrating the data from the existing database to the new database (i.e. creating temp tables, copying data from existing tables into temp tables, dropping existing tables, creating new tables, performing the joins to add the data from temp tables to new tables, etc.). The process is, by definition, pretty complex. I'm unaware of any tools which will automate the conversion process specifically for an end-user production environment. (There's usually a lot of special cases involved in dealing with end-user databases.)

Answer 2

Here is an option:

1. Store a db version somewhere in you database
2. At startup ensure that the database is current by comparing a constant in your code to the db version.
3. If not current run a set of scripts to create/alter tables, convert data etc.

If you have already deployed and don't have the db version, then just check the schema for something you expect in the latest db version.

To test:

1. Start with a old database
2. Create a method to UpdateDatbaseIfNeeded()
3. The test should pass if the schema gets updated and the db version gets set

You can also preload your test database to test for various conversion issues that need to be handled by your upgrade scripts.

Answer 3

Here's what you can do with Wizardby.

It has an API which allows you to perform database schema migration tasks programmatically. You'll need an appropriate IDbPlatform (SqlCePlatform is there as well), a IMigrationVersionInfoManager, which has a standard implementation which stores version information in SchemaInfo table, a IMigrationScriptExecutive which has default implementation as well, and IMigrationService itself. Next, you write a migration definition, compile it into your assembly and feed it to IMigrationService.Migrate(connectionString, null, yourMigrationDefinition).

Answer 4

The only thing I would add to the above comments is that you also need to be worried about a pack type operation.

To upgrade your apps from SQL CE 3.1 to 3.5 required you install the 3.5 libraries, and then issue a pack operation to upgrade the database format. You may want to keep this in mind as a part of your process. For SQL CE 3.5 just opening the database in 3.5 will make it no longer compatible with 3.1 - meaning if your user then later restored a backup of the application or something you can't load it. So you need to trap those errors at connection time and make sure you handle all the exceptions from MS for version incompatibility.

Another gotcha with 3.1 and 3.5 is that you can't have them both loaded to Visual Studio at the same time. So testing this process over and over means a VM that you can wipe and upgrade repeatedly. You can't really do it on your development machine. :(