22 April 2013
22 April 2013

Staying In-sync with drift correction

Getting everyone in a team behind a process change is hard.

As a database developer, not only do you need to champion the new process within your own team, but you also need to extend the olive branch to your DBA to ensure everyone is on-board with the changes.

This is where you may encounter ‘challenges’, as SQL Change Automation is designed primarily with a developer workflow in mind:

Integrated Dev Environment → Source Control → Continuous Integration

Some DBAs may not be used to working in this way.

If your DBA currently prefers to make changes directly on Production (eg. for index additions, filegroup adjustments, permissions changes, etc), you’ll need to work together closely to ensure that his/her changes also get included in source control.

Failure to do so could result in deployment issues down the line, or even cause your DBA’s changes to be (quietly) undone.

To help you keep the change management loop closed, SQL Change Automation supports drift correction: a feature that allows you to keep track of changes made outside of your established process.

What is “drift”?

Drift occurs when a target database deviates from the baseline that was used to originally deploy it (eg. the database project within your repository’s trunk).


In this example, modifications were made to the live database after Version 1.2 was released, eg. using SQL Management Studio. This effectively created a new version of the schema outside of source control.

This could cause a problem when Version 1.3 is deployed, because the deployment package (migrations) will expect the target database to be in the state defined by the canonical (Version 1.2) baseline.

How it works

During a Continuous Integration build, a comparison is performed between the target database and the appropriate source control baseline. This process generates:

  • A drift report, eg. AdventureWorks_ReSync.html
    Containing details of objects that have drifted from the source control baseline
  • A drift correction script, eg. AdventureWorks_ReSync.sql
    If executed, will re-synchronize the target db with source control, undoing changes made directly to Production

These artifacts are created within the bin\Release\ sub-folder of your database project.

Viewing the drift report

In the following example, an index has been added to an existing table in Production:

During build, a drift report is generated because one or more object changes have been detected:


Further down, we can see that the index is missing from source control:


Note that this report is distinct from the deployment preview report, which shows changes that originate from source control only.

Viewing the drift correction script

The drift correction script reflects what is displayed in the report: that the object was not found in source control, therefore it should be removed from the target database:

This is the default behaviour of drift correction. However, another option is available.

Further down in the script is a (commented out) query batch that performs the opposite change of the above:

Instead of dropping the index, this batch can be used to preserve the changes.

This is done by pasting the code into a new migration within your database project and committing the change to source control.

After the above script is deployed to Production, a further build should indicate that the drift has been resolved.

Set up

Configuring of drift correction happens within the build parameters section of your Continuous Integration server.


Specify these MSBuild properties in your build configuration to enable drift analysis:

  • TargetServer & TargetDatabase: the SQL Server instance name & database name of the target environment (typically Production). Read-only and view-definition access is required.
  • ShadowServer: a test SQL Server instance name. This will be used by SQL Change Automation to create a version of your database used as the basis of drift analysis. Sysadmin (database create/backup/drop) level access is required.

There is one other (optional) property that you can specify:

  • DBReSyncOnBuild: If True, the drift-correction script will be executed after a successful build is performed, undoing any changes made in your target database. However, unless performing drift correction against a test environment, we strongly recommend specifying False (default) to give you an opportunity to review the script before executing it.

Other uses: branch resynchronization

Drift correction can also be used to help switch between branches in your source control system, eg. to assist with rolling-back changes made during a deployment.


Tools in this post

SQL Change Automation

Continuous integration and automated deployments for your SQL Server database.

Find out more

Share this post.

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

You may also like

  • Article

    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

  • Article

    Simple SQL Change Automation Scripting: The Release Object

    Once you understand the SCA data objects, it can give you a certain glow of to discover, suddenly, that SCA can do some complicated and time-consuming tasks with just a few lines of code. Phil Factor demonstrates how to get the most of SCA's Release object.

  • Article

    Deploying cross-database dependencies to different environments

    The SQL Change Automation team here at Redgate occasionally take time out from development to explore some of the issues our customers face when automating deployment of database changes.  As part of one such exercise, we took a closer look at cross-database and cross-server dependencies – these can cause problems when deploying databases to multiple environments

  • Webinar

    Extending DevOps practices to SQL Server databases with Redgate - Using TFS work items

    Microsoft Data Platform MVP, Steve Jones, and Redgate's Arneh Eskandari, will show you how Redgate’s Database DevOps solution works with TFS work items.

  • University

    Take the SQL Toolbelt course

    SQL Toolbelt includes all of the components that enable Database DevOps. This step-by-step guide takes you through the process right from being able to analyze the impact of database changes through to making those changes, source controlling them, deploying them out to your target environments, and finally monitoring and documenting those environments.

    Here you will see an overview of each tool and how it can benefit your organization, but be sure to check in the module description whether there is a full course available for the tool you are learning.

  • Forums

    SQL Change Automation Forum

    Continuous integration and automated deployments for your SQL Server database