Why database migrations fail, and the discipline that prevents it

Most database migrations that go wrong do not go wrong because the migration service failed. AWS Database Migration Service (DMS) reliably copies rows and replicates ongoing change; the Schema Conversion Tool (SCT) reliably converts the schema and flags what it cannot. Those tools are necessary, and they are good. They are also not sufficient. A migration is not really a data-copy problem. It is a problem of knowing what depends on the database, proving the copy is correct, and switching over without breaking the business.

Three classic disciplines decide whether a migration lands cleanly, and the failures map one-to-one to skipping them: no discovery of dependencies, underestimated data validation, and cutover treated as an afterthought. None of the three is a tooling gap. Each is an engineering practice that a project either does or doesn't. This piece is about doing them.

Cause 1: no discovery of dependencies

A production database is almost never a standalone object. It sits at the centre of a web of consumers that grew over years, and the people who built half of them have moved on. Before you move it, you need to know what is attached to it. The usual list:

• Applications, including the obvious primary app and the less obvious internal admin tools and microservices that read directly.
• ETL and integration jobs that extract, load, or sync data on a schedule.
• Reporting and BI, dashboards and extracts that point at the database or a replica of it.
• Scheduled jobs, cron entries, SQL Agent jobs, and database-internal schedulers that run quietly overnight.
• Linked servers and database links that let other databases reach in.
• Downstream replicas and the consumers fed from them.
• Monitoring and backup agents with their own credentials and connections.

The failure mode is specific and familiar: the migration goes smoothly, the application works, everyone relaxes, and three nights later a report comes out empty or a partner's nightly feed silently stops because a job nobody listed was still pointing at the old host. The data moved fine. The map was incomplete.

Discovery is empirical, not a meeting. You find consumers by observing the database under real load and corroborating with the people who own things:

• Inspect active sessions over a representative period, including end of month. On PostgreSQL, pg_stat_activity shows connecting users, client addresses, and the queries they run; on SQL Server, sys.dm_exec_sessions joined to sys.dm_exec_connections gives host names and programs.
• Enable and inspect audit and connection logs so you capture connections that are intermittent rather than constant; a once-a-month job will not appear in a single afternoon's snapshot.
• Use VPC Flow Logs and network analysis to see which source addresses actually open connections to the database port, which catches consumers that no application config of yours knows about.
• Inventory connection strings across application configs, deployment manifests, secrets stores, and scheduler definitions, so each live connection maps back to a named owner.
• Interview the teams. Reporting, integration, finance, and operations each tend to know about a feed or extract that lives nowhere in the codebase.

The output of discovery is a single artefact: a complete consumer inventory, every connection mapped to a system and an owner, signed off before anything moves. That inventory is also your cutover contact list and your post-cutover test plan, so the effort pays for itself three times.

Cause 2: underestimated data validation

The second failure is quieter and more dangerous, because it can pass unnoticed into production. “It looks fine” is not validation. A spot-check of a few screens proves nothing about millions of rows, a truncated column, a collation that silently reordered keys, or a numeric overflow on a single large record. Validation means proving the target is equivalent to the source, by measurement, before anyone trusts it.

A layered set of checks, from cheap-and-broad to precise-and-targeted, catches different classes of error:

• Row counts per table, source versus target, as the first and coarsest gate. A mismatch here is unambiguous and fast to find.
• Checksums per critical table, hashing the meaningful columns so that altered values, not just missing rows, are detected.
• Referential-integrity checks, confirming foreign keys resolve and no orphaned child rows were created by an out-of-order load.
• Reconciliation of business aggregates, the sum of a ledger column or an account-balance total, source versus target, so the numbers the business actually cares about are proven equal.
• Targeted edge-case checks, nulls versus empty strings, character encodings and collation, very large values, and dates with their time zones, which are where engine differences hide.

