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