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, use drift correction 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:

USE [AdventureWorks_PRODUCTION]
CREATE NONCLUSTERED INDEX IX_Person_ModifiedDate ON Person.Person
  ( ModifiedDate DESC ) ON [PRIMARY]
GO

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:

-------------------------- BEGIN DRIFT CORRECTION (GENERATED) SCRIPT --------------------------
GO

Print 'Drop Index IX_Person_ModifiedDate from [Person].[Person]'
GO
DROP INDEX [IX_Person_ModifiedDate] ON [Person].[Person]
GO

-------------------------- END DRIFT CORRECTION (GENERATED) SCRIPT ----------------------------

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

----------------------------------------------------------------------------------------
--------------------------       NEW MIGRATION SCRIPT       --------------------------
----------------------------------------------------------------------------------------
/*
-- <Migration ID="e5910f2f-c691-44bd-828b-205e4481ac34" />

Print 'Create Index IX_Person_ModifiedDate on [Person].[Person]'
GO
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[Person].[Person]') 
  AND [type]='U')) AND NOT (EXISTS (SELECT * FROM sys.indexes WHERE [name]=N'IX_Person_ModifiedDate' 
  AND [object_id]=OBJECT_ID(N'[Person].[Person]')))
  
      CREATE NONCLUSTERED INDEX [IX_Person_ModifiedDate]
          ON [Person].[Person] ([ModifiedDate] DESC)
          ON [PRIMARY]
GO

*/

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