Redgate Test Data Manager

Improve your release quality and reduce your risk, with the flexibility to fit your workflow

Flyway

Automate database deployments across teams and technologies

Redgate Monitor

Understand server performance in an instant with fast deep-dive analysis

See all our products

Overview

Redgate’s end-to-end Database DevOps solutions

Test Data Management

Reliable test data management, automating repetition out of the process

Automate

Automate and accelerate your database change management workflow

Monitor

Monitor your cross-database, multi-platform estate to ensure optimal performance

Product articles SQL Change Automation
Sometimes the tool just fits –…

30 June 2015
30 June 2015

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:

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

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.

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

SQL Change Automation

Automate your database changes with CI and automated deployment

Find out more

SQL Source Control

Connect your databases to your source control system

Find out more

SQL Change Automation

Automate your database changes with CI and automated deployment

Find out more

You may also like

  •

    Article

    Database Build Blockers: Mutually Dependent Databases

    Phil Factor demonstrates a clever way to create 'stub' objects, in SQL Change Automation pre-deployment scripts, in order to overcome the problems caused by 'missing objects' when building databases that have circular, or mutual, dependencies. In the subsequent deployment, SQL Change Automation fully builds every object in each of the databases, so all dependencies are fully tested.

  • Article

    Database Delivery with Docker and SQL Change Automation

    Phil Factor demonstrates how to integrate SQL Change Automation into containerized workflows, such as are typical of a microservices architecture. He shows how to automate database builds into a Linux SQL Server container running on Windows, and then backup the containerized database and restore it into dedicated containerized development copies for each developer and tester.

  •

    Article

    Recreating Databases from Scratch with SQL Change Automation

    Phil Factor starts with the basics how to rebuild a set of development database from scratch, using SQL Change Automation, and then demonstrates how to check for any active sessions before rebuilding, import test data using BCP, and secure passwords if connecting to the target with SQL Server credentials.

  •

    Article

    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't. But one particularly common culprit is when deployments go wrong: I don't know a single DBA who hasn't been burned by a bad release.

  •

    Forums

    SQL Change Automation Forum

    Continuous integration and automated deployments for your SQL Server database

Redgate Hub