It is known as ‘database version drift’ or ‘database version creep’. For the best of reasons, the version of the metadata in production no longer matches what is in source control. Usually, the most serious consequences would be the loss of the changes that were made to vitalize a moribund production system, but it always means something that needs to be investigated.

Drift usually happens during the terror of a database deployment when the original deployment from source control causes problems in production, and rather than attempt a complicated ‘roll back’, it is easier to ‘roll forward’ by making a direct fix to production. It could be something as trivial as adding a vital missing index, or making a small change to a database object. In the ideal development environment, it shouldn’t happen, but then most developers don’t work in such an environment. Unexpected events can unsettle even the most orderly IT shop.

When this sort of drift is detected in a locally-developed database application, then we need to understand why these changes have happened, who did them, and decide if they need to be made permanent by retrospectively legitimizing them in source control. If they represent a legitimate ‘roll forward’ or a hurried performance tweak, the DBA merely smiles, and updates source control so that a new version is created that matches the production system. The decision must then be taken whether these changes must be in the trunk for the next version. If, on the other hand, it is a bought-in database, any changes have to be reported to the supplier.

Occasionally, you might find changes that aren’t part of a legitimate ‘roll forward’. Not all illegitimate changes are fraudulent; many well-intentioned alterations don’t follow the due process. However, we are no longer allowed to ignore this in the business setting due to obligations imposed by corporate information security policies and applicable laws and regulations concerning both privacy and financial management and reporting systems. There must be a clear audit trail for any production database changes.

There are many ways of detecting changes that lead to drift. They need to be automated, with alerting. The Default trace, DDL trigger, Extended Events, Event Notifications and SQL Trace, can all be used, although they all come with various challenges and ‘caveats’. One advantage of Event notifications is that, despite its relative complexity, it can be extended to multiple servers and databases.

Recently our team at Red Gate have been working hard on a tool called DLM Dashboard, the idea being to provide a simple, nonintrusive system that combines change-detection with alerting and difference-reporting. It’s a free tool, currently in beta. Ultimately, we hope it will mean that there is no excuse for not having a system in place to detect drift!

We’d love to hear what challenges you face with drift, in your deployment environments. We also want our users to help shape what else this tool should do, so we’d appreciate any feedback on how the tool works currently, and on the essential processes for proper drift detection and correction. Then hopefully in the next release, we’ll be able to include in the tool a nice, red UNDO button and green LEGITIMIZE button!