DevOps: Should migration-based deployments use idempotent scripts?

In my last DevOps-related post, I discussed some of the issues that arise when using migration-based deployments.

Migration-based deployments are based on a series of T-SQL scripts that apply changes to your database from its current state to a desired final state.

One of the questions that arises though, is whether or not these T-SQL scripts should be idempotent ie:

Should the script be written so that you get the same outcome if you run the scripts multiple times?

Creating T-SQL scripts that you can reliably run more than once isn’t easy. It’s not required for migration-based deployments if all goes well, it might make them more reliable, and/or make them more useful when errors occur during deployment. However, achieving that isn’t easy.

In essence, creating scripts like this means that instead of writing code like this:

you write code like this:

But what would happen if the table is there but different?

Sometimes it’s also messier than you’d hope. For example, CREATE SCHEMA has to be the first statement in a batch. You can’t write:

Instead, you need to write something like:

And apart from now losing help from Intellisense, etc. within the script items, this sort of thing just gets harder and harder.

My experience is that apart from situations that are fairly trivial, creating seriously idempotent scripts is much, much harder than it looks, and it’s far too easy to create scripts that can still end up with different end states.

I think you are far better off validating the schema before running the migration, and if failure occurs, recover and re-run whenever possible.

2018-03-02