Automatic approvals

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:

1. Generate the what-if report using the database deployment tooling. Save the report to a shared location for easier access.
2. Run a script to:
   1. Open up the what-if report.
   2. Loop through a list of schema change commands, such as Drop Table, Create Table, Drop Column, Alter Table, Drop User.
   3. If a schema change command is found set a DBA Approval Required output variable to True.
   4. If no schema change command is found set the same DBA Approval Required output variable to False.
3. Notify the approvers when that DBA Approval Required output variable is True using run conditions.
4. Pause for a manual intervention when that DBA Approval Required output variable is True using run conditions.
5. Deploy database changes.
6. 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:

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.

Send feedback

Page updated on Sunday, January 1, 2023