We recommend including DBAs in the automated database deployment process. If something goes wrong in Production
at 1 AM, they are the ones who are paged. The manual approvals documentation walks through how to include DBAs in the deployment process in Octopus Deploy. The concern with manual approvals is scalability. It is common for us to see a 15-to-1 or 20-to-1 ratio of developers to DBAs. The number of approvals a DBA is involved in will exponentially grow as the number of teams and projects automate their database deployments increases.
Schema change commands are the biggest concern. Thankfully, the SQL language defines those commands. Most database deployment tools, Flyway, DBUp, RoundhousE, Redgate, or DacPac, generate what-if or dry-run reports. It is possible to write a script that looks for specific commands, and when one is found, run a manual intervention. The format of the what-if report depends on the tool.
The general auto-approval process looks something like this:
- Generate the what-if report using the database deployment tooling. Save the report to a shared location for easier access.
- Run a script to:
- Open up the what-if report.
- Loop through a list of schema change commands, such as
Drop Table
,Create Table
,Drop Column
,Alter Table
,Drop User
. - If a schema change command is found set a DBA Approval Required output variable to
True
. - If no schema change command is found set the same DBA Approval Required output variable to
False
.
- Notify the approvers when that DBA Approval Required output variable is
True
using run conditions. - Pause for a manual intervention when that DBA Approval Required output variable is
True
using run conditions. - Deploy database changes.
- Send notifications on the status of deployments.
Output variables and run conditions
We recommend creating a variable in the project to reference the output variable from the auto-approval step. A variable referencing the output variable makes it easier to change if the auto-approval step names change.
For instance, #{Octopus.Action[Auto-Approve Delta Report].Output.DBAApprovalRequired}
.
Creating a variable also makes it much easier to use in a run condition:
We recommend setting the output variable to True
or False
because that is what the run conditions look for. If you need an if/then statement, then in include it in the auto-approval script.
Logging
We recommend the auto-approval step write logs using Write-Host
for PowerShell or echo
for Bash scripts. That output is captured by Octopus Deploy and can be viewed in the Task Log
tab on the deployment screen. When we’ve debugging scripts, the more logging, the better.
For important logs, such as when a command is found, leverage the write highlight command. That is a custom command Octopus Deploy injects into the deployment process. Using that command will show the message on the task summary screen.
Example
View a working example on our samples instance.
Help us continuously improve
Please let us know if you have any feedback about this page.
Page updated on Sunday, January 1, 2023