Database Migrations Without Downtime in .NET

Every team I have led eventually hits the same wall: the schema needs to change, the business cannot tolerate a maintenance window, and the database underneath a live payments platform is the least forgiving place to make a mistake. In regulated fintech the cost of getting this wrong is not measured in a few seconds of user frustration. It is measured in failed settlements, broken reconciliation, and a compliance conversation nobody wants to have.

Over the years I have settled on a fairly disciplined approach to schema evolution in .NET, built around the idea that a migration is not a single event but a sequence of small, individually safe steps. This post is the playbook I hand to engineers joining my teams. It is opinionated, it leans on Entity Framework Core where that helps, and it is deliberately conservative because conservatism is the right default when money is moving.

Why Downtime Is Not an Option

When I started out, a Sunday-night maintenance window was a perfectly respectable way to ship a schema change. You took the system offline, ran the script, verified, and came back up before the markets opened. That world is gone. Our customers integrate over APIs that they expect to be available continuously, and our own internal services assume the database is always there. A planned outage is a contractual and reputational liability, not a convenience.

The deeper reason is architectural. Modern .NET systems are rarely a single application talking to a single database. They are a fleet of services, background workers, and scheduled jobs, often deployed independently. The moment you accept that you cannot stop all of them at once, you are forced to accept that old code and new code will run against the same database at the same time. Every migration strategy I trust flows from that single constraint.

Once you internalize that constraint, the problem reframes itself. You are no longer asking how to change the schema. You are asking how to change it in a way that is compatible with every version of the application that might be running during the rollout. That is a harder question, but it is the right one.

The Expand and Contract Pattern

The single most valuable idea in zero-downtime migrations is expand and contract, sometimes called the parallel change. Instead of mutating a column or table in place, you split the change into three distinct deployments: expand the schema additively, migrate the code and backfill the data, then contract by removing what is now unused. Each deployment is independently reversible, and at no point does the database hold a shape that the currently running code cannot understand.

Consider renaming a column from customer_name to legal_entity_name. The naive approach is a single rename, which instantly breaks any deployed code still selecting the old name. The expand-and-contract version looks like this:

• Expand: add the new legal_entity_name column, nullable, with no constraints. Old code ignores it; new code can begin writing to it.
• Migrate: deploy code that writes to both columns and reads from the new one with a fallback, then backfill historical rows in batches.
• Contract: once every instance reads and writes only the new column, drop the old one in a final, separate deployment.

It feels like more work, and it is. But each step is small enough to reason about, test, and roll back. I would rather ship three boring deployments than one heroic one that pages the on-call engineer at three in the morning.

EF Core Migrations as the Baseline

In .NET, EF Core migrations are my default tooling, not because they are magic but because they give you a versioned, reviewable, source-controlled history of schema change. The mistake teams make is treating the generated migration as the finished product. The scaffolded code is a starting draft. You must read it, and frequently you must rewrite it, because EF does not know which operations will lock a table under production load.

I insist that every migration be split so that schema changes and data changes never share a transaction with anything long-running. I also require that destructive operations live in their own migration, applied only after the corresponding code has been fully rolled out. Here is the shape of an additive expand step that I would happily approve:

public partial class AddLegalEntityName : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Additive and nullable: safe to apply while old code runs.
        migrationBuilder.AddColumn<string>(
            name: "legal_entity_name",
            table: "customers",
            type: "varchar(256)",
            nullable: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "legal_entity_name",
            table: "customers");
    }
}

Notice what is absent: no NOT NULL, no default that forces a table rewrite, no index built inline. Those come later, deliberately, once the column exists and is populated. The migration that I will not approve is the one that tries to do everything at once.

Backfilling Data in Batches

The expand step is cheap. The backfill is where the danger lives, because a single UPDATE across a large table will take a lock, blow up your transaction log, and stall every concurrent writer. I have watched a well-meaning engineer run an unbatched update on a forty-million-row ledger table and effectively take the platform down without ever touching the application code.

The discipline is to backfill in bounded batches, with a delay between them, and to make the operation idempotent so it can be safely restarted. I keep this logic out of EF migrations entirely and run it as a background job or an out-of-band script, because migrations should be fast and deterministic. A batched backfill in raw SQL looks like this:

DECLARE @batch INT = 5000;
WHILE 1 = 1
BEGIN
    UPDATE TOP (@batch) c
    SET c.legal_entity_name = c.customer_name
    FROM customers c
    WHERE c.legal_entity_name IS NULL
      AND c.customer_name IS NOT NULL;

    IF @@ROWCOUNT < @batch BREAK;
    WAITFOR DELAY '00:00:01';
