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
Online migration with continuous replication keeps the source database online while changes flow to the Google Cloud target. The source, such as Amazon RDS or Amazon Aurora, continues to serve reads and writes while Cloud SQL (opens in a new tab) or AlloyDB for PostgreSQL (opens in a new tab) catches up. The migration is complete only when traffic switches to the target, replication stops, and all reads and writes happen on the target. This pattern is also called trickle migration because work moves in small chunks.
Online continuous replication versus scheduled maintenance is a choice about how much downtime the workload can afford. Scheduled maintenance, also called one-time or big bang migration, takes the database offline to back up, transfer, restore, and switch applications. Continuous replication is required when the workload cannot afford downtime or cannot afford the cut-over window of backup, transfer, restore, and switch.
| Strategy | Downtime and risk | Cost, complexity, and fit |
|---|---|---|
| Scheduled maintenance | Requires downtime for the full cut-over window; if migration fails, the process restarts and downtime grows | Lower cost and complexity with little application refactoring; best for small and infrequently used databases |
| Continuous replication | Near-zero downtime with lower risk of data loss because failure affects only one chunk | More planning, time, and application refactoring; best for mission-critical databases |
Strategies can differ even for databases on the same instance. A common mix is scheduled maintenance for small databases and continuous replication for mission-critical databases.
Exam tip: Choose continuous replication when you cannot afford any downtime or cannot afford the cut-over window of backup, transfer, restore, and switch; choose scheduled maintenance only when that window is affordable.
Offline bulk transfer through Cloud Storage staging moves a full copy while the source is stopped during a planned maintenance 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, network bandwidth cannot meet the deadline, and the business can accept an extended outage.
The workflow runs in order:
Transfer time must include bandwidth efficiency, approval time, and peak-hour 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 (opens in a new tab) to ship hardware offline for loading to Cloud Storage.
Cutover monitoring and safe source cleanup is the controlled switch of production traffic followed by validation before the old system is removed. After cutover, applications using 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. In a fallback, writes on the target replicate back to the original source, so traffic can return with minimal data loss. Before cleanup, the team creates a final backup of each source, collects source parameters and statistics to compare with the target, configures a maintenance window for the primary instance, keeps at least 20% available storage, 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 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 to the source with minimal downtime if the target has issues.
MySQL and PostgreSQL change capture for reverse replication relies on source-side 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.
Private network paths for returning writes to source are encrypted VPC and hybrid connections that carry replication traffic without using the public internet. Cross-Cloud Interconnect (opens in a new tab) provides high-speed links between clouds with up to 100 Gbps per link, while Dedicated Interconnect and Partner Interconnect connect through colocation facilities. High Availability Virtual Private Network (HA VPN) uses VPN tunnels for lower-bandwidth needs or to start before upgrading.
| 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 Partner Interconnect then the Google backbone |
| 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 between domains, with duplicate connections as active/active using Equal Cost Multi-Path routing or active/passive for failover. Firewall rules and Cloud DNS forwarding must allow both replication traffic and name lookups in both directions. For SQL Server failback, only Private Service Connect interfaces are supported to reach the original instance.
Exam tip: SQL Server failback requires Private Service Connect even when forward migration used VPN or Interconnect, while reverse replication cannot be fully tested end-to-end until after cutover to the cloud target.
Validating parity and choosing how to fall back means confirming the source has caught up before moving workloads back. Replication lag must be monitored until the source matches the target, and row counts and content must be validated. Only after lag is near zero and validation passes should applications point back to the source.
| 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 application refactoring |
Cloud Bigtable (opens in a new tab) and other systems follow the same principle that the return path must handle data type changes, reverse forward transformations, and route rows to the correct shard. Database Migration Service failback jobs perform only change data capture with no full backfills and replicate only tables from the original job.
Continue with this study guide: Reverse replication from Google Cloud to source
Planning and performing database migration with fallback and conversion covers moving schema, data, and application logic to Google Cloud while keeping a tested return path. 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. A successful migration preserves data types, SQL behavior, and application logic so the target provides functional parity.
Fallback options after production cutover define how applications return to a current copy if the target fails. The source instance is kept online and kept in sync after cutover so applications can point back with minimal data loss. A fallback plan must be treated as a full migration with its own planning, success criteria, timeboxes, and testing.
Simple fallback points applications back to the source and, if target writes must be kept, stops target writes, backs up the target, and restores to the source. Reverse replication streams writes continuously from the target back to the original source but cannot be end-to-end tested until after cutover. Forward replication streams writes from the target to a third instance and can be fully tested in advance. Duplicate writes send application writes to both source and target at the same time, which allows instant return but requires refactoring or a data-access microservice. When AlloyDB for PostgreSQL (opens in a new tab) 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.
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 online and offline data movement is the end-to-end orchestration from a source such as Amazon RDS or Aurora to Cloud SQL (opens in a new tab) or AlloyDB. Scheduled maintenance copies the database once during a cutover window and fits when downtime is affordable. Continuous replication keeps the destination synchronized with the source and is required for mission-critical databases that need near-zero downtime.
Database Migration Service (DMS) is the managed service for online moves. A DMS migration job pairs source and destination connection profiles, defines migration settings, monitors progress, and finalizes cutover. Creating and running a job follows an ordered workflow:
One-time migration jobs are not supported in DMS, so one-time moves use engine-native dump and load through Cloud Storage. For PostgreSQL, pg_dump and pg_dumpall extract from RDS or Aurora and load into Cloud SQL or AlloyDB, with parallel jobs and compression to reduce time. Cloud Monitoring tracks replication lag and target sizing, because an undersized target causes long migrations and slow applications.
Converting schema and application logic for a new engine translates structure and behavior so code runs correctly on the target. 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 (opens in a new tab) convert Oracle DDL to PostgreSQL syntax, then a migration job performs full load and ongoing capture. 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 the source. Checks include monitoring pg_restore output, reviewing PostgreSQL logs, running row counts and sample queries, and testing 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.
Selecting the correct database migration tool means matching the source engine, data size, and transformation needs to the Google Cloud service that moves schema and data with the least friction. Heterogeneous moves change engines, analytical moves load warehouses into BigQuery, and homogeneous moves keep the same engine on Cloud SQL or AlloyDB. The choice depends on whether schema can move unchanged, whether data must be reshaped before loading, and how large the dataset is.
Tools for warehouse and Spanner migrations handle analytical and globally distributed targets that need schema conversion. BigQuery 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.
Dataflow is the serverless service for complex transformations during migration, built on Apache Beam. Data flows from the source or staging bucket into a Dataflow pipeline, the pipeline applies transforms, and the BigQuery connector writes to BigQuery tables. Choose Dataflow when migration requires joining datasets, filtering sensitive fields, converting types, or enriching records in flight. 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.
Migration pipelines fall into three patterns:
| 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 |
Migrations to Cloud Spanner (opens in a new tab) require redesign for keys and distribution. The Spanner migration tool performs assessment, schema conversion, and data migration. Datastream reads bulk data and change data capture 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.
Exam tip: Choose BigQuery Data Transfer Service when the goal is automated extract-and-load from Redshift or Teradata without transformation; choose Dataflow when data must be trimmed, enriched, or reshaped before it lands in BigQuery.
Homogeneous moves with managed jobs or manual copies keep the same engine when moving MySQL, PostgreSQL, or SQL Server to Cloud SQL (opens in a new tab). Database Migration Service provides serverless continuous migration built from connection profiles, which store connectivity for a source, and migration jobs, which pair a source profile with a destination instance. Access is controlled by Identity and Access Management to prevent unwanted access.
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. For PostgreSQL to AlloyDB, the AlloyDB connection profile carries cluster ID, user, VPC network, primary instance ID, and machine shape, which DMS uses to create the new cluster.
Manual export and import moves data through files without managed replication. For SQL Server, export to a BAK file and import into a prepared target with matching storage, networks, and users, without exporting the sqlserver system database. Manual moves require an outage, so they fit low-activity windows. Another manual path replicates from an external server into Cloud SQL, then demotes the Cloud SQL primary and promotes the external server to reverse roles. After promotion to primary, Cloud SQL resets backup settings to defaults, so custom backup locations must be configured again.
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.
An online migration follows an ordered sequence:
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 (opens in a new tab) copies staged files from Amazon S3 to Cloud Storage, while Transfer Appliance ships data offline when bandwidth cannot meet the deadline.
Last updated