Dealing with production database drift

The Redgate DLM Automation team that we’re part of recently took time out from development to explore some of the issues our customers face when automating database deployment. Following on from last week’s post about cross-database and cross-server dependencies, this blog shares some of our thoughts about how to deal with production database drift.

If you’re using automated deployment for your databases, direct changes to your production environment can cause a headache. These unmanaged changes are known as database drift.

To better understand the issues this can cause, our team created a test application we could work with; this was a system for a fictional company called Lightning Works. We used DLM Automation and Octopus Deploy to deploy two databases, a website, and associated services. The two databases were our Foreign Exchange database, Forex, and the LightningWorks database used to hold the application data.

The problem with production database drift occurred when someone added an index directly to a production database. I was making some changes to the GetLatestExchangeRate stored procedure in the Forex database. I checked my changes into SQL Source Control and then deployed them to the Quality Assurance (QA) environment. Using DLM Automation, the QA database is made to look exactly like the database in source control. This is done to ensure full knowledge of what the database will look like after deployment. The deployment to QA was successful.

I then tried to deploy to our production environment. Because we use DLM Automation in our deployment process, I could review the script that would run on the production database if I continued with the deployment. But when I looked at the script, I noticed there was a change to a table that I hadn’t touched. Someone had added an index to the ExchangeRate table in the production database.

I wanted to keep this change because querying had become slow and adding an index had improved performance. But because this index was not checked into source control, it would be dropped as part of my deployment.

The DLM Automation change report showed that the ExchangeRate table had been modified, even though I had only made a change to the GetLatestExchangeRate stored procedure:

databasedrift1

Expanding the ExchangeRate table modification in the report showed the index being deleted as part of my deployment. The relevant sections of the change script are prefixed with minus signs and highlighted in red:

DatabaseDrift2

So I asked around my team to find out who had made this change and discovered it was Peter. We got together to try to solve the problem. We quickly realised that we needed to add the new index into source control. To do this, we grabbed the script that Peter had run on the production database, and executed it on my development database. This was checked into source control and then deployed to both the QA and production environments. This time when I deployed, the index was left alone. We can see this in the change report, which no longer has the ExchangeRate table marked as being modified:

databasedrift3

This solved our problem. But this process of looking out for production drift and then getting the change into source control was error prone and inefficient. How could we stop this from happening again? We came up with two ideas:

  • Install DLM Dashboard to monitor any changes to our production database
  • Lock down permissions so only DLM Automation is allowed to make changes to the production database

Having DLM Dashboard installed would have been useful because it would have notified the team as soon as Peter had made his change to the production database. This would have prompted me to sort out the problem before I even tried to make a database deployment. For example, the production drift would have been flagged up on the DLM Dashboard homepage:

DatabaseDrift4

By clicking the Review button, we could have reviewed this change in the associated drift report:

DatabaseDrift5

So DLM Dashboard would have helped us to spot this problem. But it wouldn’t stop people making direct changes to the production database in the first place. In order to do this, I wanted to lock down permissions.

However, Peter wasn’t convinced initially. He felt that being able to make changes directly to the production database was useful. For example, in this case the performance issue was only on the production database. However, I showed him that he could reproduce the problem and fix it on his development database. This would then be checked into source control and deployed to the production environment as part of the automated process.

Eventually we agreed this was a better solution for us, and locked down the permissions. This would make sure that all changes first go through testing and QA, and only then to the production environment.

To achieve this, we created a DLM Automation Windows user. Our Windows user was associated with a corresponding SQL Server user on the production server and databases. Only this user could modify the schema. Now, when Peter, or anyone else, wants to make a change to the production database, it must be checked into source control and go through the standard deployment procedure.

All in all, locking down the permissions on your production server would seem to be the best way to go. This prevents direct changes to your production database. No changes will be lost because they must be checked into source control and go through the automated deployment process. If emergency access is required to the production database, then consider using a ticketed procedure that logs exactly when and why permissions were elevated, and when they were revoked. Also consider using DLM Dashboard to give you a heads-up on when these changes are made.

How do you deal with changes made directly to your production databases? Please share your methods in the comments section below.

Tools in this post

DLM Automation

Continuous integration and automated deployments for your SQL Server database.

Find out more

DLM Dashboard

Track your SQL Server databases and be the first to know about schema changes.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in DLM Automation

Unearthing Bad Deployments with SQL Monitor and Redgate's Database DevOps Tools

Sudden performance issues in SQL Server can have many causes, ranging all the way from malfunctioning hardware, through to simple misconfiguration, or perhaps just end users doing things they shouldn'...

Also in DLM Dashboard

We don't need no documentation - automating schema docs in Redgate DLM

"Understanding the existing product consumes roughly
30 percent of the total maintenance time."
Facts and Fallacies of Software Engineering by Robert L. Glass.

You should be documenting your datab...

Also in Hub

Building reusable table build scripts using SQL Prompt

You've been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, ...

Also in Product learning

Using SQL Data Compare to Synchronize Custom Error Messages

One way to offer a better experience to our application users is to anticipate likely mistakes they could make, such as when filling in a web form, and provide them with a meaningful error message tha...

Also about automated deployment

5 tips for achieving continuous delivery

If you’re struggling to set up a reliable, repeatable release process you’re not alone. The good news is that most of the problems you’ll encounter have been solved before.

There are many smart...

Also about DLM Automation

Database CI with Redgate DLM Automation, PowerShell and Jenkins

While the practice of Continuous Integration (CI) started with application code, you can apply the same principles to databases. Database CI is the process by which we build, test and deploy the datab...

Also about DLM Dashboard

DLM Dashboard – the inside story

As a DevOps engineer at Redgate, I’m one of those responsible for keeping the systems going behind the scenes. That’s everything from the internal systems and databases to the infrastructure that ...