END;

The WAITFOR DELAY is not a nicety. It gives the database room to breathe, lets replication keep up, and keeps lock contention with live traffic to a minimum. Slower is safer, and on a production payments system safer always wins.

Indexes and Constraints Without Locks

Adding an index is one of the most common ways teams accidentally cause an outage, because a default index build holds a lock that blocks writes for the duration. On any table large enough to matter, that duration is long enough to break things. The fix depends on your engine, but the principle is universal: build indexes online or concurrently, never inline with the rest of a migration.

On SQL Server I use WITH (ONLINE = ON) where the edition supports it; on PostgreSQL the equivalent is CREATE INDEX CONCURRENTLY, which cannot run inside a transaction and therefore must be lifted out of EF Core's default transactional migration wrapper. Constraints follow the same logic. Adding a NOT NULL constraint or a foreign key validates every existing row, which can be a lengthy locking scan.

The safest constraint is one the database does not have to prove all at once. Add it as not-validated, then validate it as a separate, low-priority operation against data you have already cleaned.

This staging of constraints matters because it lets you decouple the cheap structural change from the expensive validation scan. You get the integrity guarantee you want without forcing the database to acquire a heavy lock at the worst possible moment.

Coordinating Deployment and Schema

The hardest part of zero-downtime migration is not the SQL. It is the choreography between application deployments and schema state. The rule I enforce is that the schema must always be compatible with both the version of the code currently running and the version about to be deployed. This is sometimes called the N-1 compatibility rule, and it is non-negotiable on my teams.

Concretely, this means you never deploy a schema change and the code that depends on it in the same release. The schema expands first, in its own release, and only once that is fully rolled out and verified does the dependent code follow. The contraction happens in a third release, after you are confident no running instance references the old shape. Rolling deployments and blue-green strategies both work, but only if the database respects this ordering.

I also instrument the rollout. Before I drop a column, I want telemetry proving that nothing has read or written it for a meaningful window. Hope is not a deployment strategy, and in a regulated environment you will be asked to demonstrate that a change was safe, not merely assert that it was.

Rollback and the Point of No Return

I tell every engineer that a migration without a rollback plan is not a plan, it is a gamble. The expand-and-contract pattern is valuable precisely because most of its steps are trivially reversible. An additive nullable column can be dropped. A backfill can be re-run. A dual-write can be turned off with a feature flag. As long as you have not yet contracted, you can retreat.

The contract step is different, and I treat it with appropriate respect. Dropping a column or a table is the point of no return. Before that operation runs, I want a verified backup, a written confirmation that the data is no longer needed, and ideally a soft-delete period where the object is renamed and quarantined rather than destroyed. I have more than once renamed a table to customers_deprecated and left it untouched for a release cycle before truly dropping it.

Feature flags deserve a specific mention here. They let me separate the act of deploying code from the act of activating behavior. I can ship the dual-writing code dark, enable it gradually, watch the metrics, and flip it off instantly if something looks wrong, all without a redeploy. In a downtime-sensitive system that decoupling is worth a great deal.

Testing Migrations Before Production

A migration that has only ever run against an empty developer database has not been tested. The behavior you care about, locking and duration and contention, only appears at scale. I require that significant migrations be rehearsed against a restored copy of production-scale data, ideally in an automated pipeline that times each step and fails the build if anything exceeds a threshold.

Containerized databases make this far easier than it used to be. Spinning up a real engine in a test fixture, applying the full migration chain, and asserting on the resulting schema is now routine in a .NET test suite. I also test the down path, because a rollback that has never been exercised is a rollback you do not actually have. The discipline pays for itself the first time a migration that looked fine in review turns out to take twenty minutes against real volume.

Finally, I review migrations as carefully as I review application code, often more so. A pull request that includes a schema change gets a second reviewer who has done this before, and the migration is read line by line. The database is shared state for the entire platform, and shared state in a payments system earns the strictest scrutiny we can give it.

Conclusion

Zero-downtime migration is not a clever trick or a single tool. It is a habit of mind that treats every schema change as a sequence of small, reversible, independently deployable steps, ordered so that old and new code can always coexist. Expand and contract, batched backfills, online indexes, N-1 compatibility, and rehearsed rollbacks are the load-bearing pieces. EF Core gives you the versioning and the scaffolding, but the judgment about what is safe under production load remains yours. In regulated fintech that judgment is the job, and the teams that internalize it ship change continuously without ever asking their customers to wait.