Migrating a Legacy Database From Integer IDs to UUIDs: A Case Study

This post walks through a full integer-to-UUID primary key migration on a mid-sized relational schema — the kind of system most teams actually run: a handful of core tables, a dozen or so foreign-key relationships, a REST API with external consumers, and zero tolerance for a multi-hour outage. The scenario is a composite drawn from patterns common to this class of migration rather than a single named production system, because the mechanics — not the company — are the reusable part. We'll go phase by phase: planning, schema changes, the dual-write period, backfill, cutover, and rollback, with the SQL and C# actually used at each step.

---

Starting Point

A representative legacy schema before migration:

CREATE TABLE Customers (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Email NVARCHAR(255) NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

CREATE TABLE Orders (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL REFERENCES Customers(Id),
    Total DECIMAL(10,2) NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

CREATE TABLE OrderLines (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT NOT NULL REFERENCES Orders(Id),
    ProductId INT NOT NULL,
    Quantity INT NOT NULL
);

Migration driver: the team was standing up a second write region and needed order creation to work without a round trip to a single primary database — a distributed-write requirement that IDENTITY columns fundamentally can't satisfy, covered in depth in our switching to UUIDs post.

Constraints that shaped the approach:

• No maintenance window longer than a few minutes was acceptable.
• The public API returned integer order IDs to external partners who couldn't be forced to migrate on the same timeline.
• Three services beyond the core API (billing, fulfillment, analytics) read directly from these tables.

---

Strategy: Expand-Contract, Not Big-Bang

A single ALTER TABLE ... ALTER COLUMN swap was never viable here — it would lock every table for the duration of the rewrite and break every foreign key simultaneously. The migration instead followed the expand-contract pattern: add the new structure alongside the old, migrate all readers and writers over incrementally, then remove the old structure once nothing depends on it anymore.

The five phases:

1. Expand — add a Uuid column to every affected table, nullable, no constraints yet.
2. Backfill — populate Uuid for all existing rows in batches.
3. Dual-write — application writes both Id and Uuid for new/updated rows.
4. Cutover — switch reads, foreign keys, and the API surface to Uuid; rename columns.
5. Contract — drop the old integer columns and their dependent objects once nothing references them.

---

Phase 1: Expand

ALTER TABLE Customers ADD Uuid UNIQUEIDENTIFIER NULL;
ALTER TABLE Orders ADD Uuid UNIQUEIDENTIFIER NULL;
ALTER TABLE Orders ADD CustomerUuid UNIQUEIDENTIFIER NULL;
ALTER TABLE OrderLines ADD Uuid UNIQUEIDENTIFIER NULL;
ALTER TABLE OrderLines ADD OrderUuid UNIQUEIDENTIFIER NULL;

Every table gets its own new primary-key-to-be column, plus a shadow column for each foreign key it holds, so the relationship can be walked in either direction during the transition. This step is purely additive — nullable columns with no constraints don't lock the table for reads or writes, and existing application code is entirely unaffected.

---

Phase 2: Backfill

Backfilling millions of existing rows in a single transaction risks lock contention and a bloated transaction log. The approach used here was batched updates with UUIDv7, chosen specifically so historical rows would sort consistently with their original CreatedAt order once the old integer column was eventually dropped.

DECLARE @BatchSize INT = 5000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP (@BatchSize) Customers
    SET Uuid = NEWID() -- see note below on backfill vs. new-row generation
    WHERE Uuid IS NULL;

    SET @RowsAffected = @@ROWCOUNT;
    WAITFOR DELAY '00:00:00.250'; -- brief pause between batches to ease lock/log pressure
END

Note: NEWID() (random, UUIDv4-equivalent) was used for the backfill because these are historical rows with no meaningful "current" timestamp to encode — using UUIDv7 for the backfill and stamping it with the row's original CreatedAt was considered, but added complexity for no real benefit, since these rows are never being newly inserted. New rows going forward, in Phase 3, use UUIDv7 for its clustering benefits (see the benchmark post for why that distinction matters).

Foreign-key shadow columns were backfilled via join, after the referenced table's own Uuid column was fully populated:

DECLARE @BatchSize INT = 5000, @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP (@BatchSize) o
    SET o.CustomerUuid = c.Uuid
    FROM Orders o
    JOIN Customers c ON o.CustomerId = c.Id
    WHERE o.CustomerUuid IS NULL;

    SET @RowsAffected = @@ROWCOUNT;
    WAITFOR DELAY '00:00:00.250';
END

This had to run in dependency order — Customers fully backfilled before Orders.CustomerUuid, Orders before OrderLines.OrderUuid — since each shadow foreign key depends on the parent's Uuid already being populated.

---

Phase 3: Dual-Write

With historical data backfilled, the application was updated to populate both ID columns on every insert and update, for the duration of the transition:

public class Order
{
    public int Id { get; set; }              // legacy, DB-generated, still primary key for now
    public Guid Uuid { get; set; }             // new, app-generated
    public int CustomerId { get; set; }
    public Guid CustomerUuid { get; set; }
}

public async Task<Order> CreateOrderAsync(int customerId, Guid customerUuid, decimal total)
{
    var order = new Order
    {
        Uuid = Guid.CreateVersion7(), // new rows use v7 going forward
        CustomerId = customerId,
        CustomerUuid = customerUuid,
        Total = total,
    };

    _db.Orders.Add(order);
    await _db.SaveChangesAsync(); // Id is still DB-assigned via IDENTITY here
    return order;
}

Crucially, the integer Id remained the actual primary key and the source of truth throughout this phase — Uuid was populated but not yet trusted or queried against in performance-sensitive paths. This kept the blast radius of any dual-write bug limited to a column nothing depended on yet.

A background consistency check ran nightly, comparing row counts and spot-checking that every new row had both IDs populated, to catch any code path that had been missed during the dual-write rollout:

-- Catches any insert path that bypassed dual-write logic
SELECT COUNT(*) AS MissingUuidCount
FROM Orders
WHERE Uuid IS NULL AND CreatedAt > @DualWriteStartDate;

---

Phase 4: Cutover

Once dual-write had been running cleanly for a full monitoring cycle (long enough to catch any missed batch or scheduled job that writes rows outside normal request paths), the cutover happened in a single short transaction per table:

BEGIN TRANSACTION;

ALTER TABLE Customers ALTER COLUMN Uuid UNIQUEIDENTIFIER NOT NULL;
ALTER TABLE Customers ADD CONSTRAINT UQ_Customers_Uuid UNIQUE (Uuid);

-- Swap the clustered primary key
ALTER TABLE Customers DROP CONSTRAINT PK_Customers;
ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (Id); -- keep old PK as unique, non-clustered, temporarily
ALTER TABLE Customers ADD CONSTRAINT PK_Customers_Uuid PRIMARY KEY CLUSTERED (Uuid);

COMMIT;

Foreign keys were repointed table by table, in the same dependency order used for backfill:

BEGIN TRANSACTION;

ALTER TABLE Orders ALTER COLUMN CustomerUuid UNIQUEIDENTIFIER NOT NULL;
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers_Uuid
    FOREIGN KEY (CustomerUuid) REFERENCES Customers(Uuid);
ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Customers; -- old int-based FK

COMMIT;

The API layer was the most operationally sensitive part of this phase, because of the external partners still expecting integer order IDs. Rather than break their contract on a fixed date, the API was changed to accept and return both identifiers during a deprecation window:

public class OrderDto
{
    public int Id { get; set; }         // deprecated, still populated
    public Guid Uuid { get; set; }        // new canonical identifier
}

[HttpGet("orders/{idOrUuid}")]
public async Task<IActionResult> GetOrder(string idOrUuid)
{
    // Accept either form during the transition window
    Order? order = Guid.TryParse(idOrUuid, out var uuid)
        ? await _db.Orders.FirstOrDefaultAsync(o => o.Uuid == uuid)
        : int.TryParse(idOrUuid, out var id)
            ? await _db.Orders.FirstOrDefaultAsync(o => o.Id == id)
            : null;

    return order is null ? NotFound() : Ok(MapToDto(order));
}

Partner integrations were given a fixed deprecation timeline for the integer form, communicated well ahead of Phase 5.

---

Phase 5: Contract

Once every internal service had migrated its reads/writes to the UUID columns, and the partner deprecation window for the integer API form had closed, the legacy columns were dropped:

ALTER TABLE OrderLines DROP CONSTRAINT PK_OrderLines;
ALTER TABLE OrderLines DROP COLUMN Id;
ALTER TABLE OrderLines DROP COLUMN OrderId;

ALTER TABLE Orders DROP CONSTRAINT PK_Customers; -- old nonclustered int PK
ALTER TABLE Orders DROP COLUMN Id;
ALTER TABLE Orders DROP COLUMN CustomerId;

ALTER TABLE Customers DROP CONSTRAINT PK_Customers;
ALTER TABLE Customers DROP COLUMN Id;

Each DROP COLUMN here was tested against a production-sized copy of the schema first — dropping a column that's part of an active constraint, index, or default in ways not caught by staging-environment testing was the single most common source of last-minute surprises during rehearsal runs.

---

What Broke During Rehearsal (Not Production)

Running this sequence against staging surfaced several issues before they reached production — the value of the expand-contract approach is precisely that these were caught in a reversible phase rather than during a live cutover:

• A reporting job queried Orders.Id directly for a date-range export and silently returned nothing once the column was renamed mid-migration, rather than erroring — caught by the nightly consistency check, not by the job itself failing loudly.
• A cached materialized view in the analytics service kept its own copy of CustomerId and wasn't part of the foreign-key migration plan at all — a reminder that "everything referencing this column" is a bigger set than "everything with a formal foreign key."
• Pagination logic in one legacy admin panel assumed WHERE Id > @last ORDER BY Id semantics and broke once IDs stopped being sequential — the exact failure mode covered in our switching post, caught here because it was rehearsed against real data volumes rather than assumed away.

---

Results

The size and raw insert-throughput costs were real and expected — consistent with the mechanics covered in our benchmark and fragmentation posts — but they were the acceptable, known trade-off for what the migration was actually solving: removing the single-writer bottleneck that was blocking the multi-region rollout entirely.

---

Takeaways for Your Own Migration

1. Never do a big-bang swap on a live table with foreign keys and external consumers. Expand-contract costs more calendar time but converts a single high-risk cutover into several low-risk, independently reversible steps.
2. Backfill and dual-write are separate phases for a reason. Backfilling historical rows and having new rows dual-written are different code paths with different failure modes — conflating them makes debugging a partial failure much harder.
3. Pick your UUID version deliberately, and it can differ by phase. Random UUIDs for a one-time historical backfill are fine; UUIDv7 for ongoing writes is what actually matters for future index performance.
4. Budget real time for "everything referencing this column" discovery. Formal foreign keys are the easy part — cached views, reporting jobs, and pagination logic that informally depend on ID semantics are where migrations actually go over schedule.
5. Give external consumers a real deprecation window, not a cutover date — supporting both ID formats temporarily costs some code complexity but avoids forcing a breaking change onto integrations you don't control.