Database migration with no downtime

Context

An application that has been running for years accumulates a data estate its original schema can no longer carry: columns diverted from their purpose, free text where references belong, duplicates introduced by successive imports. Rebuilding the application means taking that data with you, and that is almost always the riskiest part of the project.

The problem

The risk is not technical in the narrow sense, it is operational.

• A migration that fails halfway leaves the system in an inconsistent state
• The existing data contains cases nobody had documented
• The business cannot stop while the data is moved
• Without reconciliation checks, nobody can state that nothing was lost

The solution

A migration treated as software: versioned, tested, replayable as many times as needed.

• Mapping of the existing estate, including undocumented cases found along the way
• Cleansing and normalisation before transformation, not during
• Replayable migration scripts, producing the same result on every run
• Systematic reconciliation: counts, totals and integrity checks between source and target
• Full rehearsals on copies before any real execution
• A rollback procedure defined and tested before cut-over

My role

Analysis of the existing schema, design of the target schema, writing the scripts and checks, running the rehearsals and steering the cut-over. I also built the automation that allows the whole thing to be replayed on a fresh environment.

Constraints

• No acceptable service interruption during the migration
• Integrity verifiable, not merely assumed
• Replayable scripts: a partial run must never leave an intermediate state
• Rollback possible until the last moment
• Data volume requiring batch processing rather than a single pass

Outcome

Cut-over happened with no downtime, and the reconciliation checks make it possible to state, with figures, that nothing was lost.

• The scripts are replayable, and therefore reusable for test environments
• Discrepancies found in the historical data were handled explicitly, not silently
• The new schema carries the rules the old one left to the application