Refactoring a SQL Server schema under load
Heavy schema refactors on a live database of 800 million invoice records — where the hard part was never the schema, it was the locking.
A migration strategy that held at this data volume, arrived at by testing and rejecting several others first.
The problem
The schema underneath the loyalty platform had to change: large, structural refactors, on tables holding 800 million invoice records, in a database that could not be taken offline for the length of time the obvious approach needed.
On a small database this is an afternoon. At this size, the schema change is the easy half. The hard half is that every strategy for applying it holds locks somewhere, and the wrong lock in the wrong place stops the product.
The work was mostly not writing DDL. It was finding a way to apply it.
Early attempts ran into repeated locking and blocking — the migration would start, take a lock that the application also wanted, and the two would wait on each other while the product degraded. I worked through several migration strategies, testing each against the real data volume rather than against a scaled-down copy, since the behaviour that matters here only appears at size.
What that involved:
- Reading what the database was actually doing during a migration, from the system tables, rather than guessing from the outside.
- Splitting changes into steps that each hold their locks briefly, instead of one statement that holds one lock for a long time.
- Backfilling and switching over separately, so the long-running part of the work never sits in the path of a user request.
- Accepting a slower migration in exchange for a shorter blocking window, which is the trade nearly every version of this problem comes down to.
This is the piece of work I would most like to be asked about. It is specific, it went wrong before it went right, and I can walk through why each rejected strategy was rejected.