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.

Share this post.

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

Related posts

Also in Blog

Five ways we’ve implemented Agile marketing at Redgate

Implementing Agile working practices reminds me of Communism – if we just did it properly, everything would work perfectly! All projects would be a dream to work on, all deliveries would be early, t...

Also in Database DevOps & DLM

Announcing Redgate Data Tools in Visual Studio 2017

Visual Studio 2017 is released today, and its new features like Live Unit Testing and configurable code style rules have already been grabbing the headlines. But inside the Visual Studio 2017 installe...

Also about ReadyRoll

Solving Visual Studio database delivery problems with ReadyRoll

For .NET developers working with Visual Studio (VS), the introduction of Database Projects with SQL Server Data Tools (SSDT) brought to VS the ability to manage changes to the database schema and cod...