30 June 2015
30 June 2015

Sometimes the tool just fits – using Redgate DLM 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 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.

SoC register static data
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.

SoC commit static data

Starting with release 1.2.0.2532 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).

Data change diff report

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.

Data change approval step

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.

Tools in this post

DLM Automation

Continuous integration and automated deployments for your SQL Server database.

Find out more

SQL Source Control

Connect your databases to your source control system.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in DLM Automation

Unearthing Bad Deployments with SQL Monitor and Redgate's Database DevOps Tools

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

Misuse of the scalar user-defined function as a constant (PE017)

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

Customizing the SQL Prompt built-in snippets: a better 'ata' snippet

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 Source Control v6 now supports SQL Server 2017, SQL Graph, and TFS 2018

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

5 tips for achieving continuous delivery

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

Database CI with Redgate DLM Automation, PowerShell and Jenkins

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

Moving from application automation to true DevOps by including the database

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...