on June 4, 2021
One big gotcha that teams often encounter when automating deployments for databases is that it’s difficult– or sometimes impossible – to ensure that all changes to the production database are performed through the automation pipeline.
These out-of-band changes case the production database to “drift” away from the schema as defined in version control.
Why does this happen, and what do you do when your production database gets modified outside of your automated deployment pipeline?
How production drift happens
I find that there are three common causes of production database drift.
Process lag: Production drift is especially common when process changes are occurring and the automated deployment process is newly established. In this case, not everyone is quite on board with the new deployment procedure and changes continue to flow into production via older processes.
“Oops!": Another common cause of production drift is accidental deployments. It’s pretty darn easy to deploy something to the wrong database. Sometimes the person who does this realizes their mistake, and sometimes they don’t.
“Whatever it takes” requirements: Some business critical databases rely on immediate remediation for certain types of issues to be performed by subject matter experts as soon as possible, and core stakeholders do not even wish to wait for a pipeline to execute to deploy corrective changes once they have been identified. An example of this could be an index being created to remediate a critical performance problem that unexpectedly appeared. (Note: many teams find ways to optimize their pipelines so emergency changes may be deployed incredibly quickly, but this varies.)
Automatically reverting drift is dangerous
Some folks like the idea of wiping out any drift when the next automated deployment occurs.
This can be done using “state” based deployments for SQL Server – one can do this when deploying Redgate’s SQL Source Control projects using SQL Compare (or an automated pipeline), or when deploying Microsoft’s Database Projects (SSDT). With this approach, code is automatically generated to apply the schema in the project to the target database. If anything has changed in the target database since the last deployment and it wasn’t incorporated into the schema in the project, it automatically be eliminated.
But automatically reverting drift is risky.
You can cause a performance regression - If an emergency fix was put into place for a critical performance problem, that problem is very likely to come back if we automatically revert the fix.
You probably didn’t test this change - No automatically generated code is guaranteed to be 100% perfect 100% of the time. If changes were made to production and not to other environments and you automatically generate and run code to revert it – that code probably wasn’t tested anywhere else. Do you really want that code to run?
You can lose data - In some cases, the drift might have done something like create a new column or table and persist some data there. Is it really safe to drop that column or table? We won’t know unless we take time to investigate.
Generally, we want to either prevent drift, or we want to be alerted to the drift and to investigate it.
Option 1: Alert when drift occurs
One of the best things that you can do is to monitor for database drift and alert when it happens. Establish a procedure to follow up on the alert:
- Revert the change
- Incorporate the change into your project retroactively
Grant Fritchey wrote up some sample code for monitoring for and alerting on drift in SQL Monitor. This approach uses an Extended Events Trace and SQL Monitor’s Custom Alert functionality.
If you’re using a different monitoring solution, it’s absolutely worth investigating if you can implement monitoring for database drift.
Option 2: Automatically incorporate drift into your development environment
Another option is to regularly check for production drift and proactively pull it into your development environment. Ideally, this should be an automated solution or you are likely to miss the drift.
If you have a schema snapshot tool, this can be done by automating schema snapshots of the production environment and copying the snapshots to your development environments. Then run a comparison against your development database or project to check for drift.
For Redgate SQL Compare users, there’s even a tool called “SQL Snapper” that is designed to help make taking these snapshots more easily.
For others, Microsoft provides the ability to extract the schema from a database with SqlPackage.exe.
Option 3: Prevent drift altogether
One very popular use of DDL Triggers in SQL Server is to prevent accidental or out-of-process database changes.
These are a bit like a child-safety lock – you need to be able to disable the trigger in order to make schema changes. This means that it’s always possible for an adminsitrator to disable the triggers and make a change.
That’s generally fine, the cause of drift is often ignorance, forgetfulness, or accident rather than malicious users. But it does mean that this approach pairs well with also adding an alert to monitoring to check for drift, too.