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.
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).
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 NULLconstraints 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.
Also in Hub
While the database is sometimes seen as the blocker in the development process, it doesn’t have to be. With the right knowledge and tools, you can stop problems before they occur, help to speed up d...
Also in Product learning
From provisioning databases through to version control, automated deployments and monitoring, Redgate has a tool for every part of the database development process.
You probably have a favored tool b...
Also in ReadyRoll
For .NET developers working with Visual Studio (VS), the introduction of Database Projects with SQL Server Data Tools (SSDT) brought to VS the ability to manage changes to the database schema and cod...
Also about ReadyRoll
The recent State of Database DevOps Report revealed that within two years, 80% of companies will adopt DevOps. That’s an interesting finding in itself, but the report also showed that 75% of compani...