SQL Server Database Migration Pitfalls (and How to Fix Them)

Database migrations are where great product ideas go to die—unless you plan for SQL Server’s quirks and the realities of production load. Below are the most common mistakes I see, plus concise patterns to avoid them. Every item has a tiny, copy-pasteable example.

1) “Online” index changes that still block (and can’t be paused)

Even online operations need brief S/Sch-M locks; without guardrails they can block or be blocked.

Bad

ALTER INDEX IX_Order ON dbo.Orders
REBUILD WITH (ONLINE = ON);

Better — wait politely, bail out if needed, and make it resumable

ALTER INDEX IX_Order ON dbo.Orders
REBUILD WITH (
  ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)),
  RESUMABLE = ON
);
-- If paused: ALTER INDEX IX_Order ON dbo.Orders RESUME;

2) Auto-created / auto-dropped indexes (Azure automatic tuning surprises)

If you use Automatic Tuning in Azure SQL Database, it may create and drop indexes. That’s great for performance—but surprising during migrations if you expect the schema to be “yours only.”

Bad

-- Assume only team-created indexes exist; migration drops “unknown” ones.
-- (They were created by Automatic Tuning.)

Better

• Know what’s enabled (CREATE/DROP INDEX automatic tuning).

• Treat those indexes as ephemeral and do not bake strict dependencies on them into migrations.

• In DACPAC deployments, don’t indiscriminately drop indexes not in source (see #3).

3) Letting your DACPAC drop things (or lose data) by surprise

Focus on avoiding destructive drops, especially for Users, Permissions, and indexes you didn’t model (incl. auto-tuning ones). Assume your team owns the database alone unless stated otherwise.

Baseline to aim for (teams that fully own their DB):

<BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
<DropObjectsNotInSource>True</DropObjectsNotInSource>
<DoNotDropObjectType>Users;Permissions</DoNotDropObjectType>
<DropIndexesNotInSource>False</DropIndexesNotInSource>

Explanation

• BlockOnPossibleDataLoss=True aborts risky publishes.

• DropObjectsNotInSource=True keeps drift low but pair it with:

  • DoNotDropObjectType=Users;Permissions so principals and grants stay.

  • DropIndexesNotInSource=False so you don’t drop indexes your dacpac didn’t model (incl. Azure’s automatic ones).

4) CDC tables break after schema changes

Change Data Capture doesn’t automatically follow new columns on captured tables.

Bad

ALTER TABLE dbo.Customers ADD PreferredColor sysname NULL;
/* Expect CDC to pick it up automatically – it won’t. */

Better — plan a new capture instance, then retire the old

-- Option: create a new capture instance with desired column list,
-- sync consumers to it, then drop the old instance when safe.
EXEC sys.sp_cdc_disable_table @source_schema='dbo', @source_name='Customers', @capture_instance='dbo_Customers'; -- when you’re ready
EXEC sys.sp_cdc_enable_table  @source_schema='dbo', @source_name='Customers', @role_name=NULL; -- (re)enable with columns you need

5) Temporal tables altered in place

System-versioned tables need special handling; many schema changes require turning versioning off, updating both current and history, then turning it back on.

Bad

ALTER TABLE dbo.InvoiceHistory ADD Comments nvarchar(200); -- Fails or misbehaves

Better

ALTER TABLE dbo.Invoice SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.Invoice       ADD Comments nvarchar(200) NULL;
ALTER TABLE dbo.InvoiceHistory ADD Comments nvarchar(200) NULL;

ALTER TABLE dbo.Invoice
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.InvoiceHistory));

6) Partition switching that isn’t aligned

Partition switching only works if the table and all aligned indexes share compatible partitioning.

Bad

-- Table and index have different partition schemes/ranges → SWITCH fails.
ALTER TABLE dbo.Fact SWITCH PARTITION 12 TO dbo.Fact_Archive PARTITION 12;

Better

• Ensure table and all indexes are aligned (same function/boundaries).

• Then switch:

ALTER TABLE dbo.Fact
SWITCH PARTITION 12 TO dbo.Fact_Archive PARTITION 12;

7) Long-running index maintenance on busy tables

Huge rebuilds during peak load will grow log/tempdb and hurt concurrency.

Bad

ALTER INDEX ALL ON dbo.Orders REBUILD;  -- default, offline on Standard

Better

• Prefer targeted maintenance; rebuild only the indexes that need it.

• Use ONLINE + RESUMABLE and a sensible MAXDOP.

ALTER INDEX IX_OrderDate ON dbo.Orders
REBUILD WITH (
  ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION=5, ABORT_AFTER_WAIT=SELF)),
  RESUMABLE = ON,
  MAXDOP = 2
);

8) One-shot destructive changes

Dropping or renaming in the same release as the app change risks outages.

Bad

-- In one deploy:
EXEC sp_rename 'dbo.Customer.FName', 'FirstName', 'COLUMN';
ALTER TABLE dbo.Customer DROP COLUMN LastName;

Better — two-phase, backward-compatible migrations

1. Deploy A: add new columns, dual-write, backfill, keep old ones.

2. Deploy B: switch reads, remove old columns when unused (and empty).

9) Using MERGE for upserts in critical migrations

MERGE has a long history of tricky edge cases (indexed views, OUTPUT, concurrency), and multiple documented defects.

Bad

MERGE dbo.Target AS t
USING dbo.Source AS s ON t.Id = s.Id
WHEN MATCHED     THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...);

Better — explicit, simpler statements

-- Update the matches
UPDATE t
SET    /* ... set columns ... */
FROM dbo.Target AS t
JOIN dbo.Source AS s ON s.Id = t.Id;

-- Insert the non-matches
INSERT INTO dbo.Target (Id, /*...*/)
SELECT s.Id, /*...*/
FROM dbo.Source AS s
WHERE NOT EXISTS (SELECT 1 FROM dbo.Target AS t WHERE t.Id = s.Id);

10) Deleting millions of rows under row-versioning (tempdb + log pain)

Under RCSI/SNAPSHOT, updates/deletes version rows into tempdb. A huge DELETE can explode tempdb and the log. If your intent is to purge, don’t row-by-row delete.

Bad

-- Bloats log and tempdb under row versioning:
DELETE FROM dbo.Events WHERE EventDate < DATEADD(day, -90, SYSUTCDATETIME());

Better — truncate or switch, else batch

• Partitioned table: use partition SWITCH out + fast TRUNCATE on the staging table.

-- Switch a cold partition out:
ALTER TABLE dbo.Events SWITCH PARTITION 1 TO dbo.Events_Work PARTITION 1;
TRUNCATE TABLE dbo.Events_Work;

• Non-partitioned: delete in small batches with short transactions.

WHILE 1=1
BEGIN
  DELETE TOP (10_000) FROM dbo.Events
  WHERE EventDate < DATEADD(day, -90, SYSUTCDATETIME());
  IF @@ROWCOUNT = 0 BREAK;
  CHECKPOINT; -- optional
END

• Prefer TRUNCATE TABLE for full-table purges (resets identity; minimal logging, but still fully logged meta-ops).

Quick checklist to bake into your pipeline

• Block data loss and control drops in dacpac publishes (see #3).

• Use ONLINE + WAIT_AT_LOW_PRIORITY and RESUMABLE for index work.

• Treat Azure Automatic Tuning indexes as “non-authoritative.”

• Plan for CDC and Temporal special cases.

• Prefer batching, TRUNCATE, or partition switching over huge deletes.

• Avoid MERGE in mission-critical migrations; use clearer UPDATE + INSERT.