The Definitive Guide to DB Version Control - Part II
Part 2: The Database is a Key Component
Exploring the lay of the digital land brings us to an important question: why is db version control a key component of DevOps? To answer this question, we'll need to dive a little deeper into the traits that govern proper database management as well as the outcomes required of it.
Of course, not all management methods are created equal. As such, we'll be undertaking this exercize with the express purpose of identifying a "best" practice for db version control, which maximizes value and eliminates risk.
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.
As such, the following properties, unique to the database, differentiate it from other components of the DevOps ecosystem:
- A database is more than just SQL scripts. It has a table structure, code written in the database language within stored procedures, content that is saved in reference tables or configuration tables, and dependencies between objects.
- A database is a central resource. Several developers can work on the same object, and their work must be synchronized to prevent code overrides.
- Deploying database changes is not as simple as copying and replacing old binaries. Database deployment is the transformation from version A to version B, while keeping the business data and transforming it to the new structure.
- Database code exists in any database, and can be modified directly in any environment. This is unlike other components, where everything starts from a clean workspace in the build server.
With these unique properties in mind, 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 db 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
It's no easy task managing these properties and requirements, especially since they're subject to competing interests or conflicting outcomes.
As we take our analysis to the next level, therefore, we will review the most popular methods being used to manage databases and examine the shortfalls that burden them. From there, it's just a hop, skip and a jump away from realizing your true database potential.
What are the database source control best practices? Read more.
This is part two of a seven part series on db 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.
In part three of the series, we'll examine the most basic method for managing database changes – saving the alter command in a script or set of scripts, and manage them in the exiting file-based version control. We will then examine if this method overcomes the challenges for the database.