Shipping a database transition while the site stays up

Schema changes have a reputation for requiring maintenance windows. Mostly they don't. What they require is a sequence — and the discipline to let the database and the code change at different moments instead of one big-bang deploy.

This past May I moved this site — css-i.com — from mostly-hardcoded content to a database-backed content platform: three new tables, six new columns on a live table, and a migration history rebuilt from scratch. The site served traffic through the entire transition. The whole trick still fits in one word, plus two traps — but this time I want to show the actual scripts, because the scripts are where the traps live.

The riskiest thing wasn't the deploy

Before any schema work started, the scariest discovery had nothing to do with SQL: local development was pointed at the production database. Every local run of the app connected to the live site's data. Fine for tweaking copy; disqualifying for schema surgery, because the plan called for dropping roughly half the tables — a dead subsystem left over from the site's 2017 template era — and collapsing years of EF Core migrations into a clean three-migration baseline.

So rule zero came before any clever sequencing: development got its own isolated LocalDB, and production would change exactly once, at a deliberate cutover, with a backup taken first. The lean-out — 29 tables down to 15 — happened freely in dev. Production would receive only the additive subset.

That split is the heart of the whole technique.

Additive is the whole trick

New tables and new defaulted columns break nothing that already runs. The old code doesn't know they exist and doesn't care. Which means the database transition can happen while the old code is still live — quietly, reversibly, days before any deploy if you like.

This is the expand phase of the expand/contract discipline — Martin Fowler's "parallel change", the backbone of evolutionary database design and the same sequence PlanetScale teaches for backward-compatible schema changes: expand compatibly, migrate usage, contract later. My cutover was small by those standards — three CREATE TABLEs, six ALTER TABLE ... ADDs — but the property that matters is identical at any scale: at every moment, the schema supports the code that is currently running.

That decomposes a risky simultaneous change into two safe ones. First the schema moves forward, and the running site proves backward compatibility in real time. Then the code swaps — and code swaps roll back in seconds, which a schema change never does.

A backward-compatible schema change means the rollback plan for the deploy is "do nothing."

One engine-level footnote, so "additive is safe" doesn't get repeated somewhere it isn't true: compatible and cheap are different claims. On Azure SQL Database (and SQL Server Enterprise since 2012), adding a NOT NULL column with a constant default is an online, metadata-only operation — existing rows are never rewritten, so the ALTER finishes near-instantly regardless of table size. MySQL has historically been far less forgiving, which is why GitHub built gh-ost to replay schema changes against a shadow table. Additive solves compatibility everywhere; whether it's also non-blocking is a question to put to your specific engine before you run anything under a live site.

Drops and renames are a different animal; they break the old code instantly. Defer them. Dead tables cost pennies; dropping them is housekeeping for a calm week after the cutover proves out — more on how that actually went below.

The cutover script is a review artifact

EF Core will generate the transition for you: dotnet ef migrations script --idempotent emits SQL that checks the migration-history table before every block, so it's safe to run against a database in any state. Mine came out as one transaction of guarded blocks:

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20260522155455_ContentEntities'
)
BEGIN
    ALTER TABLE [PortfolioItems] ADD [Status] int NOT NULL DEFAULT 0;
END;

But generation isn't review. I read the script top to bottom for one property above all others: no DROP anywhere. Mine was clean — six adds, three creates, the history inserts, nothing destructive. That read-through is also where the second trap was hiding, in that innocent-looking DEFAULT 0. Hold that thought.

Trap one: mark the baseline before the code boots

Trap one is migration bookkeeping. Re-baselining migrations — collapsing years of history into a clean starting point — creates a lie of omission: production already has the baseline schema, but its __EFMigrationsHistory table doesn't say so. The new code boots, calls Migrate(), tries to create tables that already exist, and dies on startup. The zero-downtime cutover becomes a site that won't start, at the exact moment the old code is no longer what's deployed.

The fix is one insert, applied during the transition, before the new code ever runs:

-- Production already has the baseline schema; record that fact so
-- the deployed app's Migrate() doesn't try to recreate it.
IF NOT EXISTS (SELECT * FROM [__EFMigrationsHistory]
               WHERE [MigrationId] = N'20260522134349_Baseline_ContentPlatform')
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20260522134349_Baseline_ContentPlatform', N'9.0.0');

