This is part 3 of a 4 part series on database deployment automation. In part 2 we examined the seven top challenges of automation for the database. We will now look at the commonly used 'compare & sync' method, examine its shortfalls, and then reveal the proven best practices.
Another concept that emerged in the last decade is using tools for dealing with creation of the transition code between environments. This way of operation is tagged as 'compare & sync', meaning a mechanical comparison examines database objects in a source environment, comparing it to the target environment, and if a difference is discovered, a script to change the target object to mimic the source object is automatically created. For a while it seemed like a good solution, until the holes became more obvious.
The comparison is often performed on a database at selected check-points, usually before deployment, after the development cycle has ended:
- The compare tool is unaware of any changes that occurred before the time it ran, or any changes that took place at the target environment. We had no information about change, no version control. Just the differences at the given time.
- Keeping object scripts in a traditional version control solution while using a compare & sync tool to deploy is as un-synergistic as you can imagine. One is completely unaware of the other.
- Manual inspection and detailed knowledge regarding each change must be part of a deployment process. Otherwise, mishaps like overriding good and up-to-date updates to production (like a hot fix supplied by one team of developers), with an out-of-date code or structure from a second team that is working on something else entirely.
- Merging of code between different teams is out of the question. If you need to merge–you need to write the code manually.
Manual processes using a 'compare & sync' tool are possible, but require proficiency and patience. Trying to automate deployment processes based on these tools encompasses a substantial risk to the database.
DBAs, being both well aware of database deployment pitfalls and bearers of the scars of the most inopportune break downs, tend to shy away from automation based on the above processes, as they are not confident in the accuracy of the automation script generators or the ability for pre-prepared, manually-generated scripts to remain true any time after they were developed. In order to avoid conflicts, they often take things into their own hands. The path of carefully examining changes and manually creating change scripts as close to the deployment event as possible seems less frustrating by comparison.
In part four of the series we will look at the necessary requirements for safe, database deployment automation.