Re: How do you handle Database Configuration Management?
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: niall.litchfield@xxxxxxxxx, ryan_gaffuri@xxxxxxxxxxx
- Date: Wed, 28 Nov 2007 05:36:23 -0800 (PST)
Further to Niall's answer:
There are a number of related aspects for you to manage:
-- creating a clean new schema
-- populating a clean new schema with clean, new seed (reference) data
-- upgrading an existing schema
-- upgrading the seed data
-- upgrading the non-seed (customer, transaction) data within an existing
schema.
For an internal (bespoke) application you only need to maintain one or two
upgrade steps at a time, and you should be able to test extensively on a clone
of production (unless you are working for a moronic corporation... which has
been known).
For a product with multiple client installs (eg a vendor's application) you
will certainly need to maintain an upgrade path across several versions. (eg
1.0 to 1.1. to 1,2 to 2,0 ... and any patches in between). Your testing of each
step has to be much more thorough, as you obviously don't have access to all
the target schemas to be upgraded.
I always recommend that you provide an upgrade set for each version step, and
then a thin wrapper that identifies the start version and applies all necessary
upgrades in sequence until it reaches the latest version (or stops on serious
error). This means you should record the schema version and upgrade history in
the schema itself. I have worked on products that retained the ability to
upgrade from any start version 1.0 and up right through 30 plus point versions,
over a 5 year life cycle.
Within each upgrade, some operations can be executed in parallel (maybe -
either by parallelising specific CTAS steps, or by running independent steps in
parallel). Only the application designer knows whether that is (a) worthwhile
and (b) predictable across all client sites (which may have different data
skewing, partitioning etc).
Sadly I am not aware of any tools which provide everything you need to automate
this process. I have used Oracle Designer as an aid to generation of "new
schema" and "database upgrade" scripts; but Designer version management (IMHO)
is to be avoided like the plague, and certainly Designer won't help you update
seed and customer data. However it is relatively simple to provide automated
scripts to extract the necessary SQL from your source code control system (eg
ClearCase, Synergy, SCCS, whatever) and package it up into a deliverable (eg a
tar file or zip file that you can ship to your DBA, client, or other 3rd party
who will actually execute the upgrade).
If you have branching application versions, then you will eventually need to
control the merging of these upgrade scripts. Try to organise your concurrent
developments to be independent of each other (so merge is simply additive). Try
and keep this under control of a single team (call them DB designers, DBAs,
Design Authorities, or what you like - just so long as they can get and keep a
grip on things).
Make sure there is a clear framework for building upgrade scripts; remember to
log progress, any errors or warnings, etc, in a consistent and reliable way.
For example, personally I prefer to avoid SQL*Plus SPOOL command for the simple
reason that a misplaced SPOOL OFF can cause some error output to be lost, and
if you are using nested scripts it's not always clear which level is
responsible for logging and error detection.
Finally, good luck. Data upgrade is often the Cinderella of the project - but
screwing it up can wreck your corporate data assets (or better, your clients'
assets). If you're lucky, the upgrade will fail, you'll restore from a backup
(that's in your upgrade plan, right?) and you'll just have a red face. If
you're unlucky, it all goes through, everyone loves the new version, and a
couple of weeks later the accounts don't balance because your CTAS accidentally
migrated only 98% of the rows of some table. Test, test and test again!
HTH
Regards Nigel
Other related posts:
