Dan Nolan

22 April 2013

• SQL Change Automation

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.