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 ReadyRoll 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, ReadyRoll 1.5 introduces 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).

Source_Control_Baseline_with_Drift_yaoxow

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:

2013_04_22_drift_correction_2_m5n9ty

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

2013_04_22_drift_correction_1_xhqeqi

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.

2013_04_19_octopus_deploy_preview_2_bzdwrh

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

Get started

Drift correction is now available as part of ReadyRoll 1.5.

Tools in this post

ReadyRoll

Develop and deploy databases in Visual Studio with migration scripts.

Find out more

Share this post.

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

Related posts

Also in Hub

SQL Prompt Hidden Gem: Auto-fill the GROUP BY clause

"Have you seen this new feature that auto-fills the GROUP BY with non-aggregated columns!" exclaimed my co-worker, soon after she had installed a new version of SSMS. I hadn't, but I was intrigued. I ...

Also in Product learning

Generating test data in JSON files using SQL Data Generator

SQL Data Generator is adept at filling SQL Server databases with 'spoof' data, for use during development and testing activities. However, what if instead of a SQL Server database full of fake data, y...

Also in ReadyRoll

How to import an existing database to ReadyRoll

The SQL Toolbelt includes ReadyRoll, which allows you to adopt a migrations-first approach to database source control and deployment. There are a number of different ways teams can get started with Re...

Also about ReadyRoll

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