Avoiding deployment grief from database drift

*Movie guy voice*

In a world where applications are developed and shipped to multiple customers. One company effectively saves money and has happier customers by understanding database drift.

I was once talking to a friend at a SQL Server Users Group about a deployment challenge she was facing and she said something that I have not forgotten to this day…. “there are 2 versions of the truth when it comes to software products: The code you have in source control and what you have in production.”

Ideally, the code running in production is the same as what you have in your source control system. When they are not the same you have version-drift.

Version drift can cause serious problems when you are trying to deploy a new release as we can see in the following scene:

INT. SERVER ROOM – SUNDAY MORNING

IT SPECIALIST, just awakened, slurps down some coffee as he prepares to install a product upgrade. Having upgraded many times without issue he foregoes making a backup of his database and environment before starting the new deployment.

BLEEP! The computer vomits out an error. Something went wrong!

IT SPECIALIST

Sally, I just ran the deployment script you sent me and I got an error.

SALLY

What did the error say? It should be at the bottom of the screen.

IT SPECIALIST

Let me see. Ah, Here it is. “Cannot create table because it already exists.”

SALLY

Hmm, that’s not right. That table didn’t exist in version 1. Can you restore your backup? We are going to have to figure out what is going on with your deployment.

IT SPECIALIST

Um, I didn’t take a backup before starting the upgrade.

Frantically, checking things on the computer.

IT SPECIALIST (CONT’D)

$#@*!. The last good one we have is 24 hours old.

SALLY

Doh! Don’t panic we can get you through this. Unfortunately I do not know the current state of your database now that the upgrade was partially deployed. Can you take a backup and send it to me so that I can get a custom deployment script for you to get the software to the state it needs to be in?

IT SPECIALIST

Yeah it will take me a little bit to make the backup and send it to you.

SALLY

I will get ahold of one of our developers and we should be able to create a custom deployment for you fairly quickly.

The story will ultimately end with the application being down for some period of time as a site specific deployment is created.

When you have a product at a single site you can request backups and make custom deployment packages for every location. However, as the number of customer sites increases this strategy quickly becomes unfeasible and too costly.

Many things can cause database drift. Here are a few of the more common ones:

Hotfixes that are delivered to the customer to fix bugs found in their current release. These should be provided and tracked by the development team so they can be accounted for during deployment creation and testing.
Custom views, procedures, report queries, tables, etc. created onsite by the customer or analyst. These should be created in the “CUSTOM” schema we ship in our product so that they do not negatively affect standard software deployment.
Indexes created for performance. Ideally the development team would provide these as part of a hotfix so they are adequately tracked and tested, but at a minimum they need to be notified of the change.

With the current tooling (SSDT, RedGate, etc.) we can easily create deployment packages based on known states, accounting for known drift as part of a common practice.

It is possible to make each deployment 100% resilient to drift. However, as with everything, this requires time and resources and could add a significant increase in the amount of time needed to create every deployment package. Typically the increase in cost is not justified as it subtracts time from doing more valuable things. As the products and customer base grows these processes are streamlined.

We should all remain aware that with standard products we must be vigilant that we are not making changes to released software in the wild without it being documented (backlog item?) as a change that development needs to account for during deployment. This is especially important as we begin product sustainment and hotfixes may be created for release between software service packs and releases.