The new app wakes up, sees everything applied, and does nothing. Exactly what you want.

An honest aside: this app applies migrations at startup, an approach the EF Core docs steer production systems away from — instances can race to migrate, and the SQL runs without anyone reviewing it. This site runs a single instance and made the trade deliberately. But the trap doesn't care how you apply migrations: whether Migrate() runs at boot or a pipeline runs the script, the history table has to be told about the baseline before anything tries to replay it.

Trap two: the default that changed what rows mean

Trap two is subtler, and it nearly cost me a portfolio. The new Status column needed a default for existing rows, and the migration's natural default was 0. But zero meant something — Draft — and every public page filters to Published. Apply the migration as-is and all seventeen existing portfolio projects silently vanish from the live site. No error, no exception, no failed request. Just a work page quietly rendering empty.

The schema change was additive; the semantics weren't. Every new column whose default carries meaning needs a follow-up statement that sets existing rows to the value that preserves today's behavior:

-- Status arrived with DEFAULT 0 (= Draft). These projects were live
-- yesterday; keep them live today.
UPDATE [PortfolioItems]
SET [Status]     = 1,   -- Published
    [CreatedUtc] = SYSUTCDATETIME(),
    [UpdatedUtc] = SYSUTCDATETIME()
WHERE [Status] = 0;

Note the timestamps riding along: the new datetime columns arrived with a 0001-01-01 placeholder default — the same trap in a milder form, a default that would have leaked into public pages as nonsense dates instead of missing records. The test for every defaulted column is one question: what does this default make yesterday's rows mean tomorrow?

Sequence is the safety

The actual order of operations, each step independently safe and independently verifiable:

1. Back up. A full copy of the production database, confirmed online before anything else moved. This is the rollback for the schema step, and nothing proceeds until it exists.
2. Inspect. A read-only query against production first: what does the migration history actually contain, and are the new tables really absent? Assumptions checked, in writing, before any write.
3. Expand, under the live site. Run the idempotent cutover transaction. The old code keeps serving throughout — this step is the one the whole post is about.
4. Fix the semantics. The baseline insert and the Status correction, together, while the old code is still what's running.
5. Verify the old world. The site still up, still showing all seventeen projects, old code against new schema. Backward compatibility stops being a hope and becomes an observation.
6. Deploy the code as its own step. On Azure App Service the designed move is deploy-to-slot then swap — a near-instant routing change whose rollback is just another swap. In the event, my publish profile shot straight past the staging slot to production — and it didn't matter, because the schema was already proven under live traffic. The sequence had made even a fumbled deploy step safe.
7. Verify the new world. Every route, the admin, the API.

Each step is invisible to users until the deploy, and the deploy itself is the smallest, most reversible piece. The order isn't ceremony; it's the entire reason nothing goes dark.

The contract phase arrives late, and calmly

The lean-out that took minutes in dev took weeks to reach production, on purpose. After the cutover, production still carried thirty dead tables — twenty-eight of them an abandoned blog-engine install that had squatted in the database since the template era, holding nothing but its own sample data. They stayed through the cutover because dropping them would have violated the one rule that made everything safe, and they stayed after the cutover for a sneakier reason: the staging slot still held the old code, and the old code still expected those tables. Until the slot was updated, the "dead" tables were load-bearing — they were the rollback path's schema.

When the drop finally happened, it got the same respect as the cutover: dependencies inventoried first, foreign keys dropped before tables, the whole thing one SET XACT_ABORT ON transaction so it either fully happens or fully doesn't. Expand took an afternoon. Contract waited until it was boring — which is exactly the energy a contract phase should have.

The short version

Isolate development from production before anything else. Make the schema change additive and apply it under the running site as an idempotent, reviewed script. Record the migration baseline before the new code ever boots. Interrogate every defaulted column for semantics that flip what existing rows mean. Deploy code as its own instantly-reversible step. And save the drops for the calm week after nothing — not even your rollback path — remembers the old schema. Downtime isn't the price of schema change; it's the price of skipping the sequence.