A Step‑by‑Step SOP for Seamless Business Data Migration

Why Business Data Migration Is Inevitable

The author, a Java specialist, explains that during system iteration business models often evolve (entity relationships change) or storage structures shift (fields move from relational databases to NoSQL stores such as HBase, text compression, or aggregation granularity adjustments). These changes require modifications to the persistence layer and migration of existing data, which introduces high risk of data inconsistency.

To achieve a smooth, non‑disruptive migration, every step must support gray‑scale rollout and data/monitoring comparison. (Note: the article focuses on business data migration; sharding migrations can use middleware like Canel.)

Standard SOP Overview

Incremental Dual‑Write For same‑database migrations, write to the old and new tables within a single transaction to guarantee consistency. For cross‑database or DB‑to‑NoSQL migrations, first write to the old table, then to the new one, and implement retry logic for failures.

Back‑fill Historical Data After the dual‑write flag is fully enabled, the new and old tables stay consistent for new writes, but existing records still reside only in the old table. Export all business IDs, read each record from the old table, and write it to the new table so that every historic field is populated.

Full Data Comparison to Ensure Consistency The author proposes two comparison methods:

Reuse the back‑fill job with a switch that disables writes and performs only comparison.

Run an offline reconciliation, referencing a previous article on real‑time vs. offline reconciliation.

SQL examples illustrate how to compare row counts and detect mismatches:

-- (1) Compare row counts
select count(1) from table_a;  -- left table count
select count(1) from table_b;  -- right table count

-- (2) Compare data content
-- (2.1) Rows only in left table (left join, right side null)
select *
from table_a
left outer join table_b on table_a.biz_field = table_b.biz_field
where table_b.biz_field is null;

-- (2.2) Rows only in right table (right join, left side null)
select *
from table_a
right outer join table_b on table_a.biz_field = table_b.biz_field
where table_a.biz_field is null;

-- (2.3) Rows with differing field values (inner join, field comparison)
select *
from table_a
inner join table_b on table_a.biz_field = table_b.biz_field
where (table_a.field_1 <> table_b.field_1
   or table_a.field_2 <> table_b.field_2
   or table_a.field_3 <> table_b.field_3);

Incremental Dual‑Read Comparison Full‑data comparison only verifies historic data. To guarantee that incremental (new) data remains consistent, every query scenario should read from both old and new tables and compare the results. Embedding this logic in a shared query layer prevents missed checks.

Switch Reads to the New Table Once dual‑read checks confirm consistency, gradually shift query traffic to the new table in a gray‑scale manner. Simultaneously ensure that all offline jobs and dependencies are redirected to the new data source.

Stop Writing to the Old Table Before decommissioning the old table, verify that no online services query it and that no offline jobs depend on it. Then cease all writes to the old table.

By following these six steps, the author asserts that business data can be migrated smoothly with controllable risk.