During the software development process, whether you’re cutting edge Agile developers, or traditional waterfall method folks, your code changes are periodically accompanied by database changes. For instance tables have particular rows and columns. When developers add new columns (ie fields on an index card), or create new tables, relationships, indexes or other database objects, all of these are lumped together as database changes.
Version control systems have brought great manageability gains to software projects, even ones involving only a single developer. That’s because they allow you to rewind to any savepoints, just like you can undo and redo changes in a word document. With database changes, however the picture because more muddied.
Database Change Management Best Practices
- Require developers to include a roll forward and a roll back script with each set of schema changes.
- Check in those scripts into the version control system just like other software code.
- Be sure developers test the roll forward and roll backward operation on dev.
- Ensure that changes are documented, as well as possible side affects.
During deployment, operations folks and/or a DBA must still have their fingers on the trigger. Some frameworks such as Ruby on Rails include Migration scripts. Do not for example allow rollback scripts to run automatically. This is a recipe for disaster.
- Be sure to take a backup of the database before running any schema change scripts.
- Consider application downtime if ALTERs or other operations may lock large tables.
- Perform another backup following the schema changes
- If database is particularly large you may isolate your backup to the schema or tables being altered.
Above all use common sense, and always second and/or third guess yourself. Better to be safe than sorry when juggling your crown jewels.