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.

For better database change management, you need to be a little bit tricky. You can make it more efficient by deploying roll forward and rollback scripts, performing backups, reviewing tests, and much more.

In this guide, we have provided 5 tips for better database change management. Let’s explore them below and implement them to manage the changes efficiently.

Five Tips On Managing Database Change Efficiently

Follow these five tips and enjoy a good night’s 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 its 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 return five columns, it might break code that uses SELECT *.

The same goes for updated statements. Be sure to specify columns explicitly, 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, including 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.

Frequently Asked Questions (FAQs)

What Is Data Change Management?

Data Change Management allows users to manage their own contact data while letting data stewards easily review and accept/reject changes to proactively manage data quality.

How Can Change Management Be Improved?

You can improve change management by identifying what will change, creating a roadmap, gathering data for evaluation, collaborating with teams, ensuring proper communication, and reviewing the improvement process continuously.

Conclusion

So, that’s all the five tips for better database change management and we hope, you can handle the database change management more efficiently after reading and implementing these tips. Thanks for reading!