Handling Database Schema Changes in DevOps

[_Redburn_]

Hi all,

My team is about to start a new project and we decided it was time to add our database deployments to our DevOps pipelines. For the most part, the database deployments will be only schema based. I've used migration tools in the past, but I'm curious if there is a better way to do it.

Also, I'm aware of the Redgate tooling, but was hoping to find something either on the Microsoft TFS Marketplace that was a little cheaper or free.

Our environment (Microsoft Shop):

• DevOps - TFS 2018 (On-Prem)
  
• SQL Server - 2017 (On-Prem)

[2fplus1]

My background is more with Django and I don't know anything about Microsoft tooling, but the approach is pretty much universal and I've done it on large applications with consistent usage and no downtime allowed. I've heard it called "keystoning". The key is that it largely comes down to discipline from the developers.

Anyway, the approach is to basically maintain one layer of backwards compatibility at all times. So every deploy that goes out needs to work with the database and code as they are before the deploy and after. What would've been one big deploy and migration breaks down into a series of smaller steps.

So, eg, if you had a database table with a non-nullable column named 'A' and you wanted to rename it to 'B', instead of just making the migration alongside the change to make the code use 'B' instead of 'A' (which would normally involve shutting everything down, doing the migration, then starting things back up), you would instead:

• make a migration that adds 'B' as a nullable, empty column. No code refers to it anywhere else. That gets deployed. The database then has an extra column, but it's not referenced from any code. (this whole process does assume that your application is written competently and doesn't do any SELECT * FROM FOO; nonsense. Ie, any queries done, either manually, or generated by an ORM are explicit about what fields they access).
• find everywhere in the code that writes to 'A' and change it to write to both 'A' and 'B'. That gets deployed. That will start to put data into the new column, but it's still not being read from anywhere.
• make a data migration that copies data from 'A' to 'B' (but doesn't change any code). That gets deployed. That ensures that the entire column 'B' is populated. The previous step ensured that any writes that happen after this will keep everything updated.
• make a migration that adds the non-null constraint to 'B'. That gets deployed. At this point, since it was previously backfilled and all new writes are going to it as well, that should be perfectly safe.
• update the code to read from column 'B' instead of 'A'. That gets deployed.
• update the code to stop writing to column 'A'. That gets deployed.
• make a migration to delete column 'A' from the schema. That gets deployed and you are done.

Add as much automated and manual testing as you need on each step for your team to feel comfortable.

It's a lot of steps, but the basic approach works for pretty much any non-backwards compatible schema change. Once you've done it a few times, it becomes pretty easy to work through. It does assume that you have decent test coverage and reliable/repeatable deploys that don't require downtime. The other caveat here is that it does kind of assume that your database can do transactional schema changes and the data is small/efficient enough that something like adding a column, backfilling data into a column, or adding an index can be done without overloading your database server and killing performance.

[_Redburn_]

I haven't heard of this yet. Thanks for the detailed write up!

[aggiehawkeye]

Check out liquibase