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
Network optimization and failover procedures for multi-regional replication balance low-latency reads, limited data loss, and fast promotion of a replica. Data flows from a primary instance or primary disk in one region to a secondary replica in a second region. Applications in the secondary region read from the nearby copy to reduce latency, and when the primary region fails the secondary is promoted to primary and replication reverses direction.
Replication latency is the delay that determines how much data can be lost and how long recovery takes during an outage. Recovery point objective (RPO) is the time delay before a change on the primary is available on the secondary, while recovery time objective (RTO) is the time needed to finish failover tasks such as stopping replication and attaching disks to virtual machines in the secondary region. Asynchronous Replication (opens in a new tab) for persistent disks targets an RPO of one minute for up to 12.5 GB of compressed changed blocks per minute at 4 KB block granularity, and failover tasks for RTO should take only a few minutes.
RPO is not configurable and grows when the disk change rate exceeds the replication rate. RPO may exceed one minute during initial replication when all used blocks are copied, after a spike above 12.5 GB per minute while replication catches up, or for up to five minutes if a replicating disk is detached or its virtual machine restarts. If a block changes multiple times between replication events, only the most recent change flows from the primary disk to the secondary disk. Monitor the disk/async_replication/time_since_last_replication metric to see current RPO before deciding to fail over.
Placement of the secondary copy controls network distance, latency, and allowed failover targets. Asynchronous Replication is available in Asia except Indonesia, Europe, North America, Oceania, and South America, and a primary disk can replicate only to a secondary disk within the same continent. For example, data flows from a primary disk in Frankfurt (europe-west3) to a secondary disk anywhere in Europe, but traffic never flows from Frankfurt to North America under this rule.
Limits and boundaries shape multi-region design and cost:
Using a regional disk as a secondary disk adds zone-level resilience inside the secondary region. The regional primary disk continues replicating data from the healthy zone to the secondary disk if one zone fails, and a regional secondary disk keeps receiving replication despite an outage in one of its zones. When failover occurs, the secondary regional disk transitions to become the new primary disk with copies already present in two zones.
Promotion to primary status in Cloud SQL (opens in a new tab) depends on first designating a cross-region read replica as a disaster recovery (DR) replica. The DR replica must sit in a different region than the primary instance, use the same database version and tier, use Enterprise Plus edition, and have automated backups with binary logging enabled on the primary. Designation links the primary instance to the replica through the failoverDrReplicaName field, which can be set in the console, with gcloud, or with Terraform.
Switchover and replica failover both promote the DR replica, but they differ in planning, safety, and data-loss risk:
| Operation | Use when | Data and role behavior |
|---|---|---|
| Switchover | The primary is healthy and the move is planned, with low transaction volume | Switchover secures binary logs from the primary instance to Cloud Storage before promotion, then reverses replication so the old primary becomes a read replica; split-brain cannot occur |
| Replica failover | The primary region is unavailable and immediate continuity is required | Failover promotes the DR replica without waiting for the old primary, then later rebuilds the old primary as a read replica when it returns online; split-brain can occur if the old primary kept accepting writes |
Avoid switchover while the primary instance is actively used or while admin operations such as automated backup or high availability changes are in progress. High transaction volume can cause switchover to time out, so retry the operation when transaction load is lower and refresh the console to see the reversed primary and replica roles. Replica failover occurs in two phases: first the DR replica is promoted to a standalone instance with instanceType: CLOUD_SQL_INSTANCE, then the old primary is reconfigured when it returns online, signaled by the RECONFIGURE_OLD_PRIMARY message in the operations log.
Exam tip: Choose switchover for a planned regional move because split-brain cannot happen, and choose replica failover for a regional outage where the old primary may still accept writes and cause split-brain.
Backup and recovery after promotion depend on point-in-time recovery (PITR) coverage inherited from the old primary. As soon as the DR replica is promoted, backup configuration and the transaction log retention policy flow from the old primary instance to the new primary instance. If disabled, the binlog flag is turned on, and Cloud SQL takes a new backup that can take 5 to 15 minutes depending on disk size.
The new primary instance has PITR coverage only after that first automated backup completes successfully, so verify the backup before relying on PITR. Cloud SQL enables PITR in a separate asynchronous operation after switchover or failover completes, so backup enablement does not add to failover time. An instance that becomes a read replica keeps its last backup and PITR retention policy, and old backups and logs are deleted on the replica according to that policy even though the new primary is now responsible for new backups.
Replication lag on cross-region replicas occurs when the SQL thread on a replica cannot keep up with the IO thread receiving changes from the primary instance. Common causes are slow queries on the replica, very large row-based writes such as broad DELETE statements, and write load that exceeds the replica size. Possible responses include fixing slow queries, indexing tables, increasing the replica size, reducing database load, sending read traffic to the replica, or recreating the replica from a fresh snapshot.
The recreate lagging replica feature automatically rebuilds a replica that exceeds its allowed lag, defined per replica by replicationLagMaxSeconds. The minimum value is 300 seconds, the maximum is 31,536,000 seconds or one year, and the default is one year when the feature is enabled without a value. Different replicas of the same primary can use different thresholds so all replicas do not go down at the same time. When a replica is recreated, the service performs an ordered sequence:
Recreation causes downtime and is limited to once per day per instance, does not preserve indexes previously created on the replica, does not support replicas of external servers, and incurs additional cost for cross-region replicas. On a cascading replica topology, Cloud SQL recreates leaf replicas first to maintain replication consistency.
Architectural configuration of multi-regional instances and replicas is the design of a primary database in one Google Cloud region with copies in different regions. The primary instance handles writes, while cross-region read replicas receive changes in near real time and serve reads close to global users. If the primary region fails, a replica can be promoted to become the new primary to restore write availability.
A Cloud SQL (opens in a new tab) cross-region read replica is a copy of a primary database instance that lives in a different region and reflects changes from the primary in near real time. Data flows from the primary instance to each replica through asynchronous replication, so applications can send read traffic to the nearby replica to lower latency while writes continue on the primary. During a regional outage, the replica provides geographic redundancy because it already holds an almost current copy, and an operator can promote the read replica to a standalone primary instance to resume writes.
Region selection for multi-regional databases determines where data at rest is stored and which compliance rules apply. The region for a Cloud SQL primary instance is chosen at creation and data at rest stays only in the configured regions, while the region for each replica must be chosen when the replica is created. Organizational policy with the resource locations constraint can allow or deny specific regions such as us-east1 or multi-regions such as asia and europe at the organization, folder, or project level.
VPC Service Controls enforces these boundaries at the network layer by creating a service perimeter that defines the virtual boundaries from which a service can be accessed. The perimeter restricts use of the Cloud SQL Admin API and Cloud Storage API for import and export, which prevents data from being moved outside the selected locations even if a user is authorized by Identity and Access Management policy. Choose replica regions that satisfy both latency goals and data residency regulations, because the replica region must independently meet residency standards.
A designated disaster recovery (DR) replica is a special cross-region read replica that enables advanced switchover and replica failover operations in Cloud SQL. The primary instance that uses advanced DR must be a Cloud SQL Enterprise Plus edition instance and must have a designated DR replica. If the deployment uses a DNS write endpoint, the primary instance must have the same SSL configuration as the designated DR replica before switchover or failover, or clients will fail to connect to the new primary after roles reverse.
The designated DR replica must meet strict compatibility and topology requirements to guarantee that replication can reverse cleanly:
replicate_do_db, replicate_ignore_db, replicate_do_table, replicate_wild_do_table, replicate_ignore_table, and replicate_wild_ignore_table.Exam tip: A designated DR replica must be a direct replica in a different region, not a cascading replica, and both primary and replica must be Enterprise Plus on the same MySQL 8.0.31 or later version.
Consistent sizing between the primary instance and the DR replica prevents performance loss after a switchover or failover. The DR replica should match the primary instance in capacity and availability features so the new primary can carry the same workload immediately. Consistent configuration also avoids application errors caused by missing cache, storage, or database flags after roles change.
The following recommendations help the DR replica handle production traffic after promotion:
Creating a cross-region replica builds a new instance from a backup of the primary instance and then starts continuous replication to it. The creation workflow depends on the primary instance, because the replica inherits the database version and must be placed in a different region. First the operator selects the source primary, then defines the replica identity and location, then matches performance settings before creation completes.
Single-region high availability with a regional Persistent Disk (opens in a new tab) protects against zonal failure but does not provide multi-regional replication. A regional Persistent Disk provides continuous, synchronous block-level replication between two zones in the same region, and the disk can be force-attached to a hot standby virtual machine in the backup zone after a failure. Because the foundational technology replicates only between zones in the same region, regional failover to another region is not possible with this architecture alone.
This boundary creates clear tradeoffs for multi-region planning. Regional Persistent Disks require no application or database layer replication and can automatically serve reads from the secondary zone, but the disk can be mounted for writes to only one database at a time and write throughput is halved compared with zonal disks with slightly higher write latency. Choose cross-region read replicas or a designated DR replica when the goal is geographic redundancy, low-latency global reads, or survival of a full region outage.
Data consistency and replication mechanics describe how database writes move from a primary to replicas and what happens when a zone or region fails. Synchronous replication waits for the replica to confirm each write before the primary commits, which keeps copies identical but adds latency. Asynchronous replication lets the primary commit first and sends changes to replicas afterward, which reduces write latency over long distances but creates replication lag and possible data loss.
Synchronous replication inside a region is provided by Regional Persistent Disk (opens in a new tab) through continuous and synchronous block-level replication between two zones in the same region. The disk automatically detects errors and slowness, switches replication modes, and catches up data that reached only one zone. Database-layer replication is not required because storage handles the copy, and if the primary zone has storage problems the disk automatically serves reads from the secondary zone with higher read latency but no manual action.
Synchronous regional replication protects against source-server zone infrastructure failure, single-zone block-storage degradation, and full-zone outage at the same time. The tradeoff is performance and scope, since write throughput is halved compared to zonal disks and write latency is slightly higher. Replication works only between zones in the same region, so this architecture alone cannot fail over to another region.
Recovery with a regional disk uses a hot standby virtual machine that is identical and running in the backup zone. After an outage is detected, the regional disk is force-attached to the standby and the database engine is started. The force-attach operation typically completes in less than one minute, so recovery time objective (RTO) measured in minutes with zero recovery point objective (RPO) is attainable when the outage is promptly identified and failover is performed.
Asynchronous replication across regions is used by AlloyDB for PostgreSQL, Cloud SQL, and SQL Server on Google Cloud for cross-region disaster recovery. In AlloyDB for PostgreSQL, the primary cluster commits transactions before secondary clusters in separate regions commit them. In Cloud SQL, a cross-region read replica is a copy of the primary that reflects changes in near real time and can be promoted to a standalone primary. In SQL Server, an Always On availability group (opens in a new tab) can replicate asynchronously to additional nodes in a disaster recovery (DR) region.
Asynchronous replication introduces two lag measures that control consistency. Replication lag is the time difference between commit on the primary and commit on the replica, while flush lag is the time difference between generation of the write-ahead log (WAL) on the primary and arrival of that WAL at the replica. Because of flush lag, promoting a secondary after a regional outage can lose transactions that never reached the secondary. Cross-region RPO is affected by primary CPU utilization and physical distance, so testing should establish a safe transactions-per-second limit that does not accumulate flush lag and region pairs should be chosen within the same continent to limit network lag.
Automated failover for asynchronous database replication depends on health checking and quorum. A group of at least two database nodes uses asynchronous replication from the source node to the hot standby replica, plus small witness nodes that provide votes for elections. Group nodes exchange heartbeats every few seconds to assess health, and a majority of nodes must participate to reach quorum and elect a new source node.
How replication choice changes RPO and RTO determines the tradeoff between data loss and recovery speed. RPO measures how much data can be lost, while RTO measures how quickly the database becomes writable again. Synchronous replication favors zero RPO within a region, while asynchronous replication trades possible data loss for lower write latency and geographic separation.
| Replication model | Data consistency | RPO during failure | RTO during failure |
|---|---|---|---|
| Synchronous regional replication | Primary and secondary zones stay identical at commit time | Zero when outage is promptly detected | Minutes, including force-attach time under one minute plus file-system detection |
| Asynchronous cross-region replication | Replica lags behind primary by replication and flush lag | Greater than zero, grows with workload, CPU load, and distance | Longer, including detection, promotion to primary, and application reconnect |
The choice maps to business requirements documented for SQL Server. Use synchronous replication in an Always On availability group for near-zero RPO and low RTO for mission-critical systems. Use asynchronous replication, Backup and DR Service, backup to a Cloud Storage bucket, or log shipping when some data loss is acceptable. Lower-cost options such as offsite backups to a multi-region bucket increase both RPO and RTO but reduce infrastructure and license cost.
Exam tip: Synchronous replication gives zero RPO but works only inside one region, while cross-region replicas are asynchronous and can lose data committed after the last replicated WAL.
Promotion, role changes, and failback describe how a replica becomes writable after a regional failure. Promotion turns a replica into a writable primary after a regional failure, but role changes are not automatic and can only be initiated by the user. For Cloud SQL advanced DR, the DR replica must be a direct, cross-region Enterprise Plus replica with the same major and minor version as the primary, binary logging enabled, transaction logs stored in Cloud Storage, and no filtering flags such as replicate_do_db or replicate_ignore_table. Matching machine type, disk size or auto-growth, high availability configuration, and data-cache configuration is recommended so the new primary handles the workload.
The promote operation breaks replication in an ordered sequence:
ACTIVE instance to the STANDBY instance stops.ACTIVE or STANDBY role is removed from both resources, with state moving from PROMOTING to READY when complete.Promotion is a switchover, not an automatic failover with failback. After promotion the original link is severed and cannot be resumed, administrators must reconnect applications to the new source instance, and a new replica must be paired to restore DR protection.
Last updated