Techniques to help plan for and ease the migration of a database schema.

Changing a live database schema can be straightforward as this example demonstrates with the techniques used in upgrading from DBMail from version 2.2 to 3.

The steps for this successful project are

  • Preparation
    • Develop and test change scripts
    • Communicate timing and issues to users
  • Action change
    • Ensure contingencies are in place
    • Perform the upgrade
    • Test success and then Commit

Preparation

Your own requirements and risk assessment should guide you to the areas where you need to spend the most time and resources. This example uses a single 8Gb database in a low volume environment upgrading from dbmail 2.2 to 3.0-rc3.

Develop and test change scripts

Although there are excellent graphical tools to help carry out database maintenance tasks, by putting sql schema and data changes into a script allows them to be tested and repeated with considerably reduced risk of error.

Often an application is tailored to suit local requirements, frequently by adding or amending tables; blindly applying any vendor upgrades may be inappropriate. Fortunately most upgrades are straightforward due to the nature of SQL in that as they are structured, it is not usually difficult to compare a current to a target schema and prepare/modify a change script by hand, though if the quantity of items to compare is large then something to avoid tedium is helpful to reduce fatigue and errors.

To reduce downtime, replication is often better than backups or dump and restore. By replicating a database, you get almost instant updates so the backup is never out of date and your contingency is sound.

The first thing is to prepare a copy of the database. To speed the initial development and testing of the scripts the data is not needed, a schema dump is all that is required. For MySQL the option --no-data is useful here, though additional options to ensure Routines and Triggers are copied if required and depending on the version of MySQL Views may need special attention. PostgreSQL has --schema-only and pretty much does the right thing.

Using a vendor upgrade script is often a good starting point as they have done all the preparation to successfully upgrade from one default schemas to the target schema, if there have been more than one upgrade then there may be multiple scripts, possibly with additional application specific updates to perform. This example shows a simple upgrade as the techniques are often the same.

Although the diff program may be of help, often a line by line manual comparison is required. This isn't as daunting as might be expected as the vendor upgrade scripts identify the changes required and since it is a Structured query language, not that difficult to navigate and interpret.

Finding which statement caused an error is easy when preceded by SELECT 'Tasks to be performed';. Commenting the output of the script is increasingly important as the size of the script output increases. An example output might look like the following.

+----------------------------------+
| Dropping old legacy cache tables |
+----------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

...

+---------------------------------+
| Updating dbmail_referencesfield |
+---------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

...

This gives easily identified access to errors and important progress reports on live updates that may take some time. In this example, schema changes aren't changing the data so there are a lot of statements with Query OK, Records: 0. In MySQL warnings should be switched on to help identify them and the possible cause.

Indexes, Constraints and many others often need appropriate placement and the ability to test is often essential to a successful upgrade. With the script working as expected it can be tested on a copy of the full database to ensure that the data does not cause issues and the output can be tested. The time taken to upgrade the complete database will help with finalising the eventual upgrade plans and timing.

Communicate timing and issues to users

As ever, communication is most important. The difference between a prognosis and an excuse is timing. By letting your user community know in advance about planned maintenance they can more easily avoid the inconvenience caused by any downtime, sufficient testing should identify issues likely to be encountered and so reduce the support workload following the upgrade.

Action change

Ensure contingencies are in place

Reducing downtime is easier with replication, a replicated database can be in place ready in seconds to be used as required, a dump ready for restore would take much longer. Replacing a live server with a backup replica is outside the scope of this document but is straightforward and can take less than a minute.

Perform the upgrade

When the time is ready to action the change, with good preparation and confidence of successful tests the upgrade should proceed smoothly. In this case stopping smtp queue runners and dbmail daemons is all that is required preventing users from accessing the database during upgrade but still allowing incoming email to queue ready for delivery.

After ensuring the replicated server is up to date, stop the replication so it can be used should the upgrade fail.

Run the upgrade script and check for errors. The 8Gb example database used here took only a couple of minutes to run, the application was then upgraded from dbmail 2.2 to 3.0-rc3 and the upgrade completed with a maintenance run which alas took a few hours. Fortunately the application maintenance can run in parallel with a live system and so the smtp queue runners were switched on and the dbmail pop and imap daemons started allowing users access to their email.

Had an error occurred, it would have been easy to replace the replica database with the failed upgrade in minutes and continue as before with no loss of data and only a few minutes downtime, rescheduling the upgrade at a later date with the hindsight of any errors.

To complete the successful upgrade, any external replicas can be switched back on and the support desk and users informed of another successful upgrade with enhanced facilities.

By Alan Hicks