Handling Schema Drift

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

To inspect these artifacts, and others related to the release process, use the Export-DatabaseReleaseArtifact cmdlet to output the artifacts to a folder.

Set up

As the generation of drift-related artifacts happens alongside the process for producing the release artifacts, you should start seeing the artifacts being generated once the steps outlined in Automating Deployments have been followed. The one exception to this is when performing the initial deployment to a given server/database: given that the drift detection process relies on a snapshot of the source control state inserted into your database by the Use-DatabaseReleaseArtifact cmdlet, the drift artifacts will only be produced during subsequent runs of the New-DatabaseReleaseArtifact cmdlet.

Configuring which objects are included in drift reporting

As a general rule, any objects that are included in your SQL Change Automation project will also be included in drift reporting. However the following type of objects are not included in the report:

  • Static data
  • Objects that are excluded by the project's filter rules
  • Objects that exist in the target environment only, although this behavior can be overridden (see below)

Additionally, any database comparison options set within the project file will also affect the types of differences that appear in the report.

You have three main choices to configure behavior of the drift report:

A. Report drift ONLY on objects that exist in the SQL Change Automation project

By default, the DriftFiltering option in the New-DatabaseReleaseArtifact cmdlet is set to 'ModifiedObjectsOnly'. 

Under this default behavior, the drift report will not include objects that exist in the target database but do NOT exist in the SQL Change Automation project.

This accommodates scenarios in which objects have been added to the target environment as part of a separate process. A common example of this is users that exist in Production but not in Test. Another is when objects are added for purely operational reasons, such as stored procedures used by the Database Administrator to perform scheduled maintenance on the database.

Objects such as indexes, constraints, primary/foreign keys and triggers are considered sub-objects of tables by SQL Change Automation. If the parent table object itself is already in source control, any changes to these "sub-objects" will be included in the drift report under the default behavior.

B. Report drift on objects that exist in the SQL Change Automation project AND extra objects in the target database

There may be some cases when you wish to include all database objects in the drift report, whether or not the objects are included in the SQL Change Automation project. This enables you to identify objects such as tables, stored procedures, views, etc. that are added to the target database outside the release process.

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

C. Report drift ONLY on objects that exist in the SQL Change Automation Project and are modified in the current deployment

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. 

For legacy projects, you may have set the DriftOptionDropMissingObjects property in the .sqlproj file to 'true' in order to achieve the same behavior as option B above ('Report drift on objects that exist in the SQL Change Automation project AND extra objects in the target database'). Removing this setting from your .sqlproj file and managing the drift report in your deployment pipeline when you call the New-DatabaseReleaseArtifact cmdlet gives you extra flexibility, as you may easily change the configuration on your drift report at any time without needing to modify your .sqlproj file.

Viewing 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]

When generating the database release artifacts, SQL Change Automation will detect that the deployment target database is different to the version that was previously deployed. A drift report will be generated which shows the differences detected. Download this example report (Reports\Drift.html)

Note that this report is distinct from the changes report (Reports\Changes.html), which shows changes that originate from source control only.

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 ----------------------------------------
PRINT N'Dropping index [IX_SalesLTAddress_ModifiedDate] from [SalesLT].[Address]'
DROP INDEX [IX_SalesLTAddress_ModifiedDate] ON [SalesLT].[Address]

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

This is the default behavior of the script.

However, for cases where you wish to leave the drift in place, another option is available.

Option 2: Incorporate drift into the release

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]'
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)


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.

Although the drift import script contains idempotent logic, additional defensive logic could need to be authored within the new migration (for example, a server/database name check) in order to ensure that the changes are only applied to the appropriate environments.

Didn't find what you were looking for?