DMS has built-in data validation that compares source and target row-by-row and reports mismatches, and you should turn it on. But run independent checks too. The point of validation is to catch the error the migration tool didn't notice or introduced, so checks that share the tool's blind spots add little assurance. A business-aggregate reconciliation is the highest-value independent check, because it tests meaning rather than mechanics:

-- Reconcile a financial total, source vs target, after load.
-- Run the same query on both engines; the two figures must match exactly.
SELECT
    COUNT(*)            AS row_count,
    SUM(amount_cents)   AS total_cents,
    MIN(posted_at)      AS earliest,
    MAX(posted_at)      AS latest
FROM   ledger_entries
WHERE  posted_at < '2026-06-01';   -- freeze the window: only settled, pre-cutover data

Storing the amount in integer cents avoids floating-point drift between engines; a row count alone would miss two rows whose values were swapped, so reconcile the sum, not just the count.

Validation belongs inside the frozen cutover window, after the final data sync and before you open the target to traffic, and it must end in a go/no-go gate: defined checks, defined pass criteria, a named person who calls it. If the reconciliation does not balance, you do not go. That gate is what turns “we think it's fine” into a decision you can stand behind.

Cause 3: cutover as an afterthought

The third failure is leaving the switchover to be figured out on the night. Cutover is the riskiest hour of the whole project, and it is too often the least planned. Treated as an afterthought, it becomes an improvised sequence of manual steps under time pressure with the business watching, which is exactly the condition under which mistakes happen and rollback decisions get made too slowly.

Cutover should be a rehearsed, owned, time-boxed event with three things written down in advance:

• A runbook: every step in order, each with an owner and an expected duration, so the team is executing a script rather than thinking on their feet.
• A go/no-go gate: the validation result and the consumer-readiness checks that authorise opening traffic, with a named decision-maker.
• A tested rollback: the exact steps to return to the source, proven to work, with a defined point of no return after which you commit forward.

One liberating consequence of planning cutover properly: downtime is set by the plan, not by the size of the database. With DMS change data capture keeping the target continuously in sync, the bulk copy happens days ahead and the actual switch is a short, rehearsed sequence of stopping writes, draining the last changes, validating, and repointing consumers. A large database does not require a long outage. A badly planned cutover does.

These are disciplines, not tools

It is worth saying plainly, because it is the whole point. DMS and SCT are genuinely good at their jobs, and they remove enormous amounts of manual labour. They are also necessary, not sufficient. No tool discovers the nightly job nobody documented. No tool decides that an unbalanced reconciliation means stop. No tool rehearses your cutover and proves your rollback. Those are engineering practices a team chooses to perform, and a migration that performs them with mediocre tooling beats one that skips them with the best tooling available.

A pre-cutover checklist

The three disciplines distil into an ordered, tickable list. Work top to bottom; you do not start one stage until the previous one is signed off.

1. Consumer inventory complete, every application, job, integration, report, linked server, replica, and agent identified and mapped to an owner.
2. Discovery corroborated empirically, against active sessions, audit and connection logs, and VPC Flow Logs, not just config files and memory.
3. Each consumer has a cutover action and an owner, repoint, reconfigure, or retire, with the owner on the cutover contact list.
4. Validation suite written and dry-run, row counts, per-table checksums, referential-integrity checks, and business-aggregate reconciliations.
5. Edge cases covered, nulls and empty strings, encodings and collation, large values, and dates with time zones.
6. DMS ongoing replication healthy, change data capture caught up with no validation errors outstanding.
7. Runbook authored, every step ordered, owned, and time-estimated, with the freeze point defined.
8. Rollback tested, proven against a copy, with the point of no return agreed.
9. Cutover rehearsed end to end against a production copy, with timings recorded.
10. Go/no-go gate defined, pass criteria written and the decision-maker named, with the authority to say no.

Work that list honestly and the migration becomes what it should be: a non-event the business barely notices. Skip parts of it and you are not running a migration, you are running an experiment in production. The tools are ready. The discipline is yours to bring.