Design, migrate, and run scalable, highly available databases on Google Cloud across relational and NoSQL services, and track your readiness for the Professional Cloud Database Engineer exam.
Prepare and test your skills
Prepare and test your skills
Design and implement data migration and replication is the practice of moving schema, data, and application logic to Google Cloud while keeping production online and keeping a tested return path. The work connects a source such as Amazon RDS or on-premises databases to a target such as Cloud SQL (opens in a new tab), AlloyDB for PostgreSQL, Spanner, or BigQuery through managed replication, staged files, and private networks. Cutover switches reads and writes to the target only after lag is near zero and validation passes.
Online continuous replication versus scheduled maintenance is the choice about how much downtime the workload can afford. Continuous replication, also called trickle migration, keeps the source online while inserts, updates, and deletes stream to the target, and migration ends only when traffic switches, replication stops, and all reads and writes happen on the target. Scheduled maintenance, also called one-time or big bang migration, takes the database offline for backup, transfer, restore, and switch.
| Strategy | Downtime and risk | Cost, complexity, and fit |
|---|---|---|
| Scheduled maintenance | Requires downtime for the full cut-over window; failure restarts the process and extends downtime | Lower cost and little refactoring; best for small and infrequently used databases |
| Continuous replication | Near-zero downtime with lower data-loss risk because failure affects only one chunk | More planning and refactoring; best for mission-critical databases |
Databases on the same instance can use different strategies, such as scheduled maintenance for small databases and continuous replication for mission-critical ones.
Offline bulk transfer through Cloud Storage staging moves a full copy while the source is stopped during a planned window. Data flows from the source database to export files, from export files to a Cloud Storage bucket, and from the bucket to the target database. Choose this path when data volume is very large, bandwidth cannot meet the deadline, and the business can accept an extended outage.
Transfer planning must include bandwidth, approvals, and peak load. For example, 1 GB can move in eight seconds over 1 Gbps, while 100 TB can take about 12 days before overhead. When bandwidth is sufficient, use online upload with the gcloud storage command or Storage Transfer Service for on-premises data. When bandwidth cannot meet the deadline, use Transfer Appliance to ship hardware offline for loading to Cloud Storage.
Cutover monitoring and safe source cleanup is the controlled switch of traffic followed by validation before the old system is removed. After cutover, applications on the target are monitored for a defined period to decide whether the migration holds or fallback is needed. The target might need a restart if database flags change, and rolling back can take more effort than fixing forward, so both paths must be weighed.
Fallback protection depends on keeping the old source usable and current so writes on the target can replicate back with minimal loss. Before cleanup, the team creates a final backup of each source, collects source parameters and statistics to compare with the target, keeps at least 20% available storage on the target, and creates a metrics-based alert for disk use. An administrative operation must never start before the previous operation completes.
Reverse replication from Google Cloud to the source is the fallback flow that streams writes from the new cloud target back to the original database after cutover. Data flows from the Google Cloud target through a Virtual Private Cloud (VPC) network and across a hybrid or cross-cloud connection to the source environment. The source stays in sync so applications can point back with minimal downtime if the target has issues, following the same cutover and fallback (opens in a new tab) principle that the return path must handle type changes and route rows correctly.
| Connection option | Best for | Key characteristic |
|---|---|---|
| Cross-Cloud Interconnect | High-throughput links between Google Cloud and another cloud | Direct connection up to 100 Gbps per link |
| Dedicated Interconnect | Private on-premises connectivity with high bandwidth | Requires two locations in the same region but different edge availability domains |
| Partner Interconnect | Private connectivity through a partner facility | Traffic transits the partner then the Google backbone |
| High Availability Virtual Private Network (HA VPN) | Lower-bandwidth or initial hybrid links | VPN tunnels that can achieve 99.99% uptime SLA |
A Transit VPC in a hub project can land external connections and route between application VPCs, services VPCs, and on-premises networks. Cloud Router with Border Gateway Protocol exchanges routes, using duplicate connections as active/active with Equal Cost Multi-Path routing or active/passive for failover. Firewall rules and Cloud DNS forwarding must allow replication traffic and name lookups in both directions. For SQL Server failback, only Private Service Connect interfaces are supported to reach the original instance, even when forward migration used VPN or Interconnect.
MySQL and PostgreSQL change capture for reverse replication relies on logs that record committed row changes. MySQL uses binary logs with row-based logging and global transaction identifier (GTID)-based replication, while PostgreSQL uses logical decoding of the write-ahead log (WAL) with pglogical and replication slots. Both mechanisms require logging to be enabled and retained before a replica connects.
A MySQL source must meet these requirements before replication can start:
A PostgreSQL source follows an ordered sequence to enable logical replication:
postgres database if it does not exist.shared_preload_libraries to include pglogical.wal_level to logical.wal_sender_timeout to 0 to disable termination of inactive connections.max_replication_slots to the maximum number of slots the source will serve.CREATE EXTENSION IF NOT EXISTS pglogical in every database to be migrated.If the source is Amazon RDS, set rds.logical_replication to 1. Tables without primary keys migrate only the initial snapshot and INSERT statements; UPDATE and DELETE statements must be migrated manually unless a primary key is added.
Fallback options define how applications return to a current copy if the cloud target fails. The source instance is kept online and kept in sync after cutover, and a fallback plan must be treated as a full migration with its own planning, success criteria, timeboxes, and testing. Replication lag must be monitored until the source matches the target, and row counts and content must be validated before applications point back.
| Strategy | How fallback works | Choose when |
|---|---|---|
| Simple fallback | Application switches back to the original source, optionally restoring a target backup to the source | Downtime is acceptable or target writes do not need to be kept |
| Reverse replication | Writes on the new target replicate back to the original source after cutover | Source can be kept and migration uses continuous replication |
| Forward replication | Writes on the new target replicate to a third instance that can serve reads | Coverage is needed at all times or the source must be discarded soon |
| Duplicate writes | Application writes to both source and target for gradual cutover | No-downtime return justifies refactoring or a data-access microservice |
When AlloyDB for PostgreSQL is the destination, a useful alternative is to replicate changes to a Cloud SQL for PostgreSQL instance and use that instance as the fallback destination. Database Migration Service failback jobs perform only change data capture with no full backfills and replicate only tables from the original job.
Exam tip: Reverse replication flows from the target back to the original source and cannot be end-to-end tested until after cutover, while forward replication flows from the target to a third instance and can be fully tested in advance.
Coordinating moves with Database Migration Service (DMS) is the managed way to run homogeneous online moves for MySQL, PostgreSQL, and SQL Server. A DMS migration job pairs a source connection profile, which stores connectivity for the source, with a destination instance profile, and access is controlled by Identity and Access Management. DMS combines an initial load with continuous replication to keep the destination in sync for minimal-downtime cutover.
For SQL Server, data flows from source backups and transaction logs to a Cloud Storage bucket that DMS monitors, then to the destination Cloud SQL for SQL Server instance. The destination must be empty with no user databases, tables, or functions, and its version must be the same as the source or one major version above. One-time moves are not supported in DMS, so one-time moves use engine-native dump and load through Cloud Storage, such as pg_dump and pg_dumpall for PostgreSQL with parallel jobs and compression. After promotion to primary, Cloud SQL resets backup settings to defaults, so custom backup locations must be configured again.
Converting schema and application logic translates structure and behavior so code runs correctly on the target. Data Definition Language (DDL) creates and changes objects such as tables, views, indexes, functions, triggers, and procedures, while Data Manipulation Language (DML) reads and changes rows inside those objects. Homogeneous migration keeps the same engine, such as Amazon RDS for SQL Server to Cloud SQL for SQL Server, and focuses on parameters, extensions, authentication, and availability differences. Heterogeneous migration changes engines, such as Oracle to Cloud SQL for PostgreSQL, and requires full remapping of data types and rewriting of proprietary syntax.
Assessment starts with inventory of engine version, CPU and memory, topology, backup policies, parameters, and customizations. Three tools divide this work: Migration Center for landscape, sizing, and cost reporting; migVisor for flagging incompatible artifacts and recommending a target and phased plan; and the Database Migration Assessment Tool for reporting features, objects, and effort. For Oracle moves, DMS conversion workspaces convert Oracle DDL to PostgreSQL syntax, then a migration job performs full load and ongoing capture, including failback to AlloyDB (opens in a new tab) or failback to Cloud SQL for PostgreSQL (opens in a new tab) when those targets are used. All related profiles, jobs, databases, and workspaces must reside in a single region because DMS is fully regional. Verification confirms DDL objects exist and DML results match through pg_restore output, PostgreSQL logs, row counts, sample queries, and tests of views, functions, triggers, and procedures. Use pg_dumpall when users and roles must move, because pg_dump does not include them. Built-in backups fit databases up to 500 GB, and Cloud Storage supports a maximum single-object size of 5 terabytes, so larger exports must be split.
Choosing tools for warehouses, Spanner, and homogeneous moves means matching the source engine, data size, and transformation needs to the service that moves schema and data with the least friction. BigQuery (opens in a new tab) Data Transfer Service is the fully managed service for automated batch transfer from Amazon Redshift and Teradata into BigQuery, still using Cloud Storage behind the scenes to stage data. For other warehouses, the pattern is to extract to files, upload to Cloud Storage, then load with transfer service, Storage Write API, or batch load. The recommended practice is to transfer schema unchanged first so feeding pipelines keep working, then evolve to nested fields and clustering later.
| Pattern | Where transformation happens | Use when |
|---|---|---|
| Extract and load (EL) | No transformation; source to Cloud Storage to BigQuery | Schema moves unchanged and speed matters most |
| Extract, transform, and load (ETL) | Transformation in Dataflow, Data Fusion, or Spark before BigQuery | Data must be cleaned or reshaped before loading |
| Extract, load, and transform (ELT) | Data lands in BigQuery staging, then SQL transforms it | BigQuery itself can do the reshaping with SQL |
Dataflow, built on Apache Beam, is the serverless service for complex in-flight transforms such as joining datasets, filtering sensitive fields, converting types, or enriching records. Data flows from the source or staging bucket into a Dataflow pipeline, the pipeline applies transforms, and the BigQuery connector writes to BigQuery tables. Cloud Data Fusion builds graphical ETL/ELT pipelines, while Managed Service for Apache Spark runs Spark and Hadoop with connectors to Cloud Storage and BigQuery. Migrations to Cloud Spanner require redesign for keys and distribution, handled by the Spanner migration tool for assessment, schema conversion, and data migration. Datastream reads bulk data and change events, bulk migration moves large MySQL datasets via Dataflow, and live migration combines Datastream and Dataflow for minimal downtime. If a MySQL or PostgreSQL dump sample is less than 100 GB, the tool can load it in proof-of-concept mode; if more than 100 GB, export to Cloud SQL and use bulk or minimal-downtime migration.
Online change capture versus offline backup and restore decides whether the source stays usable while data moves. Online migration with change data capture (CDC) captures only inserts, updates, and deletes after the initial snapshot, so the source keeps accepting writes. Offline migration copies a point-in-time image while the source is stopped and requires longer downtime.
Offline moves use engine backups, flat files such as CSV or JSON, SQL Server Integration Services, or snapshot replication. Striped backup over 10 files is used for databases larger than 5 TB. Recovery time objective measures allowable downtime and recovery point objective measures tolerable data loss; offline moves have higher values for both because writes must stop before the final copy. Storage Transfer Service copies staged files from Amazon S3 to Cloud Storage, while Transfer Appliance ships data offline when bandwidth cannot meet the deadline. Cloud Monitoring tracks replication lag and target sizing, because an undersized target causes long migrations and slow applications.
Continue with this study guide: Design and implement data migration and replication
Last updated