There are a lot of people who want an Agile Database Release Solution. I’ve listed a few links and occasional interesting bits from them, but the common solution seems to be a master script of all database changes.
‘Agile Database Techniques’ A book by Scott Ambler – Describes three types of log files: “Database change log : contains the DDL source code that implements all database schema changes”, “Update Log: source code for future changes to the database schema that are to be run after the deprecation period for database changes”, and “Data migration Log”.
Agility and the Database – A detailed discussion of the problem and high level solution.
Applying Agile SCM to Databases – “Databases come with state.” A short paper on the problems.
Version control of database objects – a long and interesting dicussion talking about a big change log solution. An initial set of considerations: “Track the end state of the object or modifications?”, “Support for automatic builds”, and “Support for branching and merging”. One of the comments describes and interesting approach, to comment out the previous DDL in the source file, so it is still there, but only the new migration command will be executed. The concept of being able to generate whatever artifacts you may be interested in is also mentioned (for example, if you want a clean table creation script for a table, or history of changes for one table, go generate it – don’t repeat yourself in the version control system).
SCM for Databases? – A short overview of some issues. One comment points out the problem with ‘difference’ tools is that they only catch certain changes – not all.
Our Database/SQL version control process – all DML and DDL changes are logged, but this one does not sound like it was automated.
The Database Patch Runner – all alterations for a “conceptual change” are written in one patch file and the patch files documented in a separate txt file. The patch file is actually a PL/SQL procedure that will be executed by a “patch runner”. The patch runner ensures that no patch is ever run into one database more than once, I am guessing that the patch runner knows because it keeps a log file, or an install log within the target database. “In response to the single script with history for each table. Until you’d mentioned it I hadn’t noticed that it wasn’t there, so no…I suppose we don’t miss it.”
Bugzilla Approach – “one great big ‘create-database.sh’ script…Whenever someone wants to make a change to a schema, they add it to the bottom of the script, along with a test to see if the change has already been applied.”
Version Control of Database Data – uses DROP table first then and number of scripts to recreate new table and reference data. Does not seem to address what happens to production data.
XP and Databases – “act as if the database is easy to change”. This article describes databases as Gold (production), Silver (migrated Gold ie test) and Bronze (migrated Silver – ie development).
Database scripts and version control short solution comment that I didn’t really follow.
“Sorry, we made database changes so there is no going back” Software releases and database changes suggests that for every change script there should be an equal and opposite rollback script. I prefer the idea that in development, you are able to rebuild everything, and in production it should happen so rarely that the effort involved in dealing with a rollback would be (much) less than the effort involved in providing a repeatable solution.
A comment in The Database Patch Runner – Rollbacks mentions the Oracle “FLASHBACK DATABASE” to rollback the entire database – perhaps this is something that could be turned on before a production release?
The Database Patch Runner: Dealing with code contains an interesting comment about retirement of code. How do you stop using a package? “We produce a patch to drop the objects we no longer need and then remove the objects from version control….Obviously you need version control software that deals with this properly (earlier tagged versions should still contain the removed scripts)”.