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 fallback and rollback contingency plans is the practice of defining, testing, and keeping ready a secure return path from the target database to the source if production cutover fails. A fallback plan usually means rolling back after cutover when problems appear on the target instance, and it must be treated as a full migration with its own planning and testing. Although fallback is a last resort and most migrations never use it, a strategy should always be in place because having no plan can add effort and avoidable disruption.
Fallback planning fundamentals are the decisions that make rollback possible without data loss or an extended outage. A fallback plan defines fallback scenarios for each execution task, sets a monitoring period after cutover to decide whether to trigger fallback, and weighs the effort of rolling back against fixing forward on the target database instance (opens in a new tab). In many cases fixing forward is less work than rolling back, especially after new writes have accumulated on the target. As a best practice, the source database instance is kept online and kept in sync after cutover so applications can be pointed back with minimal data loss.
Keeping the source ready depends on replication and careful cleanup after cutover:
The source therefore depends on the return replication stream to remain a valid rollback destination until the target is proven stable.
Simple fallback is the strategy of switching applications back to the original source database instance. The source database instance remains the fallback destination, and the target database instance stops receiving application writes when fallback begins. Choose simple fallback when downtime during fallback is acceptable or when transactions committed on the new target system do not need to be preserved.
When written data on the target must be preserved and some downtime is acceptable, data flows from the target database instance back to the source database instance through backup and restore:
Later reconciliation is possible only for some workloads, especially if workloads do not depend on specific record creation time or time-ordering constraints.
Reverse replication is the strategy of continuously replicating writes that happen on the new target database instance after cutover back to the initial source database instance. Data flows continuously from the target database instance to the source database instance, so the original source stays in sync while production runs on the new target. The source database instance therefore depends on the replication stream from the target to remain a valid rollback destination.
Reverse replication has a small period with no fallback because the replication stream from the target to the source cannot be tested until after cutover to the target. Choose reverse replication when the source instance can be kept for some time and the migration uses continuous replication. Do not choose this approach when end-to-end testing of the fallback path is required before cutover.
Forward replication is a variation of reverse replication that replicates writes from the new target database instance to a third database instance of choice. Data flows from the target database instance to the third database instance, and applications can be pointed to the third database instance if fallback is needed. The third database instance can serve read-only queries while the main server is unavailable, and any replication mechanism can connect the target to the third instance.
Forward replication can be fully end-to-end tested before it is needed, which is its main advantage over reverse replication. Choose forward replication when fallback coverage is needed at all times or when the initial source database must be discarded shortly after production cutover.
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.
Duplicate writes is a dual-write architecture in which applications write to both the initial source and target database instances at the same time. The dual-write path is already in place when the migration uses the Y writing and reading approach or a data-access microservice, but it otherwise requires application refactoring or new tools that connect to both database instances. Writes flow from the application layer to both the source database instance and the target database instance, which allows a gradual production cutover until only the target database instance is used.
If issues appear, traffic flows back to the source database instance with no downtime because the source already has current writes. The initial source and the duplicate-writing mechanism can be discarded only after the migration is observed with no issues. Choose duplicate writes when zero migration downtime and a reliable fallback are critical and there is time and resources for application refactoring.
Choosing a fallback strategy means matching downtime tolerance, testability, and source-retention constraints to one of the four options. The table compares how each fallback strategy moves data and when it fits best.
| Strategy | Data flow after cutover | Choose when |
|---|---|---|
| Simple fallback | Applications point back to the source; optional backup flows from target to source | Downtime during fallback is acceptable, or target writes do not need to be kept |
| Reverse replication | Continuous replication flows from target to original source | Source can be kept for some time and migration uses continuous replication |
| Forward replication | Continuous replication flows from target to a third instance | Coverage is needed at all times, or source must be discarded soon after cutover |
| Duplicate writes | Application writes flow to both source and target | Zero downtime and reliable fallback justify application refactoring |
After cutover, the safest posture is to keep the return path live until the target is proven stable. A common pattern is to replicate writes from the target Cloud SQL instance back to the source database, so rollback causes minimal data loss. When AlloyDB for PostgreSQL (opens in a new tab) is the migration destination, a useful alternative is to replicate changes to a Cloud SQL for PostgreSQL instance and use that instance as the fallback destination.
Orchestrate Migration Workflows and Data Synchronization is the end-to-end coordination of how data moves from a source such as Amazon RDS or Amazon Aurora to a Google Cloud destination such as Cloud SQL or AlloyDB for PostgreSQL. The workflow starts with choosing an online or offline strategy, then runs the data movement with the matching tool, keeps source and destination synchronized, and finishes with a monitored cutover where all reads and writes move to the target instance. A migration is considered complete only after replication is stopped and all traffic serves from the target.
The migration strategy determines how much downtime is allowed and how synchronization is handled. Scheduled maintenance, also called one-time migration or big bang migration, copies the database once during a cutover window and is ideal when downtime is affordable. Continuous replication, also called online migration or trickle migration, keeps the destination synchronized with the source during migration and is required for mission-critical databases that need near-zero downtime.
| Strategy | Downtime and risk | Cost and complexity |
|---|---|---|
| Scheduled maintenance | Requires a cutover window to back up, transfer, restore, and switch applications; if it fails before completion, the process restarts and downtime grows | Lower cost and complexity with little application refactoring |
| Continuous replication | Near-zero downtime and lower risk of data loss because work is split into chunks and rollback and repeat takes less time | More complex setup, more planning time, and additional work to refactor connected applications |
Strategies can be mixed by database, even on the same instance. Small and infrequently used databases can use scheduled maintenance, while mission-critical databases use continuous replication. Two continuous variations reduce application impact: the Y writing and reading approach that duplicates data in both source and destination during migration, and a data-access microservice that reduces the refactoring required by the Y approach.
Offline migrations with built-in database engine backups use dump and load, also called backup and restore, to export from the source instance and import into the target instance. For PostgreSQL targets, the pg_dump and pg_dumpall utilities extract data from the Amazon RDS or Aurora instance (opens in a new tab) and load it into Cloud SQL for PostgreSQL or AlloyDB for PostgreSQL directly or through Cloud Storage. One-time migration jobs are not supported in Database Migration Service (DMS), so engine backups are the recommended path for one-time moves. This approach is straightforward and effective for large instances, but it requires coordination when many databases move at once.
Database engine backups for offline moves have practical limits that shape the workflow:
PostgreSQL utilities add specific constraints: use them for databases of 500 GB or less because larger dumps take a long time and need substantial disk and memory, pg_dump does not include users and roles so pg_dumpall is needed for accounts and roles, and Cloud Storage supports a maximum single-object size of 5 terabytes so larger exports must be broken into smaller segments. To reduce cost and transfer time, compress data with parallel jobs using the --jobs option and set compression with the -z option from 0 to 9, where 6 is the default and higher values shrink the file but consume more client resources. After restore, verify integrity by checking pg_restore output, reviewing PostgreSQL logs for warnings or errors, and running basic queries and table counts.
Database Migration Service (DMS) is the managed service for online migrations that keeps the Cloud SQL destination synchronized with the source until cutover. A DMS migration job organizes the source and destination connection profiles, defines settings specific to the migration process, monitors progress, and safely finalizes the operation. Data flows from the source database through the migration job to the destination Cloud SQL instance, with ongoing replication capturing changes made on the source while applications still run there. A managed import follows the same pattern for PostgreSQL with an initial data load that extracts data from the external RDS or Aurora server and imports it directly into the Cloud SQL for PostgreSQL instance, followed by replication.
Creating and running a DMS migration job follows an ordered workflow:
Before starting, the foundation must be ready: resource hierarchy, Identity and Access Management (IAM), billing, network connectivity between Amazon Web Services and Google Cloud, security hardening, and logging and monitoring. The source database, destination instance, and both network paths must be configured, and the required DMS and Cloud SQL connection profiles must exist.
Exam tip: One-time migration jobs are not supported in Database Migration Service — use database engine backups for scheduled maintenance moves and DMS jobs for continuous replication.
Cloud Monitoring is the service that tracks migration health and database performance before, during, and after cutover. Cloud Monitoring includes predefined dashboards for several Google Cloud products, including a Cloud SQL monitoring dashboard, with third-party options such as Datadog and Splunk also available. During online migration, monitor replication lag, instance CPU, memory, disk throughput, IOPS, and vCPU use, because incorrect target sizing causes long migration times, database errors, and application slowdowns. Disk performance on Cloud SQL depends on disk size and vCPU count, so the target must match source performance needs.
Cutover is the controlled switch where applications stop using the source and start using the target. First, validate in staging with functional tests and row-count and data comparisons, then stop writes to the source during the final window. Then, promote the DMS job or finish the final restore, point applications to the Cloud SQL or AlloyDB instance, and monitor applications for a defined period to decide whether fallback is needed. After cutover, create a final backup of each source database, compare source and target parameter settings and statistics, keep at least 20% free disk space with a metrics-based alert on disk utilization, and configure a maintenance window for the primary instance.
Evaluate and perform DDL and DML conversions means translating both the structure and the behavior of a source database so it runs correctly on Google Cloud. Data Definition Language (DDL) creates and changes objects such as schemas, tables, views, indexes, functions, triggers, and stored procedures, while Data Manipulation Language (DML) reads and changes the rows inside those objects. A successful conversion preserves data types, SQL dialect behavior, and application logic so the target provides functional parity with the source.
Homogeneous migration moves data between the same database technology, such as from Amazon RDS for SQL Server to Cloud SQL for SQL Server (opens in a new tab) or from Amazon RDS for MySQL to Cloud SQL for MySQL. Heterogeneous migration moves between different engines, such as from Oracle to Cloud SQL for PostgreSQL, and requires full rewriting of proprietary syntax and logic. Homogeneous projects focus on parameter defaults, extensions, authentication, backup, replication, and high availability differences, while heterogeneous projects must also remap data types and rewrite stored code.
| Migration type | Source to destination example | DDL and DML conversion work |
|---|---|---|
| Homogeneous | Amazon RDS for SQL Server to Cloud SQL for SQL Server | Align instance settings, extensions, and unsupported features with minimal syntax change |
| Heterogeneous | Oracle to Cloud SQL for PostgreSQL | Convert schema syntax, map data types, and rewrite dialect-specific functions, triggers, and procedures |
Even homogeneous pairs are not identical because infrastructure, storage, capacity models, engine integrations, and default parameter values can differ between Amazon RDS or Aurora and Cloud SQL. Benchmarking and inventory collection help size the target instance correctly by disk size, throughput, IOPS, and vCPU count, because incorrect sizing can cause long migrations, errors, and poor application performance.
Analysis of the source schema identifies every object and proprietary feature that must be converted, sized, or reworked before data movement begins. The inventory captures engine version and edition, CPU and memory, topology, backup policies, parameter settings, and special customizations that affect compatibility on Cloud SQL or AlloyDB. Automated collection is preferred because manual inventories are error-prone and can lead to incorrect assumptions about scope and downtime.
The assessment examines database logic and objects that carry DDL and DML behavior:
Three assessment tools divide this work. Migration Center (opens in a new tab) provides landscape assessment, technical fit, sizing recommendations, and total cost of ownership reporting. migVisor is especially useful for heterogeneous moves because it flags incompatible proprietary artifacts, estimates migration complexity, recommends a Google Cloud target with sizing and architecture, and produces a phased plan. The Database Migration Assessment Tool (DMA) uses open source collector scripts to report features in use, database objects, and database code plus effort estimation.
Data type mapping replaces source-specific types, precision rules, defaults, and null handling with equivalent PostgreSQL, MySQL, or SQL Server behavior on the target. SQL dialect differences include functions, operators, date and string handling, sequences, identity columns, procedural language syntax, and error handling that do not translate one-to-one. migVisor output highlights where proprietary features will fail by default and points to workarounds, which lets engineers decide whether to rewrite logic, replace an extension, or change application SQL.
Functional parity requires converting both table definitions and the code that touches them. A table may create successfully while its related views, triggers, and procedures still fail if dialect functions or exception logic were not rewritten. Collecting source parameters and statistics before cutover also matters, because comparing source and target settings helps explain performance or behavior differences after the converted DDL and DML are deployed.
Database Migration Service (DMS) provides conversion workspaces and migration jobs that carry converted schemas and replicated data from source to destination. For Oracle to Cloud SQL for PostgreSQL (opens in a new tab), DMS converts Oracle schema and objects to PostgreSQL syntax, then runs a migration job that performs the full load and, for continuous jobs, ongoing change capture. All related entities, including connection profiles, migration jobs, destination databases, and conversion workspaces, must reside in a single region because DMS is fully regional.
The heterogeneous Oracle migration follows an ordered flow:
For one-time PostgreSQL moves, one-time migration jobs are not supported in DMS, so engineers use engine-native dump and load through Cloud Storage instead. The pg_dump and pg_dumpall utilities export from Amazon RDS or Aurora and restore into Cloud SQL for PostgreSQL or AlloyDB for PostgreSQL, with compression and parallel jobs to reduce time and cost. Flat-file CSV export and import is another option when transformations, checks, or consolidation of multiple databases into one instance are needed during a maintenance window.
Exam tip: pg_dump does not include users and roles, while pg_dumpall does include them for migration to Cloud SQL or AlloyDB for PostgreSQL.
Verification of the converted database confirms that DDL objects exist correctly and DML results match the source. Checks include monitoring restore logs for warnings or errors, running row counts and sample queries, comparing table data between source and target, and testing views, functions, triggers, and procedures with representative workloads. For PostgreSQL dumps, best practices include using correct dump flags, compressing output, tuning parallel jobs and compression level, and splitting exports when approaching the 5 TB single-object limit in Cloud Storage.
Built-in backups are practical for databases up to 500 GB because larger dumps can require substantial disk, memory, and time to restore. Source parameter settings and statistics should be saved before cleanup so target flags can be aligned and performance can be compared fairly. A final source backup preserves the end state for compliance and supports fallback if the converted application needs additional fixes after cutover.
Last updated