database migration: upgrade schema deployed on client machines

Question

I'm working on an application, which when deployed is installed locally on client machines, and uses a local database server. Stupidly, I didn't plan for schema changes -- and now it needs to change.

Perusing lots of SO and Programmers questions, and googling, I've found a few strategies:

• many SQL files. One for each version. When upgrading, use version number to decide which SQL file(s) to execute
• one SQL file. No drop-statements, tables created with `create table if not exists -- so when upgrading or installing the first time, simply run this file. Everything already in the db will be fine, all new tables etc. will be created
• blow away, then rebuild db.
  
  1. make a copy of all the data
  2. drop the entire schema
  3. load in the new schema
  4. reload the data
• auto-generated diff file. Seems to be mistrusted by some; tool for generating such a file costs money (??)

I'm looking for advice to help decide between these strategies.

An additional note: the data is upwards-compatible (if that's the right term): none of the schema changes involve dropping tables or columns, or changing FK/PK relationships.

Answer 1

I would personally do it the following way:

1. The applications released to your clients must have clear version numbers, for example "1.2", "1.4" etc, which is easy to be quickly determined on the client machine (registry/configuration file/table).
2. The database schema for every single version is known and constant on every client machine.
3. When you roll-out a newer version of your application you just implement the necessary upgrade the the latest public version (upgrade from previous minor version), for example from 1.2 to 1.4, 1.4 to 1.6 etc. In your script you perform all necessary updates: adding new columns, keys, tables, references etc.
4. You installer should bundle all update scripts:
   • When your customer runs the installer it first checks for the installed version.
   • Depending upon the determined version all scripts are applied sequentially until the database is updated to the latest version.
5. If your release cycle is short and you often release new versions, you might think of impelementing a cummulative updating scripts from one major version to another, so that you can diminish the number of scripts applied. You still first update the client DB to the next major version using single scripts and then quickly upgrade from major version to major version using this cummulative update scripts.

The main advantage here is that you don't have to support many scenarios for update: you just make diff against the latest public version and maintain this part of your update.

Answer 2

I deal with this in code - this is a variation on having a set of database files that you run in that its a sequence of DDL (and CRUD SQL where appropriate) that needs to be run in a specific order but I have it embedded in a file rather than as a set of scripts.

I then put a version table in the database.

The code is then straightforward:

1. If the database doesn't exist then create a standard blank database.
2. The database does now exist, so run all the schema update methods in order - each method updates (adds a row to) the version table. Each update is a transaction so that if it fails you're left with the previous version.

Because its code you can wire it in to wherever is appropriate, the installation process, the application itself (good for desktop apps), wherever - and you may well be able to setup a backup of the data prior to the schema changes. Additionally you can wire the schema version check into your application - so that it won't run if the versions don't match.

I won't assert that this is a perfect system - but I will assert that it has been consistently successful for me for at least 15 years using first Access and subsequently SQL Server databases.

In terms of your current setup, the biggest issue is working out a reasonable method to establish a baseline for existing systems - once that's done subsequent updates become almost trivial.

Answer 3

SQL file for each schema version change with creates/alters/etc.