Schema Changes Without Breaking Running Applications

A schema migration can succeed while the application fails. During a rolling deployment, old and new processes often share the same database. Renaming a column in place or immediately making a new field mandatory can break the version that is still running. The deployment unit is the application-data contract, not just one ALTER TABLE statement.

Expand before requiring the new shape

A practical sequence is to add a compatible structure, deploy code that understands both shapes, migrate historical data, verify the result, and only later remove the old contract. Adding a nullable column is often a useful first step, but it still requires schema locks. Small metadata changes are not promises of zero blocking.

The temporary-table exercise demonstrates those stages on a tiny dataset. Execute each block in order. The final NOT NULL change represents a separate deployment gate, not something to run automatically as soon as the column appears.

CREATE TABLE #Orders(OrderId int PRIMARY KEY, Amount decimal(12,2));
INSERT #Orders VALUES(1,10),(2,20);
ALTER TABLE #Orders ADD CurrencyCode char(3) NULL;

UPDATE #Orders SET CurrencyCode='USD' WHERE CurrencyCode IS NULL;
SELECT OrderId,Amount,CurrencyCode FROM #Orders ORDER BY OrderId;

IF EXISTS(SELECT 1 FROM #Orders WHERE CurrencyCode IS NULL)
    THROW 50000, 'Backfill is incomplete.', 1;
ALTER TABLE #Orders ALTER COLUMN CurrencyCode char(3) NOT NULL;

In production, establish how new writes populate CurrencyCode before finishing the backfill. The sample assigns USD only because that is the declared meaning of its practice rows. A convenient default is not a substitute for discovering the actual currency of historical orders. Unknown values need reconciliation rather than invented facts.

Control the transition under concurrent writes

If old and new fields coexist, decide which is authoritative during each phase. Dual writes must be atomic or reconciled; otherwise a failure can update one representation but not the other. A fallback read may preserve availability while silently hiding divergence, so measure disagreement explicitly before switching readers to the new field.

Backfill in restartable batches when the real table is large. Use a stable key range and a predicate that does not overwrite newer application values. Commit progress with the corresponding changes when progress must survive failure. A batch-size limit bounds changed rows, not necessarily rows scanned or log generated by triggers and dependent indexes.

Schema modifications can wait behind long-running readers or writers. Set an operational timeout and a retry policy appropriate to the deployment, inspect blockers, and avoid repeatedly launching another migration while the first attempt is still waiting. Online options are operation-, version-, and edition-dependent; they do not mean that no locks are taken.

Make rollback a compatibility decision

Before removing the old field, prove that no supported application version, report, job, export, or dynamic SQL path still uses it. Catalog dependency queries help but cannot discover every string assembled outside the database. Combine inventory with execution evidence and a period of mixed-version testing.

Rolling application code back is straightforward only while the database still supports its contract. Once new writes contain values that the old representation cannot express, reverting code may lose meaning even if the old column still exists. Document the last reversible stage and the data-preserving recovery path for later stages.

Validate more than row counts: compare transformed values, constraints, representative reads, and writes from both application versions. Rehearse interruption during backfill and resumption after partial completion. Keep migration identifiers and completion state so a retry can distinguish already applied work from a failed step.

The final removal should be its own reviewed release after the new contract has demonstrated stability. This approach costs several small steps, but it makes each step explainable: what can read, what can write, which data is authoritative, and what remains reversible.

Technical references: Microsoft Learn: ALTER TABLE · Microsoft Learn: Dependency metadata.