Schema Version Control

An outbreak of synchronicity has produced a volley of blogs and questions about database version control in the last few days.

• Tom Kyte adds his own twist to this story at Coding Horror.
• JAson Hinds asked about database release management on one of the OTN forums.
• And Syed Jaffar Hussein (the Human Fly) launched a thread on Oracle-L on the subject.

I seen so many projects take code control seriously but have no real idea how to control a database definition (let alone how to control changes to multiple instances of development, test and live data, both in house and on client sites).

Code control is a walk in the park compared to "schema control" (where the delta , like an ALTER TABLE needs to be developed alongside the new version of the database object creation script).

And "data control" adds even more challenges:

• can you upgrade the data in place? or do you need to create a replacement object?
• Is there enough space?
• Do you need to suspend user access? How long will it take?
• what are the chances of reversing the upgrade if something goes wrong?
• Will you even know if something goes wrong?
• Who decides go forward, go back or muddle through?

A complex application version upgrade should be treated with as much respect as a data migration project. There should be a strategy (sure, it may be a standard strategy). Use standard tools / scripts. The more the process is repeatable - and the more it is repeated - the better. Make sure that all likely error conditions (missing log files, out of DB storage, non-writeable working directories, no listener) are tested for - test the tests!

By the way, the "process" includes all the operating procedures. Make sure that upgrades aren't nannied through by developers. Script the whole thing; have the production DBAs try it out the procedures as early as possible in test to make sure that they understand what's supposed to be happening (and how to know if it worked OK; the best thing is if the script just says:

IT ALL WORKED OK

or

SOMETHING WENT WRONG IN STEP 13 - SEE LOGFILE xxxyyy13

By the time the schema-and-data upgrade is executed for real, nothing - not even the unexpected - should be unexpected.