The Definitive Guide to Database Version Control — Part 5

this is part five of a seven part series on database version control. in part one , two , three , and four , we reviewed how the methods for creating software evolved into agile and devops, what separates the database from application code and the challenges that must be addressed when managing database changes.

we are now going 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.

another approach that is commonly used is to generate the database change script automatically by comparing the source environment (development) to the target environment (test, uat, production, etc.). this saves the developers and dbas time because they don’t have to manually maintain the script, if it is a create script or an alter script for the release. scripts can be generated when needed, and refer to the current structure of the target environment.

let’s review the challenges and see if this approach overcomes them:

✔ ensures all database code is covered – most compare & sync tools know how to handle the different database objects, but only a few have the functionality to handle the compare & sync of the reference data.

x ensures the version control repository can act as the single source of truth – simple compare & sync does not utilize the version control repository when performing the compare and generatin g the merge script.

✔ ensures the deployment script being executed is aware of the environment status when the script is executing – the best practice is to generate the script just before executing it, so it will refer the current environment status.

x ensures the deployment script handles conflicts and merges them – simple compare & sync tools compare a to b (source to target). based on the simple table at the left, the tool then generates a script to “upgrade” the target to match the source.

without knowing the nature of the change, the wrong script can be generated. for example, there is an index in the target that was created from a different branch or critical fix. if this index does not exist in the source environment, what should the tool do? drop the index? if there is an index in development, but not in production, was it added in development? dropped in production? using such a solution requires deep knowledge of each change to make sure they are handled properly.

x generates deployment scripts for only relevant changes – the compare & sync tools compare the entire schema and show the differences. they are not aware of the reason behind the changes, as this information is stored in the alm, cms, or version control repository, which is external to the compare & sync tool. you might get a lot of background noise, making it difficult to determine what you actually need to deal with.

✔ ensures the deployment script is aware of the database dependencies – compare & sync tools are aware of database dependencies and generate the relevant ddls, dcls, and dmls in the correct order. not all compare & sync tools support generating a script that contains objects from several schemas.

bottom line: compare & sync tools satisfy some of the must-have requirements, but fail to deal with others. scripts must be manually reviewed, and cannot be trusted in an automated process.

in part six of the series, we will review the only fully automated method for managing the database, which involves utilizing a database enforced change management solution.