Zero Downtime Database Migration: Step by Step

Zero downtime database migration: step by step

A database migration does not have to mean a long maintenance window. The idea is simple: build a copy of the database in the new place, keep it in sync with the old one while the business keeps working, prove the copy is correct, and only then switch the application. The downtime shrinks from a whole maintenance window to the time the switch itself takes. This guide describes the steps we use with Repstance, for example when moving from Oracle or SQL Server to PostgreSQL, Amazon Aurora, MySQL or Snowflake, or from a data center to AWS or Azure.

1. Plan and convert the code

If the new database is a different platform, the schema, procedures and application code have to be converted first. This is done with a schema conversion tool such as AWS Schema Conversion Tool, plus manual conversion of what the tool cannot translate. Repstance does not convert code; it migrates and replicates the data. Decide at this stage the data type mapping, object naming and any transformation rules (Repstance applies them during replication), and check which tables have a primary or unique key, because the data comparison needs one.

2. Prepare the source and the target

Enable change capture on the source (Oracle redo, SQL Server CDC, and with Repstance Advanced Edition also PostgreSQL and MySQL), create the empty target schema and install Repstance on a server that can reach both databases. All targets except Oracle and SQL Server need Repstance Advanced Edition.

3. Initial load

Repstance reads the existing data from the source and loads it into the target while the source stays online. Changes made during the load are captured at the same time, so nothing is lost. In our benchmark Repstance read 228,000 rows per second from Oracle, and loaded 112,000 rows per second end to end into Aurora PostgreSQL.

4. Continuous replication

When the initial load is finished, the captured changes are applied to the target and replication continues in real time. The lag between the two databases can be checked at any time. In the benchmark the average lag was 11 seconds (19 seconds maximum) at 4,250 transactions per second. The databases can stay in sync for as long as you need, so there is time to test the application on the new database.

5. Compare the data

Do not trust a copy you have not checked. When the lag reaches zero, run a full comparison of source and target. After that, run an incremental comparison, for example daily, that checks only the data changed since the previous comparison, so it stays fast. Differences can be repaired with the built-in repair. See Data validation and repair.

6. Cutover

Stop the application, or switch it to read-only.
Wait until the lag is zero.
Run the last incremental comparison and repair any difference.
Point the application to the new database and start it.

These four steps are the only downtime.

7. Keep a way back

With Repstance Advanced Edition, replication can run in the opposite direction after the cutover, from the new database back to the old one. If a legacy application fails to work with the new database, it can still work with the old one, and the old database does not fall behind. This is how one of our Fintech customers migrated Oracle RAC to PostgreSQL: see the case on the home page.

8. Decommission

When the new database has run in production long enough and nothing depends on the old one, stop the reverse replication and retire the old database.

Where to read more

Planning a migration? Contact us and we will help you plan it. Support is included during GMT business hours; 24/7 support is available as a paid Enterprise option (sales@repstance.com).