Choosing Offline or Online Database Migration with a Decision Algorithm

Database migration conversations often start with a tool name. An organization already owns a replication product, someone prefers an export utility or a team has used a particular cloud service before.

I prefer to start with three business questions:

How long can the application be unavailable?

How much data must move?

How will we return to the existing database if the move fails?

The answers help us choose a migration approach and then the tools needed to carry it out.

I’ve developed two models that I believe can help enterprises address these concerns.

• The Simple Model checks whether copying all the data could fit within the allowed interruption.
• The Advanced Model checks whether offline or online migration can meet the cutover and rollback requirements.

Let’s not over sell this, a decision algorithm is simply a repeatable set of steps for making that choice. These models work across different suppliers and database platforms. Their results depend on the assumptions and measurements entered.

Understand The Two Migration Approaches

The source is the existing database. The target is the database receiving the data. Writes are additions, updates and deletions, such as creating an order or changing a customer address.

Cutover means switching the application to the target. Rollback means returning it to the source if the migration cannot proceed successfully.

Bulk loading, or a full load, means copying the existing data. Change data capture (CDC) records subsequent changes so they can be applied to the target. Online migration usually needs both.

For example, a customer updates an address while the initial copy is running. CDC captures that update so the target does not keep an outdated address.

Both approaches need a consistent copy: related records must agree at a defined point in time. They also need validation, which means checking the required data and application behavior.

In this post, downtime is the interruption from stopping source application writes until the target application is available for normal use. Online migration can shorten that interruption, but does not automatically eliminate it.

The Simple Model

The Simple Model answers one question:

Could we copy all the data and complete the cutover within the allowed downtime?

Step 1: Enter the business requirements

Start with the four input groups:

GB means gigabytes. This post uses 1 GB = 1 billion bytes and 1 TB, or terabyte, = 1,000 GB. Count the data being copied, rather than unused storage reserved for future use.

A millisecond (ms) is one thousandth of a second. The round-trip network delay is sometimes called RTT, meaning round-trip time.

The preferred approach is a preference. It does not override the requirements.

Step 2: Add explicit planning assumptions

To estimate copy time, we need an assumed data movement speed. Network delay alone cannot provide that speed.

Bulk throughput means how much data the complete process can read, move and load each minute. At 10 GB/minute, copying 100 GB takes 10 minutes.

This is different from bandwidth, the network connection’s data-carrying capacity. Source reading, target loading and how the tool groups and processes records also affect the complete migration speed.

For a first screening, enter these three assumptions:

A planning factor of 1.25 adds 25 percent to the total estimated interruption. A 20-minute estimate becomes 25 minutes.

These example values are not universal defaults or vendor performance claims. Replace them with assumptions suitable for the project.

Other cutover work can vary with database size and complexity. Include only work done during the interruption. Work safely completed beforehand does not consume the downtime budget.

Step 3: Calculate the expected interruption

Use three short calculations:

Copy time = Data volume / Bulk speed
Time before planning allowance = Copy time + Other cutover work
Estimated offline downtime =
    Time before planning allowance × Planning factor

Compare the result with the cutover time limit.

An offline failure does not prove online will work. The Advanced Model evaluates that separately.

Step 4: Calculate the speed we would need

This calculation makes the requirement concrete:

Time available for the copy =
    (Cutover time limit / Planning factor) - Other cutover work
Required bulk speed =
    Data volume / Time available for the copy

If the available time is zero or negative, the assumed operational work leaves no time for the full copy. Review the activities and time limit before evaluating offline migration further. Online migration needs its own estimate for operational work.

A Simple Example 🤔

Assume 1,000 GB, a 30-minute cutover limit, 10 minutes of other work, a planning factor of 1.25 and bulk speed of 10 GB/minute.

The result is easy to state:

Offline migration does not fit the 30-minute limit at the assumed speed. Evaluate online migration or demonstrate at least 71.43 GB/minute bulk throughput. Rollback within 15 minutes still needs to be verified.

A 40 ms network delay tells us the conditions under which to test that speed. It does not determine the speed or automatically select online migration.

The Simple Model deliberately leaves rollback unresolved: a time limit does not explain how recovery will work.

The Advanced Model

The Advanced Model answers a broader question:

Can either approach meet the cutover requirements and provide an acceptable return to the source?

It uses measured performance, confirmed tool capabilities and a separate recovery plan for each approach.

Step 1: Measure the data movement

A backlog means changes waiting to be processed. Write freeze means the point at which source writes stop.

Use measurements from a rehearsal, a practice migration that reflects the actual workload and network. Busy periods matter because changes can arrive faster than the daily average.

Measure rates and backlogs on a comparable basis. Compressed transfer bytes and database change-log bytes may differ for the same activity.

If export, transfer and import happen one after another, include all three durations. Export reads data out, transfer moves it between environments and import loads it into the target. If they overlap, measure the combined process. Avoid counting work twice.

Step 2: Check offline cutover

Use the same calculation as the Simple Model, replacing assumed speed with measured speed:

Copy time = Data volume / Measured bulk speed
Offline downtime =
    (Copy time + Other offline cutover work) × Planning factor

Offline cutover passes when the result fits the cutover limit.

Required preparation may include building indexes, structures that speed up searches, and checking constraints, rules that prevent invalid data. Include this time if the work happens during the outage.

Step 3: Check whether CDC can keep up

The source generates new changes while CDC processes them. Processing capacity must exceed the incoming change rate:

