This is the final part of a 4 part series on database deployment automation. In part 3 we examined the shortfalls of the commonly used “compare and Sync" method. We will now reveal the proven best practices database automation.
Achieving automation by scripting database objects change-scripts into traditional version control is limited, inflexible, disconnected from the database itself, and may be untrue and prone to miss updates of target environment because of conflicting scripts. Using ‘compare & sync’ tools is a risky thing to automate. The two concepts do not play together, as one is unaware of the other. A better solution must be found.
In order to take a database into proper automation, you must factor in the following:
- Proper database version control, dealing with databases’ unique challenges (structure, code & content), while enforcing a single work process. This prevents any out of process changes, code overrides, or incomplete updates.
- Leverage proven version control best practices (check in&out changes etc.) for complete information about who was doing what, when, and why. Making sure changes are perfectly documented is the base for later deploying them.
- Harmony with task based development enables correlating each version control change with a change request or a trouble ticket. This enables task based deployments, partial deployments, and last minute scope changes to be coordinated between code and database.
- Ensure configuration management & consistency so every development environment, branch, trunk, sand-box, and testing or production environment follows the same structure, and matching status; or any deviation and difference are well accounted for.
- Scriptable interfaces, to deal with automation of deployment processes, providing repeatable results every single time. Even the most sophisticated solution becomes cumbersome if you have to use the UI to do the same task over and over again.
- Provide reliable deployment scripts, which are capable of dealing with conflicts and merges of database code, and cross updates from other teams; while also ignoring wrong code overrides, and are fully integrated into the version control repository.
- Provide automatically generated development scripts on the fly to deal with deploying any combination of project scope, from multi-schema mega-updates, to a single task based change and its dependent objects.
- Leveraging ‘Labels’ (Tagging of database structure snapshots and relevant content) before and after deployment of changes, to act as a safety-net, so quick and easy roll-backs are always close at hand.
- Fully integratable to other systems (ALM, Change management / trouble tickets, build servers, and release managers).
Implementing a solution to deal with these challenges would enable a company to practice proper database automation. Database automation which would be easy to integrate with the rest of change and release processes, to achieve a complete end to end automation.
Once automated continuous integration and continuous deployment the last two steps of automation heaven, are just a decision away from reality.
The database sets up a real challenge for automation. Scripting database objects change-scripts into traditional version or using ‘compare & sync’ tools is either an inefficient or plain risky thing to automate, as the two concepts are unaware of the other. A better solution needs to be implemented in the shape of DevOps for database.
DevOps for database should follow the proven best practices of change management, enforcing a single change process over the database, and enable dealing with deployment conflicts to eliminate the risk of code overrides, cross updates and merges of code, while plugging into the rest of the release process.