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 unproblematic – 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.
SQL Source Control includes an option to register any given table as a static data table, bringing it under the same control as your schema and database code.
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.
Starting with release 22.214.171.1242 of the DLM 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 on drift protection across your environments, have a look at DLM Dashboard).
What’s more, if you’re using Octopus Deploy with Redgate DLM, 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.
Also in DLM Automation
Sudden performance issues in SQL Server can have many causes, ranging all the way from malfunctioning hardware, through to simple misconfiguration, or perhaps just end users doing things they shouldn'...
Also in Hub
Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to...
Also in Product learning
Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for p...
Also in SQL Source Control
SQL Server 2017 went on general release a couple of weeks ago and the latest version of SQL Source Control offers full support, as well as support for Team Foundation Server (TFS) 2018.
This brings ...
Also about automated deployment
If you’re struggling to set up a reliable, repeatable release process you’re not alone. The good news is that most of the problems you’ll encounter have been solved before.
There are many smart...
Also about DLM Automation
While the practice of Continuous Integration (CI) started with application code, you can apply the same principles to databases. Database CI is the process by which we build, test and deploy the datab...
Also about SQL Source Control
The recent State of Database DevOps Report revealed that within two years, 80% of companies will adopt DevOps. That’s an interesting finding in itself, but the report also showed that 75% of compani...