How do you move an entire database from your own server to the cloud without shutting down your business for even an hour? That is the problem of continuous migration and cutover, and it is a critical skill for any database engineer. For the PCDE exam, you need to know not just how to start a migration, but how to keep old and new systems in sync, validate that nothing got lost, and perform the final switch with zero downtime.
Jump to a section
A simple way to picture Managing Ongoing Data Migration and Cutover
Have you ever tried to move houses while the new place was still being decorated? That messy overlap is exactly what managing ongoing data migration and cutover feels like. You need to move all your furniture (your data) from your old house (your old database) to a new, renovated one (a cloud database), but you can't just dump everything on the lawn. You also have to keep living in the house while the move happens. So you hire a moving truck (the continuous data migration service) that shuttles boxes back and forth. But here's the trick: you don't just move everything once. While you are moving, you still receive new mail, buy new books, and order takeaway — new data is constantly being created at the old house. If you just moved all the old boxes and then stopped, you would lose all that new activity. So, the moving truck has to keep running in a loop, checking for any new items on the front porch and zooming them to the new house. This is called 'ongoing replication'. The 'cutover' is the final, terrifying moment when you hand back the keys to the old house. You unplug the moving truck, announce 'we are live', and everyone has to walk through the new front door without tripping. If you cutover too early, you lose everything that hasn't arrived yet. If you cutover too late, you pay double rent. The perfect cutover is a timed, tested switch, where you stop all activity at the old house for a few seconds, let the truck make one final, frantic dash, and then lock the door forever.
This is not a one-time dump of files. It is a careful, overlapping operation where old and new systems run side-by-side until you are absolutely sure the new house is ready for your family to live in, and that no package is left behind on the porch.
Continuous data migration is the process of moving data from a source database (your old system, often called 'on-premises' because it sits in your own server room) to a target database (usually in Google Cloud, like Cloud SQL or Spanner) while the source database is still being used by live applications. This is different from a 'one-shot' migration where you take the whole system offline, copy everything, and then start it again. A one-shot migration works for small, non-critical systems, but for any real-world business that needs 24/7 uptime, that approach is devastating.
So, how does continuous migration work? The core technology is called replication. Replication means the target database continuously copies every single change that happens in the source database, almost in real time. Think of it like a direct fibre-optic cable between the two systems. When a customer buys something on your website, a new row is added to the 'orders' table in the source database. Replication catches that new row and writes it into the target database. The two databases are kept 'in sync' — they contain the same data at all times, within a few seconds of delay.
There are two main ways to do this:
Homogeneous migration: The source and target are the same type of database engine. For example, you are moving from MySQL (running on your own server) to Cloud SQL for MySQL. Because they speak the same language, replication is easier and often uses built-in features like ‘replication slots’ that guarantee no data is lost.
Heterogeneous migration: The source and target are different engines. For example, you are moving from Oracle to Cloud Spanner. This is much harder because the databases have different structures, different SQL dialects, and different ways of handling transactions. You often need a special tool, like Google’s Database Migration Service (DMS) or a partner tool like Striim or Informatica, that translates the data on the fly.
Why does this exist? Why not just export a file and import it? The answer is downtime. Traditional export/import (known as ‘bulk copy’) requires you to stop all writes to the source database to ensure you get a consistent snapshot — a picture of the data at a single moment. If you have an e-commerce store, stopping writes for two hours means no one can place an order. Continuous migration avoids this by using Change Data Capture (CDC).
Change Data Capture (CDC) is the most important concept here. CDC reads the transaction log of the source database — a behind-the-scenes diary that records every INSERT, UPDATE, and DELETE operation. Instead of scanning the whole table (which is slow and blocks other users), CDC just streams those log entries to the target. This is fast, efficient, and does not slow down the source database for normal users.
Once continuous replication is running, you eventually need to perform the cutover. Cutover is the point when you officially switch all your applications to use the new target database instead of the old source. A well-planned cutover is often a three-step process:
Validation: You run checks to ensure the data in the target database matches the source. You count rows, compare checksums (a fancy mathematical fingerprint of the data), and run sample queries. You also test your application against the target to make sure performance is good.
Drain: You tell the source database to stop accepting new writes. Usually, you put the application into maintenance mode for a few minutes, or you reconfigure the application to write to the target but still read from the source for old data. This phase is often called ‘the dry run’ or ‘final sync’.
Switch: You update your network connections (like DNS records or application configuration files) to point all traffic to the new target database. You stop the replication. The migration is complete.
If something goes wrong during cutover — for example, the target database is much slower than expected — you need a rollback plan. This means you can reverse the switch and point traffic back to the old source database. A good continuous migration strategy always plans for failure.
The PCDE exam expects you to know the specific Google Cloud tools for this: Database Migration Service (DMS) for homogeneous migrations (MySQL, PostgreSQL, SQL Server to Cloud SQL), and Migrate for Compute Engine or BigQuery Data Transfer Service for other scenarios. You also need to understand Cloud Spanner’s interleaved replication and how to manage replication lag — the delay between when a change happens on the source and when it appears on the target. If replication lag gets too high (say, more than a few minutes), you risk data loss during cutover.
Assess and Prepare the Source Database
Check the source database engine version, size, and write rate. Ensure network connectivity between source and Google Cloud (via VPN or Interconnect). Verify that the source database has transaction logs enabled for CDC (e.g., binary logging for MySQL).
Create the Target Database Environment
Provision a Cloud SQL instance (or Cloud Spanner instance) in Google Cloud with adequate storage and performance. Do not migrate any schema yet — the migration tool will handle the schema automatically for homogeneous migrations.
Start the Continuous Migration Job
Configure Database Migration Service (DMS) with the source and target connection details. DMS performs an initial full copy of all data, then automatically enables CDC to stream ongoing changes. Monitor the full copy progress and replication lag in the DMS dashboard.
Validate Data Consistency Continuously
Run row count comparisons and checksum checks on key tables periodically. Use tools like mysqldbcompare or custom scripts. Ensure that the target data matches the source data, and that no rows are missing or corrupted.
Perform Cutover Preparation
Schedule a maintenance window. Inform stakeholders. Set the application to read-only mode or redirect new writes to a queue to stop changes on the source. Verify replication lag drops to zero and stays there.
Promote and Switch Traffic
In DMS, click 'Promote' to stop replication and make the Cloud SQL instance writable. Update DNS records or application configuration to point all connections to the new Cloud SQL endpoint. Remove the application from read-only mode. Monitor application performance.
Monitor and Decommission
Keep the old source database running in read-only mode for at least 24-48 hours as a safety net. Monitor replication and application logs for errors. After confirming stability, shut down the source database and release its resources.
Imagine you are a database engineer for a retail company called 'ShopNow'. They run their entire e-commerce platform on a MySQL server in their own data centre (on-premises). The CEO decides to move to Google Cloud to save money and improve reliability. You are tasked with migrating the 2 terabyte orders database to Cloud SQL for MySQL, with no downtime.
Here is exactly what you do in a real-world scenario:
Assessment and planning: You first examine the source database. You check its size (2 TB), its version (MySQL 5.7), its uptime requirement (99.99%), and its active connection count (about 500 concurrent users at peak). You note that the database has a heavy write load — hundreds of new orders per minute. You decide a one-shot dump is impossible.
Setting up Database Migration Service: You go to the Google Cloud Console, open Database Migration Service, and create a new ‘Continuous’ migration job. You specify the source as ‘on-premises MySQL’ and the target as a new Cloud SQL instance (a pre-configured MySQL server in the cloud). DMS automatically sets up connectivity using a VPC peering or a VPN tunnel (a secure connection between your data centre and Google Cloud).
Full dump and continuous replication start: DMS first takes a full snapshot of your 2 TB database. This is a bulk copy that runs in the background and takes about 6 hours. Crucially, DMS does NOT stop writes during this phase. It starts CDC at the same time, so every new order that comes in during those 6 hours is being logged by CDC. When the full dump finishes, DMS applies the backlog of CDC changes to the target. Now both databases are in sync.
Validation and monitoring: You run a script that compares the row counts of the 50 most important tables between source and target. You also run a checksum comparison on a few sample tables. Everything matches. You monitor the replication lag metric in Cloud Monitoring. It stays under 2 seconds, which is excellent.
The cutover weekend: You schedule the cutover for Saturday at 2 AM, when traffic is lowest. You notify the CEO and the customer support team. At the appointed time, you put the ShopNow website into maintenance mode (a simple ‘We will be right back’ page). This stops all new orders. You then verify the replication lag is zero — meaning the target has processed every single change. You click the ‘Promote’ button in DMS, which stops replication and makes the Cloud SQL instance the primary, writable database.
Switching traffic: You update the DNS record for the database hostname (db.shopnow.com) to point to the new Cloud SQL IP address. You wait for DNS propagation (a few minutes). You take the site out of maintenance mode. Orders start flowing to the new database.
Rollback readiness: You keep the old on-premises server running for 48 hours, but in read-only mode. If something fails — say, a query that used to take 10 milliseconds now takes 10 seconds — you can reverse the DNS and point traffic back to the old server. The data written to Cloud SQL during those hours would be lost, but you have backups. Luckily, nothing breaks. After 48 hours, you decommission the old server.
In this scenario, the critical tool was Database Migration Service (DMS), which managed both the initial bulk copy and the ongoing CDC. The most stressful moment was the ‘promote’ action during cutover. If done incorrectly, you could lose the last few seconds of orders or corrupt the target database.
The PCDE exam tests ‘Managing Ongoing Data Migration and Cutover’ in several distinct question formats. Here is exactly what you need to know to pass those questions.
First, you will see scenario-based multiple-choice questions. A typical question reads: ‘A company is migrating a 10 TB PostgreSQL database to Cloud SQL for PostgreSQL with minimal downtime. The database receives 1000 writes per second. Which migration approach should be used?’ The correct answer is almost always ‘Use Database Migration Service with continuous replication and CDC’. The trap answers are often ‘Use pg_dump and pg_restore’ (one-shot, causes downtime) or ‘Use a manual export/import’ (same problem).
Second, the exam loves questions about cutover validation. They will ask: ‘Before performing the final cutover, what action must be taken to ensure data consistency?’ The correct answer is: ‘Verify that replication lag is zero and that the data in the target matches the source using checksums or row counts.’ A common trap is ‘Take a new full backup of the source’ — this is unnecessary and introduces extra downtime.
Third, you will encounter comparisons between homogeneous and heterogeneous migrations. The exam might ask: ‘Which additional challenge does a heterogeneous migration present compared to a homogeneous migration?’ The answer is: ‘Data type conversion and schema transformation are required because the source and target use different database engines.’
Fourth, questions about rollback strategies are very common. They might ask: ‘During a migration cutover, the target database experiences performance degradation. What is the most appropriate immediate action?’ The answer: ‘Rollback the cutover by redirecting application traffic back to the source database.’ The trap is ‘Tune the target database in place’ — you do not have time to tune during a live cutover.
Fifth, questions on replication lag and its meaning. They might ask: ‘If replication lag is 5 minutes during a continuous migration, what is the implication for the cutover?’ The answer: ‘The cutover cannot proceed safely because any data written in the last 5 minutes on the source has not yet been replicated to the target. You must wait for lag to reach zero.’ The trap is ‘The cutover can proceed but you will lose 5 minutes of data’ — this is incorrect because the cutover is designed for zero data loss.
Key concepts to memorise: - CDC (Change Data Capture): The method of reading transaction logs to capture ongoing changes without locking tables. - Database Migration Service (DMS): Google’s managed tool for continuous migration to Cloud SQL. - Replication lag: The time delay between source write and target write. Must be zero for cutover. - Promote: The action that stops replication and makes the target the primary database. - Rollback plan: The ability to return to the source database if cutover fails. - Homogeneous vs Heterogeneous: Same engine vs different engine migration. - Validation: Comparing row counts, checksums, or using tools like ‘mysqldbcompare’.
Trap patterns to watch for:
Confusing ‘continuous’ migration with ‘one-shot’ migration. The exam will give clues like ‘minimal downtime’ or ‘without stopping writes’ which should point you to continuous.
Thinking that validation only happens once at the end. It should happen continuously and at the final sync.
Assuming that cutover is immediate. It requires a formal process of draining, promoting, and switching.
Finally, the exam expects you to know the specific order of operations. You should be able to list, in the correct sequence: 1) Assess source, 2) Set up connectivity, 3) Start DMS with continuous replication, 4) Validate data, 5) Perform dry run cutover if possible, 6) Promote and switch, 7) Monitor, 8) Rollback if needed, 9) Decommission source.
Continuous migration uses Change Data Capture (CDC) to stream live changes from source to target without downtime.
Replication lag must be reduced to zero before you perform the final cutover to guarantee zero data loss.
Database Migration Service (DMS) supports homogeneous migrations to Cloud SQL for MySQL, PostgreSQL, and SQL Server, but not heterogeneous migrations.
Validation is not a one-time task — you must compare data row counts and checksums both during and at the end of migration.
A rollback plan is mandatory, and it involves redirecting traffic back to the source database, accepting the loss of any data written to the target after promotion.
Heterogeneous migrations require schema transformation and data type mapping, increasing complexity and risk.
Cutover is a multi-step process: drain writes, verify lag is zero, promote, update connections, monitor, and then decommission.
The main difference between one-shot and continuous migration is downtime: one-shot requires the source to be locked, continuous does not.
Cloud Monitoring and Logging are used to track replication lag and detect anomalies during the migration.
Test your cutover in a non-production environment first to identify pitfalls before the real switch.
Maintain the source database in read-only mode for a period after cutover to enable a quick rollback if needed.
These come up on the exam all the time. Here's how to tell them apart.
Continuous Migration (CDC)
Uses Change Data Capture to stream live changes continuously after initial full copy.
Allows source database to remain fully operational with zero downtime during migration.
Cutover can be delayed and requires replication lag validation.
Suitable for large databases and high-write systems.
One-Shot Migration (Bulk Copy)
Exports a static snapshot of the entire database at one point in time.
Requires locking writes on the source database, causing downtime.
Cutover is immediate after the import completes, no lag check needed.
Suitable for small databases or non-critical systems that can tolerate downtime.
Database Migration Service (DMS)
Managed service that automates full copy, CDC, and cutover.
Supports continuous replication with built-in monitoring and lag metrics.
Limited to homogeneous migrations (same engine to Cloud SQL).
Manual Export/Import (pg_dump, mysqldump)
Manual process requiring custom scripts and manual coordination.
Only supports one-shot dump and restore, no built-in CDC.
Works for any database engine but requires manual schema and data conversion.
Homogeneous Migration
Source and target use the same database engine (e.g., MySQL to Cloud SQL for MySQL).
Schema and data types are compatible, requiring minimal transformation.
Simpler to set up and less error-prone.
Heterogeneous Migration
Source and target use different engines (e.g., Oracle to Cloud Spanner).
Requires mapping and converting data types, SQL syntax, and schema structures.
More complex, often requiring third-party ETL tools and thorough testing.
Cutover (Promote)
Stops replication and makes the target the primary writable database.
Once promoted, new writes go only to the target.
Requires application traffic to be redirected to the target.
Rollback
Returns traffic to the source database if the cutover fails.
Data written to the target after promotion is lost upon rollback.
Requires keeping the source ready and running in read-only mode.
Mistake
Continuous migration means the data is copied again and again from scratch every few minutes.
Correct
Continuous migration uses Change Data Capture (CDC) to stream only the changes (new rows, updates, deletes) since the initial full copy, not a full re-copy.
People hear 'continuous' and assume it means re-doing the entire process repeatedly, which would be wasteful and slow.
Mistake
You can perform the cutover at any time as long as the initial full copy is finished.
Correct
You must wait until replication lag is zero and you have validated data consistency before cutting over, otherwise you risk data loss.
Beginners think the initial sync is enough, but live systems continue to change, so the target is only truly consistent when lag is zero.
Mistake
A heterogeneous migration (e.g., Oracle to Cloud Spanner) is just as easy as a homogeneous one (e.g., MySQL to Cloud SQL) if you use the right tool.
Correct
Heterogeneous migrations are significantly harder because they require transforming data types, SQL dialects, and schemas, which can introduce errors and require manual mapping.
Beginners underestimate the complexity of translating between different database 'languages'.
Mistake
Database Migration Service (DMS) can migrate any database to any Google Cloud service.
Correct
DMS only supports homogeneous migrations to Cloud SQL for MySQL, PostgreSQL, and SQL Server. Other databases (like Oracle to Spanner) require different tools like Striim or custom pipelines.
The name 'Database Migration Service' sounds universal, but its scope is limited.
Mistake
Once you click 'Promote' during cutover, you cannot go back to the source database.
Correct
Promote stops replication, but you can still rollback by redirecting traffic to the source database. However, any data written to the target after promotion will be lost if you rollback, so a rollback plan must account for this.
People think 'promote' is a one-way irreversible door, but it is reversible with a data loss trade-off.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
A one-shot migration stops writes to the source, copies all data at once, and then switches over, causing downtime. Continuous migration uses CDC to stream changes live, so the source can remain in use until the final cutover.
You check the replication lag metric in Database Migration Service or Cloud Monitoring. When the lag is zero, and you have validated row counts and checksums, the data is fully synced.
No. DMS only supports homogeneous migrations to Cloud SQL (MySQL, PostgreSQL, SQL Server). For Oracle to Spanner, you need a third-party tool like Striim or a custom ETL pipeline.
You should have a rollback plan. Redirect application traffic back to the old source database. Any data written to the target after promotion will be lost, so you may need to re-replicate those changes manually or accept the loss.
Ideally, you put the application into a maintenance mode or read-only mode for a few minutes during the final cutover to prevent new writes to the source while replication lag reaches zero.
CDC is a technique that reads the transaction log of the source database to capture every INSERT, UPDATE, and DELETE operation as it happens, without locking tables or slowing down the source.
You've finished Managing Ongoing Data Migration and Cutover. Continue through the PCDE study guide to build a complete picture of the exam.
Done with this chapter?