5 Tips for Better Database Change Management

5 Tips for Better Database Change Management:

Deploying new code that includes changes to your database schema doesn't have to be a process fraught with stress and burned fingers. Follow these five tips and enjoy a good nights sleep.

1. Deploy with Roll Forward & Rollback Scripts

When developers check-in code that requires schema changes, that release should also require two scripts to perform database changes. One script will apply those changes, alter tables to add columns, change data types, seed data, clean data, create new tables, views, stored procedures, functions, triggers and so forth. A release should also include a rollback script, which would return tables to their previous state.

This idea of database change management is popular as Migrations in Ruby on Rails. It is a convenient way to enforce good coding, and better database change management. However, keep in mind it has it's limits. See Perform Backup section below.

2. Ban SELECT * FROM table

Whenever you select from a table, be sure to specify the column names in all of your SQL code.  That way if later features or code changes add columns to that table, there is no potential to break old code that did SELECT * and got the old table column structure.  If the table was formerly returning four columns and after your code and schema changes returns five columns, it might break code that uses SELECT *.

The same goes for update statements.  Be sure to specify columns explicitely, and have a policy to enforce this on developers.

3. Perform Backup on Affected Data

Database rollback scripts have their limits, so a full database backup on any affected tables, or to be thorough the entire database, is a fully insured way to deploy database changes.

Since the developer's script cannot account for data in a live database, a rollback script will not work on database changes that do any of the following.  Only a backup will suffice:

• drop a table


• drop a column


• delete any data

4. Build in Feature Flags

Feature flags provide the operations team with a mechanism to turn on or off a new feature.  If you're going the extra mile to build in these switches you will make rolling out new features a lot more trouble free.  Even better, include a mechanism to enable or disable a feature for a percentage or class of users.  That allows you to dial the feature on gradually or expose it only to a certain group of users.   In an emergency this will also come in handy as a way to quickly disable a new feature that has gone awry.

5. Involve DBAs in the Review & Test Process

Before deploying a new release, include a process whereby the DBA must review schema changes and scripts.  This provides an additional sanity check, and a Database Administrators perspective on those changes.  He or she may well provide insight or caution on potential gotchas that you might encounter.