How to plan a legacy database migration without downtime

Where migration downtime comes from

Most migration downtime comes from a single moment: the big switch, when the old system is stopped, the data is copied, and everyone waits to find out if the new one works. The longer the copy and the less tested the new system, the longer the outage and the harder the return.

The alternative is to make the switch small and dull. You move data ahead of time, keep both systems in step, prove that they agree, and change only where the applications point. Nobody can honestly promise zero downtime before seeing your systems. What you can do is plan so that downtime has few places left to come from.

Inventory and profile before you design

A database that has run for decades holds more than tables. It holds stored procedures, scheduled jobs, triggers, views, file drops, reports and spreadsheets that read from it directly, and integrations nobody has documented. List all of them. Query logs and job schedulers tell you what is really used. People's memory does not.

Then profile the data itself. Count rows and volumes per table, measure growth and daily change, and look for the things that break migrations: missing keys, duplicate records, dates stored as text, old character encodings, columns whose meaning changed over time, and codes with no reference table. For each problem, decide if you will fix it in flight, carry it across as it is, or leave that data in an archive.

• Every object, job and interface that touches the database
• Real consumers, taken from logs
• Volumes, growth and daily change per table
• Known data defects and the decision for each
• Data that can be archived instead of moved

Run old and new side by side

Build the new platform and its pipelines while the old system keeps serving production. Start with a bulk load of history. Then keep the new side current by capturing ongoing changes from the old one, so that the gap between them is small by the time you cut over.

Run the new side in shadow first. Send it the same reporting queries and batch jobs, compare the outputs, and watch performance under real load. Move consumers in waves, starting with those that only read: reports, analytics, downstream extracts. Systems that write data move last, because two places accepting writes is the hardest state to keep consistent. Keep the parallel period long enough to cover a month-end, or any other cycle where unusual jobs run.

For a leading insurance group, we migrated databases dating from 2003 to a modern, scalable ETL pipeline and architecture, at petabyte scale. The transfer was complete, with zero production downtime, within 4 months of project start. That is one engagement's result. Your own plan depends on what your inventory finds.

Reconciliation: prove that the two sides agree

Parallel running is only useful if you check it. Reconciliation is the automated comparison of old and new, run on every load and reported to named people.

Layer the checks. Row counts per table catch missing loads. Sums on amounts and quantities catch truncation and type errors. Hashes on rows catch silent changes in content. Business-level checks, such as customer counts by status or balances by account, catch errors in transformation logic that technical checks miss.

Agree tolerances in advance. For most financial fields the tolerance is zero, and every difference needs an explanation, even when the explanation is a known defect in the old data.

• Row counts per table and per load
• Control totals on numeric fields
• Row-level hashes on sampled or full data
• Business reports compared side by side
• A log of every difference and how it was resolved

Plan the cutover and the rollback together

Write the cutover as a runbook with timed steps, an owner for each step, and a check after each one. Rehearse it more than once on production-sized data. The rehearsal tells you how long each step really takes and where the runbook is wrong.

Define the rollback with equal care. Decide what triggers it, who can call it, and the last moment at which it is still possible. Once the new system accepts writes, going back means carrying those writes to the old system. Either keep a reverse sync running for a set period, or accept a point of no return and say so openly.

Pick a low-activity window, freeze schema changes ahead of it, and have the people who know the old system available on the day. Keep the old system intact and readable until the business confirms it is no longer needed.

• A rehearsed runbook with owners and timings
• Written rollback triggers and a named decision-maker
• A stated point of no return
• The old system kept readable after cutover

Who must sign off

Name the people who sign off at the start, and tell them what evidence they will receive. Sign-off works when each owner accepts a specific thing against written criteria. A general approval in a meeting is worth little when someone questions a number three months later.

Put the go or no-go decision in the hands of one person, and hold a formal checkpoint before cutover where each owner states their position.

• Data owners: reconciliation results for their domain
• Application owners: their systems tested on the new platform
• Operations: monitoring, backup and restore proven
• Security and compliance: access and retention reviewed against your privacy and compliance requirements
• Executive sponsor: the final go or no-go

What to do next

Start the inventory now, before you choose a target platform. List consumers from logs, profile the largest tables, and write down the defects you find. That document makes every later estimate more honest. If you want a second opinion on the plan, talk to our data engineering team.