This is part two of a four part series on database deployment automation. In part one we examined the evolution of Agile and DevOps. We will now look at the challenges of automation for the database, the various solutions, their shortfalls, and then reveal the proven best practices.
DevOps is a natural evolution of the software industry, it’s not a revolution.
One way of dealing with the database challenge is to force the database into the generic process: create scripts out of database objects and store them in the traditional version control.
That creates other challenges, namely:
- Scripts in the version control system are not connected to the database objects they represent as these are two separated systems. Coding and testing of the database code is done at the database side, disconnected from any of the coding best practices (check-in, check-out, labels, etc.), and prone to all illnesses of the "old days" such as:
- Code-overrides in the database are common as there is nothing to prevent it.
- Scripts are required to be taken from the version control before starting the code on the database, to prevent working on the wrong version; but there is nothing to enforce that.
- Scripts do not always find their way to the version control system, as it depends on the developer to remember to do so.
- Out of process updates go unnoticed, etc.
- Scripts are manually coded, prone to human error, syntax error, etc.
- To have everything you might later need, you actually have to save two to three scripts for each object; the actual code of the object, the upgrade script, and a roll-back script.
- Scripts are hard to test in their entirety, as you make changes to a single object, while someone else makes a change to another single object, and running in any order would usually raise errors due to faulty dependencies between these scripts that need to run in a specific order.
- If a script is developed as a single script to represent the entire update, instead of a single change, it can deal with dependencies, but is much harder to deal with project scope changes. It is a big list of commands.
- And these scripts, unless super sophisticated, are unaware of changes made in the target environment during the time passed from their coding to the time they are run, potentially overriding production hot-fixes, or work done in parallel by another team.
- Content changes are very hard to manage. Metadata or lookup content does not practically fit into the version control. In most cases, they are simply not managed.
In part three of the series we will look at the standard "compare & sync" solution and its shortfalls.