The Definitive Guide to Database Version Control – Part 2

This is part two of a seven-part series on database version control. In part one we reviewed how the methods for creating software evolved into Agile and DevOps and how automation is based on version control. We will now discuss why version control for the database is a key component of DevOps. We will go through the most popular methods being used and the shortfalls of each. We will then reveal the correct way to practice database version control in order to maximize value and eliminate risk to the organization.

Most IT applications today have many components using different technologies, such as mobile, ASP, PHP, application servers, Citrix, databases, etc. These must all be in sync for the application to work. If, for example, a new column was added to a table, or a new parameter was added to a stored procedure, all other application components must be synchronized to the structure change in order to function correctly. If this synchronization breaks, then the application can fail by calling the wrong parameters to the stored procedure, or by trying to insert data without the new column.

The unique properties of the database component differentiate it from other components:

Must-Have Requirements

There are several challenges that must be addressed when managing database changes. You must:

• Ensure all database code is covered (structure, code, reference content, grants)
• Ensure the version control repository can act as the single source of truth
• Ensure the deployment script being executed is aware of the environment status when the script is executing
• Ensure the deployment script handles conflicts and merges them
• Generate a deployment script for only relevant changes
• Ensure the deployment script is aware of the database dependencies

In part three of the series, we will examine the most basic method for managing database changes, which is to save the alter command in a script or set of scripts, and manage them in the existing file-based version control. We will see if this method overcomes the challenges for the database.