Using a change management system, sometimes called source control or version control, is part of the foundation for all the automation required when we begin to build a DevOps process. However, change management brings several additional benefits to the database that make implementing source control within your database systems extremely attractive, all on their own.

The nature of a source control system adds the following functionality:

• Auditing of the changes made and the source of the change
• The ability to undo, or rollback, changes to a database without requiring a restore
• A known state for comparison between environments and tracking changes over time
• An additional backup of the code that defines the database
• The isolation of different development processes from each other

To better understand these benefits, let’s address them one at a time.

Auditing

Implementing a change management process within a database immediately adds auditing capabilities that previously were not possible or were very hard to build and maintain. The very nature of most source control systems means that, out of the box, you get auditing capabilities.

The first and most obvious of these is that you’ll be able to tell who made each change. As developers pull their code from the appropriate branch of the source code repository, their changes to that code get recorded within the source control system. Days, weeks, or even years after the fact, you’ll know exactly who made any given change in the code. This capability becomes useful in a number of ways. Obviously, from an audit perspective, knowing who made the change is necessary. However, you’ll also receive the benefits when troubleshooting of being able to talk directly to who made the change to better understand what was done and why. You can also use this as a mechanism for education to ensure that bad or sub-optimal changes to the code can be tracked down to the individual.

Next, you’ll know when the change was introduced to the system. While frequently a problem is immediately apparent, often an issue doesn’t become obvious for days or even weeks. With change management, you can immediately identify when a change was made to assist troubleshooting issues that occur much later.

Finally, you’ll know what the change actually was. Going back to troubleshooting again, it’s frequently difficult to understand if a change was made and what that change was when all you have is the database as it currently exists. Sure, you could restore a copy of the database somewhere to identify the change, but a quick look through the history in source control will be much faster and easier. Then, you can address whatever bug fix is necessary.

Undo

The ability to quickly roll back an individual change is not something available in most database systems. Instead of an undo command, like CTL-Z on a Windows machine, you’re forced to look to backups. When we’re just talking about code, like with a view or a stored procedure, waiting for a very large database to go through a restore process can be extremely time consuming. Instead, in the event of a mistaken change to a piece of code, we can go to the source of the code, our change management system. The ability to quickly undo a mistake makes development much easier and more successful.

Known state

Putting our database code into source control gives us a way to establish exact moments in time. We can see the state of the database as of two weeks ago, despite any development and changes over that intervening period. That ability lends itself to a number of scenarios.

When we want to troubleshoot why behavior has changed, knowing the exact state of all the objects prior to a given change, or even a set of changes, allows us to identify problems more quickly. We can see what was changed, so we can more easily rectify it.

If we have deployed code to a given environment, such as test or pre-production, we can do that from our source control system. Then, we’ll know, just by looking at the source control system, what a given environment’s state is currently. From there, we can decide if we need to deploy new changes or if we’re ready to promote the set of changes defined by our known state to the next environment.

Finally, the known state of a source control system gives us the ability to track changes over time. We can see where we’re making changes to better understand how our systems are being developed. You may even spot trends in sets of changes showing us potential areas for improvement in our processes.

Backups

The ability to restore a given database to a moment in time is a vital part of any recovery process. Building and testing a backup and restore process should be fundamental to any system as a part of the protections built into that system. Change management offers us one more source from which to restore things in the case of an emergency.

Yes, this backup is code only and won’t include all the information stored within our database. However, because this backup is code only, our ability to very quickly restore code from it is much better than what we get from a traditional database restore process. Instead of waiting for an entire database to be restored, you can go directly to the piece of code you need. Further, because we have a history of the changes to that piece of code, you can also go to any one of those historical states as a part of the restore.

Isolation

Releasing that very first version of a piece of code or a database is frequently the easiest part of the process. Difficulties arrive when we must add new functionality or perform hotfixes for problems discovered in our code. Isolating new development from each other and new development from necessary hotfixes is a serious challenge. However, modern change management systems usually use a form of isolation called branching. A branch allows us to create a copy of the code separate from other copies of the code. We can then perform our hotfix in isolation. We’re not impacted by other development going on and can deploy our change independently.

While branching a database will require a copy of that database, branching the code of a database is simple. With this, we can then ensure that two different development streams working on new functionality can perform those tasks without breaking each other’s code. You’ll need to build some testing mechanisms for when those branches get merged back together, but in the meantime, development has not slowed down.

Conclusion

As you can see, there are many benefits from change management within a database. Any or all of these are immediately available to you when you start using a source control system with your database. Then, you can easily leverage this change management system when you later decide that you intend to use it for automating your deployments.