Kendra Little

13 August 2020

Inside a DevOps Deployment: How Change and Drift Reports Support Code Review

Source controlling database code and automating deployments is a tricky business. To work quickly and maintain control over changes, developers need both productivity tooling to help generate code quickly, but also the ability to modify the code and control it — in case either they’d like the deployment to be performed in another way, or perhaps they’d like to add code to perform data modifications.

You need both detail and an overview to review database changes

Let’s say that the change I’m deploying is adding a column to an existing table. When doing code review, it’s useful for everyone to see the code that’s being run:

ALTER TABLE [dbo].[Employees] ADD[boo] [nvarchar] (128) NULL

But there’s a lot which this code sample doesn’t tell us. What other columns exist in the table? Are there potentially other commands in this batch of change scripts which are also modifying that table, and what is their combined result?

In other words, what does the “before” and after” look like for the entire deployment?

Redgate has extra automation for static code analysis and change review support

Redgate’s SQL Change Automation was designed to support easy change review. When users generate a Release Artifact, the artifact contains not only the script which will be deployed to the target database, but also human-consumable change reports and drift reports which contain:

• A visual diff showing the “before” and “after” of objects modified in the change
• Static code analysis run against the deployment code
• A drift report showing any modifications which have occurred to the production database since the last deployment, which have not been “circled back” and included in the automated deployment pipeline

How does this work?

Because Redgate allows users to fully control and tweak the changes which are going to be executed in the deployment, making this work takes a bit of cleverness to get done on the back end!

Changes Report

Some background info: as with most DevOps pipeline workflows, Redgate builds changes first, and outputs a build artifact. But not all builds are necessarily deployed to production — many builds may be done for periodic validation as a change is being designed, tested, and reviewed in a Continuous Integration process. When build artifacts are used to make a deployment, Redgate uses the build artifact to create a release artifact which contains the necessary code to perform the deployment to the target database, along with information for human review. Here’s how that change information is generated:

1. We create a schema snapshot in the build artifact which represents the desired state of the database after changes are deployed.
2. When creating a release artifact, we can compare the snapshot from the build with a fresh snapshot of the target database. This yields the summary of the overall changes effected by all of the steps of the deployment.
3. We also display the exact SQL statements which are going to be deployed, as it’s important to review those as well.

Drift report

We record a schema snapshot for a database at the end of each deployment you make. When creating a release artifact, this empowers us to use that same quick snapshot of the current production schema mentioned above to do a comparison of what the database’s schema looks like right now with what it looked like before.

Voila, a drift report!

What about filters?

SQL Change Automation allows you to set filters to exclude any parts of a database schema which aren’t part of your development project.

These filters are applied in all of the snapshots reference above, so you won’t be distracted by information that isn’t relevant to your work.

And that’s the look inside our deployment engine!

I hope you enjoyed this little view into the “behind the scenes” of the types of activities that help bring together a comprehensive versioning and automation engine for Database DevOps.

A big thanks to Ivo Miller for his help in sharing how this works.