CDC spare capacity = CDC capacity - Source change rate

In our example:

2 - 0.5 = 1.5 GB/minute of spare capacity

That spare capacity can clear changes that have accumulated.

If capacity equals the source change rate, there is no spare capacity to clear an existing backlog. If capacity is lower, the backlog grows while writes continue.

With reasonably steady rates:

Time to clear an existing backlog while writes continue =
    Existing backlog / CDC spare capacity

Use that calculation only when spare capacity is greater than zero. The backlog during preparation may differ from the remaining backlog at final write freeze.

Step 4: Calculate online cutover

Once source writes stop, CDC can focus on the remaining changes:

Time to finish remaining changes =
    Remaining source changes / CDC capacity
Online downtime =
    (Time to finish remaining changes + Other online cutover work)
    × Planning factor

For our example:

Online cutover fits the 30-minute limit, provided the capacity and capability checks pass.

Count all unapplied changes, including those still waiting to be captured. An empty apply queue does not establish that the target contains every source change.

The initial full copy still takes time before cutover. The plan must retain change logs, database records used to track changes, and have storage for changes waiting to be applied. The initial copy’s duration and the final backlog require separate evaluation.

Step 5: Check rollback

Before the target accepts writes, returning to the source may involve checking the retained source and switching the application back.

After the target accepts writes, the source may be missing new orders, payments or other activity. The recovery plan must deal with that difference.

Reverse replication copies target changes back to the source. Reconciliation means restoring the required target changes so the source is ready for the application to return. A tool that replicates forward does not automatically support recovery in the opposite direction.

Enter separate values for each approach:

The permitted loss of recent activity is often called the recovery point objective (RPO). An RPO of zero requires preserving all committed changes. An RPO of five minutes permits losing up to five minutes of recent activity.

Allowed data loss is different from recovery duration. Returning in ten minutes does not tell us how much data was lost.

Calculate:

Time to restore target changes =
    Target changes to restore / Restore speed
Rollback downtime =
    (Restore time + Other rollback work) × Planning factor

If no changes need restoring, restore time is zero and a restore speed is unnecessary. The source must still be ready to resume service.

Suppose 2 GB must be restored at 1 GB/minute, with 6 minutes of other work:

(2 / 1 + 6) × 1.25 = 10 minutes

That fits the 15-minute rollback limit. Recovery passes only if it also meets the data-loss limit, remains available for the required coverage period and uses a confirmed recovery method.

For example, two hours of required coverage means recovery must remain possible for two hours after cutover. It does not mean recovery may take two hours.

Step 6: Make the decision 🤯

Use three statuses:

• Feasible: all modeled requirements pass using measurements and confirmed capabilities.
• Not feasible: at least one known requirement fails.
• Unknown: evidence is missing and no known failure already settles the result.

Capability checks confirm that the tools support both databases, the data and operations in scope, correct copying, required change capture and application readiness.

Our example gives 137.5 minutes for offline cutover, 15 minutes for online cutover and 10 minutes for the example online rollback.

Online meets the modeled time limits. It becomes an overall feasible result only after the capability, performance, data-loss and coverage evidence is confirmed.

Watch the explainer video

Try the online calculator

You can try both models in the OraMatt Database Migration Calculator.

Start with the Simple Model to see whether the full copy could fit within your cutover budget. Switch to the Advanced Model to compare offline and online cutover, CDC capacity and separate rollback plans. Each input includes a plain-language explanation, and the results show which checks pass, fail or still need evidence.

Use Load example to explore the numbers from this article, Clear inputs to enter your own values or Print results to keep a copy. Calculations run in your browser without sending or saving your input values.

An example or assumed processing rate is a starting point. Confirm the relevant measurements and recovery capabilities before treating an approach as feasible.

Use the accompanying spreadsheet 🧮

Prefer to work in Excel? Download the companion workbook, or visit the Migration Decision Models GitHub repository for the spreadsheet, calculator source and README instructions.

The workbook contains three worksheets:

Yellow cells are editable. Shared requirements on Advanced link to Simple, so enter them once.

The workbook opens with the article’s example numbers. Performance evidence is marked Assumed, and capability confirmations are Unknown. This lets readers explore the calculations without presenting an illustrative example as a proven migration plan.

Replace example numbers with project evidence. Select Measured only after representative tests support the processing rates. Missing inputs remain unknown rather than becoming zeros.

Apply the distinction to ongoing replication

Migration moves an application to another database. Replication keeps a copy updated and may continue indefinitely without cutover.

For ongoing replication, add a maximum permitted replication lag, meaning how far behind the source the target may be. A scheduled bulk refresh may meet a daily freshness requirement. CDC may be appropriate when changes must arrive continuously.

The workbook evaluates migration cutover and rollback. Ongoing replication additionally needs tests of freshness, busy-period lag and recovery after interruption. CDC capacity above the source change rate helps, but does not establish a maximum delay.

Choose tools that meet the requirements

Once an approach passes the assessment, evaluate tools for the capabilities the plan needs: correct copying, adequate speed, supported changes, monitoring, restart after interruption and recovery to the source.

Moving between different database technologies is a heterogeneous migration. It may also require schema conversion, adapting database structures, and application remediation, changing application code for the target. Successful data movement does not prove that the application works correctly.

Start with the Simple Model to identify the speed and interruption requirement. Use the Advanced Model and a representative rehearsal to establish whether the migration and recovery plan can meet it.

Choose the migration approach that meets the business requirements then choose the tools that can demonstrate it.