Prefer a video to reading? Check out this YouTube video overview of Drift Reports in SQL Change Automation.

What is 'Schema Drift'?

Schema Drift occurs when a target database deviates from the codebase that was used to originally deploy it.

In this example, modifications were made to the live database after Version 1.2 was released. This effectively created a new version of the schema outside source control.

This could cause a problem when Version 1.3 is deployed, because the deployment package will expect the target database to be in the state of Version 1.2.

How it works

When the New-DatabaseReleaseArtifact  cmdlet is used, a comparison is performed between the schema of the target database and a representation of the schema that was stored in the __SchemaSnapshot table in the target following the last successful deployment. This process produces the following artifacts:

• Reports\Drift.html 
  A report containing details of objects that have drifted since the last successful deployment

• DriftRevertScript.sql 
  If executed, the script will re-synchronize the target db with source control, undoing changes made directly to the database

There may be some cases when you wish to have the drift report ONLY include drift on objects which will be modified in the current deployment.

This accommodates scenarios in which a project includes a large number of objects, and a variety of different teams are collaborating on shared environments in the pipeline. In these scenarios, teams are concerned only with drift on the objects which they are modifying in their current work. Drift that has occurred on other items in the project, or additional items which have been added to the target database will be identified and handled by an external  process. 

To enable this behavior, set the DriftFiltering option to 'ObjectsToBeUpdatedOnly' when you call the  New-DatabaseReleaseArtifact cmdlet.

With this option, it is important to understand that the drift report will NOT include drift on any dependent objects. In other words, if you are modifying a view and drift has occurred on a table referred to by the view, that table will not be surfaced in the drift report. 

In the following example, an index has been added to an existing table in the live production database. 

USE [adventureworks]
CREATE NONCLUSTERED INDEX IX_SalesLTAddress_ModifiedDate ON SalesLT.[Address]
  ( ModifiedDate DESC ) ON [PRIMARY]
GO

Resolving instances of drift

The first step to resolve drift is to make a decision about whether the drift should be reverted – in other words, if the target environment should simply be brought back in line with source control – or whether the drift should be left in place and the change representing the drift should be imported into source control.

Because a team my make quite different choices depending on the nature of the drift and the use case for the target environment, resolving drift is a task that currently must be handled manually.

The drift detection process provides a script which can be used as a starting point for resolving the drift in the target environment.

Option 1: Revert drift from the database

To allow the deployment target back in line with the previously deployed version in source control, SQL Change Automation generates (but does not run) a drift revert script.

In this example, the script reflects what is displayed in the report: that the index object was not found in source control, therefore it should be removed from the target database. Download this drift revert script (DriftRevertScript.sql). 

You can execute this script using the Use-DatabaseReleaseArtifact  cmdlet, by setting the DriftHandling parameter to a value other than Ignore.

--------------------------------------- BEGIN DRIFT CORRECTION (GENERATED) SCRIPT ----------------------------------------
GO
PRINT N'Dropping index [IX_SalesLTAddress_ModifiedDate] from [SalesLT].[Address]'
GO
DROP INDEX [IX_SalesLTAddress_ModifiedDate] ON [SalesLT].[Address]
GO

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

Instead of dropping the index, the drift can resolved by manually importing the changes into source control as an idempotent migration. During subsequent releases, the index will be propagated to any environments where it does not currently exist.

SQL Change Automation assists with this by producing a separate script that performs the opposite change of the drift revert script. This can be found within the commented-out section of the script footer:

------------------------------------------------------------------------------------------------------------------------
------------------------------------------      NEW INCREMENTAL MIGRATION     ------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-- DESCRIPTION: The following code performs the opposite of the above statements: it preserves the changes
--              made to the target database instead of reverting them.
-- HOW TO USE:  Paste the following into a new migration to import changes.
/*
PRINT N'Creating index [IX_SalesLTAddress_ModifiedDate] on [SalesLT].[Address]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SalesLTAddress_ModifiedDate' AND object_id = OBJECT_ID(N'[SalesLT].[Address]'))
CREATE NONCLUSTERED INDEX [IX_SalesLTAddress_ModifiedDate] ON [SalesLT].[Address] ([ModifiedDate] DESC)
GO

*/

To incorporate the change into source control, copy and paste the section of code into a new migration within your SQL Change Automation project, commit the change back to source control and perform a new build.

When the new build is released, the changes will be included within the script that is deployed onto your development, test and production environments (TargetedDeploymentScript.sql). Once the script has been deployed to all environments, a new set of release artifacts will need to be generated to demonstrate that the drift has been resolved.