Database Version Control The Definitive Guide Part III

Part III: Utilizing SQL Alter Scripts Generated During Development

The most basic method for managing database changes is to save the alter command in a script or set of scripts, and manage them in the exiting file-based version control. This guarantees a single repository that stores all the application component assets.

Developers have the same functionality when checking-in changes for the database as they do when they check-in changes for .NET or Java, such as linking the change to the reason. (CR, defect#, user story, etc.) Almost any file-based version control solution today has a merge notification when several developers change the same file.

The SQL Alter Scripts Checklist

So, can well-built SQL alter scripts serve as a solution to database version control challenges without adding new pitfalls? Let’s see:

✔ Ensures all database code is covered –

Since the developer or DBA writes the script, he or she can make sure it will handle all database code.

X  Ensures the version control repository can act as the single source of truth –

Not really, as the developer/DBA can login directly to the database (in any environment) and make changes directly in the database.

Changes made to the deployment scripts as part of scope changes, branch merges, or re-works are done manually and require additional testing.

Two sets of scripts must be maintained – the SQL create scripts and the SQL alter scripts for the specific change for the release. It should go without saying that having two sets of scripts for the same change is a recipe for disaster.

X  Ensures the deployment script being executed is aware of the environment status when the script is executing –

This depends on the developer and how the script is written. If the script just contains the relevant alter command, then it is not aware of the environment status when it is executed. This means it may try to add the column although it already exists.

Writing scripts that will be aware of the environment status at execution time significantly complicates script development.

X   Ensures the deployment script handles conflicts and merges them –

Although the file-based version control provides the ability to merge conflicts, this is not relevant to the database as the version control repository is not 100% accurate and cannot be the single source of truth. The script might override a hot fix performed by another team, leaving no evidence that something went wrong.

X   Generates deployment scripts for only relevant changes –

Scripts are generated as part of development. Ensuring the scripts include only relevant and authorized changes – based on the tasks being approved – requires changing the script(s), which creates more risk to the deployment and wastes time.

X   Ensures the deployment script is aware of the database dependencies –

Developers must be aware of database dependencies during the development of the script. If a single script is being used, then the change usually is being appended. This can result in many changes to the same objects.

If many scripts are being used, then the order of the scripts is critical and is maintained manually.

Bottom Line

Not only does such a basic to database version control approach fail to solve the database challenges, it’s also error-prone, time consuming, and requires an additional system to keep track of the scripts being executed.

In other words, SQL alter scripts alone are not the solution.