Switching branches using drift correction - Redgate Software

Last month I wrote about how drift correction can be used to keep track of changes that have been made outside of your established change management process.

In that post I used the example of a DBA making a change directly to the Production environment. In this post we will look at how to re-sync your database to a different source control branch using the same approach.

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

This will typically occur if changes are made outside of source control, but can also happen if you modify source control itself by removing or otherwise changing the project migration scripts.

By switching the branch/tag underlying your build configuration (eg. within your Continuous Integration server), you can generate a script to synchronize from another tag/snapshot/branch in source control, effectively “correcting” the drift.

Source_Control_Baseline_with_Rollback_kohfxg

In the above example, the decision was made to roll back the Version 1.2 schema to the state defined by Version 1.1: the source control head was repositioned to the Version 1.1 tag, causing drift between the currently-deployed baseline (v1.2) and the canonical baseline (v1.1).

Changes supported

At the moment, the following types of changes can be synchronized using drift correction:

  • Deploy earlier versions of table-bound objects such as foreign keys, indexes, column constraints, etc.
  • Deploy earlier versions of programmable objects, such as stored procedures, views, triggers, etc.
  • Deploy earlier versions of permission schemes, including users, roles and memberships, etc.

Limitations

Because drift correction uses a simple “compare source to destination” method to generate the sync script, there are a number of drawbacks:

  • It cannot revert complex changes, like table renames or columns splits.
  • By default, it will not allow actions that may result in data loss, such as reducing the precision/width of columns or removing of columns (ie. if the column was increased in size or added in the release).
  • By default, it will not allow NOT NULL constraints to be re-introduced, because this requires a full copy operation to be performed on the table.

The latter two restrictions can be lifted by specifying DriftOptionBlockDataLoss=False, however for obvious reasons, this is not recommended for Production environments.

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 Blog

How to document multiple SQL Server databases using SQL Doc and PowerShell

You can use SQL Doc's command-line parameters to automate database documentation, but when you try to automate the process of documenting a group of databases on a server, they sometimes don't give yo...

Also in Database DevOps & DLM

How Redgate helped define a truly automated database deployment process

At the end of 2016, we interviewed Bob Walker, a Lead Application Developer, to find out how he went about setting up automated database deployments using Redgate tools and what lessons he’d learned...

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