Sometimes the tool just fits – using SQL Change Automation and Octopus Deploy for Data Change Control
From a business risk perspective, data change can be just as significant as code or schema change. Sometimes even more so; an incorrect static (or reference, or master) data change can drive your software’s behaviour more dangerously askew than pretty much any bug can.
Imagine treating a retail customer for an investment fund as a corporate by mistake, or vice versa (a potential regulatory breach). Or booking the repatriation trades in the wrong currency (and not finding out until they settle, and hit the wrong cash balance). And what are your web customers going to do when the ‘choose credit card type’ drop-down is empty?
There are of course whole businesses and a whole software category dedicated to master data management (MDM). If you’re a customer of a big MDM vendor and have data governance and data change control locked down and causing no concerns – good for you. But many firms find that they have to build their own workflows.
If you’re using the Redgate stack for database version control and database automation, and also use Octopus Deploy for release management, you might just find that you have a perfectly serviceable option at your fingertips, using software you already own.
Whether you’re using the SQL Change Automation extension, in Visual Studio, or the SQL Source Control extension in SQL Server Management Studio, you have the option to register any given table as a static data table, bringing it under the same control as your schema and database code. This example uses SQL Source Control:
When you commit change and build your database as part of your continuous integration (CI) process, the deployable package includes the committed state of your static data.
With SQL Change Automation product, when this package is prepared for deployment against any given target, you are given a clear GitHub-style diff report for review before any change is applied.
This review file (changes.html) is associated with this deployment against this target, and no other. If the target database is changed (schema, code or static data) before the change is applied, the deployment is invalidated and will not be applied, regardless of approval. (For more information on drift protection across your environments, have a look at the article, Dealing with production database drift).
What’s more, if you’re using Octopus Deploy with SQL Change Automation, just adding a review step to the deployment process creates a durable association between the change being approved, the approver’s comments, and the deployment itself.
So don’t just keep these tools within the dev team; invite Operations, Risk, Compliance, Legal – anyone who has to approve change – to the party.
Combine it with schema documentation and you have a clear picture for the business approver to understand, and a clear and durable audit trail to keep you looking good.