Database Migration – Upgrading Schema on Client Machines

databasedatabase-developmentmigrationsoftware-updatesversioning

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.

Best Answer

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.

Related Topic