DevOps: Dealing with issues in migration-based deployment of databases

In an earlier post, I described the difference between state-based deployments and migration-based deployments. What I want to talk about today are two of the main issues that can arise in a migration-based deployment and what to do about one of them.

When you are using a migration-based deployment technique, you are storing all the scripts that take a database from its current state to the desired final state. While tools like Ready Roll make this is a relatively easy way to perform migrations, it suffers from a few key problems:

Performance

When you are applying a series of steps to make all the required changes to a database, you may end up taking much longer than a move directly from the current state to the final state would involve. For example, you might add a new index, then later decide to add another column to it. Clearly it would be faster to just create the final index, rather than to create the index then modify it.

I often see deployments where indexes are applied, removed, reapplied, tables added, then removed again, etc. This isn’t a big problem for small databases but can be a major issue for larger databases.

While this is a real issue, it’s one that doesn’t have a simple answer when you are using migration-based deployments. It’s not one that I’m going to discuss further today. Let’s talk about another issue that you can do something about.

Drift

If you have multiple production servers, then servers in other environments (like UAT, Development, Test, etc.), in a perfect world, the schemas of the databases in each of these environments would be identical. Often though, that’s not what happens.

If an urgent production fix is required, someone might make a change directly on a production server. Worse, they might apply the same change differently on each of the production servers. Then they might forget to apply the same change (or apply it differently) in other environments. No matter what the cause, the schemas have drifted.

Now if you create migration scripts in the development environment, and even test them in a test environment, those same scripts might break when applied to the production servers because the scripts are dependent on the schemas being the same.

To work around this issue, your deployment process needs to start with schema comparisons, using either Visual Studio’s schema compare, or a tool like Red-Gate’s SQL Compare.

You might not be able to get tools like this into the production environment, but generally, you should be able to script the database(s) in the production environment (and other environments), load the scripts in a test environment, and run the comparisons there.

If they aren’t in sync, you need to fix that first.

Once you know that you schemas are in sync, you can proceed with a migration-based deployment with more confidence.

2018-02-23