Grant Fritchey

19 April 2021

Manual Reviews Can Be a Part of Your Automated Deployments

The ultimate goal for automating deployments can be the hands-free deployment of all code, including the database, from development to production. However, for many of us, the complete automation of all deployments with zero manual intervention will always remain a goal. We may have systems that are simply too complex for a 100% automated deployment. We might even have regulatory or business requirements that we implement manual steps, especially a review or approval step. These requirements will keep us from a perfectly automated system. However, this is actually not a problem and is easily implemented.

There are only really two things you need to incorporate a review or approval step into your automated processes: an artifact and the ability to pause the automated processes. We’ll start by discussing artifacts.

Artifacts

An artifact for our purposes is represented by nothing more complex than a script, or a report, showing what changes would be made, if we let the automated tools do their jobs. Talking about Redgate Deploy or Flyway, both tools can be set up and controlled in ways that automate the execution of changes directly on your databases, without generating a script of those changes. However, both tools will allow you to generate artifacts, scripts, which will show exactly what a given deployment would be about. Let’s explore them one at a time.

Flyway

Flyway offers a couple of different ways to generate an artifact. One method is to use the Dry Run functionality. Basically, if you define a value for DryRunOutput within your configuration file, or as part of the command line call for Migrate, instead of deploying changes, Flyway will generate a SQL script showing what changes it would have deployed. This is a great way to get a script that you can then use for review, in order to understand exactly what changes would have been done to the database in question. You even have the option of simply using this script to perform deployments if you so choose.

Another option in Flyway is to run the command Info instead of Migrate. The Info command will then show you a listing of all your scripts, which ones have already been deployed, which need to be deployed, and whether or not the individual script can be rolled back. All of this is output to a grid, or, can be output to a JSON file. Since this is just a listing of scripts, and not the SQL like when using Dry Run, you’d be forced to look at each individual script in order to know what changes are incorporated there.

Using either of these methods, you have the ability to create an artifact that is used for your review and approval process.

Redgate Deploy

Redgate Deploy also has several different ways to generate an artifact. In this case, it depends on how you’re using Redgate Deploy and what your target system is. Let’s start with targeting SQL Server systems and then we’ll cover Oracle.

If you were to use SQL Change Automation to deploy your SQL Server changes, then there’s a couple of commands built into the PowerShell command line that you need for generating artifacts: New-DatabaseBuildArtifact and Export-DatabaseBuildArtifact. The first command, New-DatabaseBuildArtifact, generates a T-SQL script based on the output from a database build. You do have to complete the build process first, because it validates and generates the changes to be deployed. However, once you create an artifact, you can make that artifact available for review through the Export-DatabaseBuildArtifact command. As with Flyway, you can even use the artifact for the deployment after you review it.

If you are working with Source Control for Oracle, then set up a Redgate Change Control project and target that for your development source. In Redgate Change Control, you have two tabs that you’re going to use through the GUI, Apply To Database or Generate Migrations. For our purposes, we want to use the Generate Migrations in order to simply put together a PL/SQL script of the changes we want to deploy. As with the other tools, you can review this script, and if you like deploy that script. The real strength comes by using the command lines to control the mechanisms. The Build command of Redgate SQL Change Automation will generate an artifact. You simply supply a path and location for the -output and you’ll have a PL/SQL script.

Flow Control

Now that you have a method for generating an artifact, regardless of the database system we’re working on, we need to ensure that we can pause our deployments for a review and/or approval. The good news is, all the different flow control tools I’ve worked with, from Azure DevOps, to AWS Developer Tools, to Octopus and Jenkins, all of them have the ability to create manual review steps. All you have to do is use the appropriate tool to generate an artifact. Then, expose that artifact through your flow control system. It’s that simple.

Conclusion

While I advocate for automating as much as you possibly can, the simple fact is, manual reviews are going to be necessary. Happily, the tools are already oriented to support exactly this kind of manual step.