Apply concepts to implement scalable and highly available databases in Google Cloud.

Provisioning highly available database solutions in Google Cloud means keeping databases reachable when a zone or region fails. A zone is a separate location inside a region (opens in a new tab), and high availability spreads copies of data across zones or regions so one failure does not stop service. This design uses synchronous replication inside a region, automatic failover between zones, and readable copies across regions for wider outages.

1.1Cloud SQL Regional High Availability with Synchronous Replication

Cloud SQL high availability is a regional instance with a primary instance in one zone and a standby instance in a second zone of the same region. Every write is copied with synchronous replication to persistent disks in both zones before the transaction is reported as committed. This order guarantees the standby holds the same committed data as the primary, which supports zero or near-zero Recovery Point Objective (RPO). An HA-configured instance costs twice as much as a standalone instance for CPU, RAM, and storage, and regional persistent disk with at least one dedicated CPU carries full Service Level Agreement (SLA) coverage.

1.2Automatic Failover and Cross-Region Replicas in Cloud SQL

Automatic failover in Cloud SQL is the process where the standby becomes the new primary and client traffic is rerouted after an instance or zone failure. The failed primary does not automatically take over when it recovers, so applications stay on the promoted instance until a planned failback moves traffic back. The lifecycle runs in order: first failure detection, then promotion of the standby, then rerouting of user traffic, and finally rebuilding the old primary as the new standby.

Cross-region replicas extend protection beyond one region. Data flows from the regional primary to a readable replica in a different region, and promotion makes that replica the new primary if the whole primary region is lost. Cascading replicas add a second hop, so data flows from the primary region to the first replica region and then to a second replica region. Because a read replica can be configured for HA in advance, the promoted replica already has its own primary and standby across zones.

1.3Bigtable Multi-Cluster Replication and App Profile Routing

Bigtable replication is the automatic synchronization of data between all clusters in the same instance, where each cluster lives in a single zone and holds an independent copy. Production instances need at least two clusters in unique zones, which protects data if one zone fails. Replication can stay inside one region to avoid cross-region charges and extra latency, or span regions for geographic redundancy at extra cost.

An app profile controls which clusters an application can use. Multi-cluster routing sends requests to the nearest healthy cluster and fails over automatically, but it provides eventual consistency and does not allow single-row transactions. Single-cluster routing pins all requests to one specified cluster and keeps other clusters as near-real-time backups, which gives strong reads but requires manual failover by changing the app profile. For highest availability, use N+2 clusters in different regions, where N is the minimum needed for capacity, ideally spread across continents.

1.4Cloud Spanner Regional and Multi-Regional Availability

Cloud Spanner (opens in a new tab) is a managed database that can run as a regional or multi-regional resource to survive zone and region failures. In the regional model, infrastructure is redundantly spread across all zones in one region, so traffic serves from another zone if one zone fails. In the multi-regional model, infrastructure spans several regions, so service continues even if an entire region is lost, typically at higher latency and cost.

Replica placement separates failure domains. The documented eur6 configuration uses four read-write replicas equally across two regions in separate zones, plus a witness replica in a third region. Separate zones protect against zonal outage, while the witness in a third region lets voting succeed if one main region is lost. A multi-region application stack flows from global DNS to a regional external Application Load Balancer in each region, then to web servers, then to application servers, and finally to the shared multi-region Spanner instance, so no database restore is needed after a regional failure.

1.5High Availability Compared With Disaster Recovery Scope

High availability (HA) and disaster recovery (opens in a new tab) use different scopes and replication to meet different recovery goals. HA protects a regional deployment against zone failure with synchronous replication and automatic failover. DR protects a multi-region deployment against region failure with typically asynchronous replication and manual or semi-automated promotion.

Choose HA when business continuity requires automatic survival of a zonal outage inside one region. Choose DR with cross-region replicas when survival of a regional outage is required.

Testing high availability and disaster recovery strategies means intentionally disrupting resources to prove failover works and to measure actual recovery. A simulation restores backups in an isolated environment, runs the full application stack, and compares measured downtime and data loss to targets. Recovery Time Objective (RTO) (opens in a new tab) is how long before service runs again, while Recovery Point Objective (RPO) is how much data loss in time is acceptable.

2.1Recovery Time and Recovery Point Objectives

Recovery Time and Recovery Point Objectives are the two measurable targets that define disaster recovery success. RTO is the maximum time an application can stay offline, and RPO is the maximum time period of data that might be lost. Frequently changed user data might need an RPO of minutes, while back-office data might tolerate hours.

Two variables control the achievable point: backup frequency and backup retention. Backup frequency sets how much new data is at risk, because a disaster just before the next backup loses almost a full interval. Backup retention sets how far back recovery can go, which protects against logical errors by allowing restore to a point before corruption. For RTO, the controlling variable is readiness at the recovery site, from stopped virtual machines up to a fully running hot standby.

2.2Simulating Zonal and Regional Disruptions

Simulation of zonal and regional disruptions validates that detection, failover, and notification work under a real scope of loss. Google Cloud validates its platform by disconnecting network connectivity to a production-equivalent region, and customer teams use fault injection for 1, 5, 10, or 30 minutes. A well-designed simulation answers the same operational questions at every layer:

• What customers experience and how the outage is detected
• What happens to the application, its data, and dependent applications
• What recovery actions are required, who performs them, and who is notified when
• Whether backups, backup schedules, and recovery from stored backups all succeed
• Whether security controls, system access, and network routing remain correct after failover

Test systems must be isolated from primary systems while primaries keep running. Isolation prevents split brain where two databases both accept writes.

2.3High Availability Versus Disaster Recovery Expectations

High availability protects against zone failure inside one region, while disaster recovery protects against failure of an entire region. Simulations must test both scopes because even a regional product designed for 99.99% availability can still have nearly an hour of downtime per year.

Asynchronous replication offers lower latency at the cost of possible data loss, which increases RPO. Managed multi-regional services such as Spanner and Cloud Storage provide built-in redundancy and reduce manual recovery steps.

2.4Cold, Warm, and Hot Redundancy Models

Cold, warm, and hot redundancy models describe how ready the standby environment is when disaster occurs. Smaller RTO and RPO values require more continuously running resources, so cost rises as recovery gets faster.

A cold test proves images and backups can rebuild the system, a warm test proves replication is current and the standby can scale up, and a hot test proves traffic can shift without data loss.

2.5Manual and Automated Failover with Failback Operations

Manual and automated failover operations move production work from a failed primary to a standby through an ordered sequence. A typical cold failover from on-premises to Google Cloud follows this order:

1. Create deployment: Use the Deployment Manager template to create the recovery environment in Google Cloud.
2. Restore backup: Apply the most recent database backup from Cloud Storage to the database server.
3. Apply logs: Apply the most recent transaction logs from Cloud Storage to reach point-in-time recovery.
4. Test application: Simulate user scenarios on the recovered environment.
5. Redirect traffic: Configure Cloud DNS to point to the web server on Google Cloud.

Best practice is to start with a documented manual process, refine the playbook after each run, and then automate. Failback reverses the flow: back up the temporary primary, copy the backup to production, restore production, stop new writes to the temporary system, copy final logs, redirect DNS back, verify replication to Cloud Storage, and delete the temporary deployment. Internal load balancing preserves the same database IP after automatic replacement, while cross-environment failover requires updating Cloud DNS.

2.6Backup Integrity and Point-in-Time Recovery Testing

Backup integrity and point-in-time recovery (PITR) testing proves that backups and logs can restore a working system to a specific moment. Data flows from the stored base backup to the recovery system first, then from stored transaction logs in order to reach the target time. The recovery system must mirror production, including the same database version and disk configuration.

Validation combines automated checks, frequent backups, and health monitoring:

• Run automated consistency checks after each backup and restore in a non-production environment
• Align backup frequency with RPO using Cloud SQL automated backups and PITR, Spanner backups with PITR up to 7 days, and incremental backups for large datasets
• Track backup health with Backup and DR Service and replicate backups across regions
• Monitor RPO adherence with Cloud Monitoring alerts and Pub/Sub notifications from transfer services

A rigorous restoration test restores the base backup in isolation, replays logs in order, starts application tiers with the restored data, validates consistency and user scenarios, and measures actual RTO and RPO against targets.

Setting up multi-regional replication for databases means keeping a primary database in one region with copies in different regions. The primary handles writes, while cross-region replicas receive changes in near real time and serve reads close to users. If the primary region fails, a replica can be promoted to restore writes.

3.1Replication Latency, RPO, and Placement Limits

Replication latency is the delay that determines how much data can be lost and how long recovery takes. RPO is the delay before a primary change is available on the secondary, while RTO is the time to finish failover tasks such as stopping replication and attaching disks in the secondary region. Asynchronous Replication for persistent disks targets an RPO of one minute for up to 12.5 GB of compressed changed blocks per minute at 4 KB granularity, and failover tasks should take only a few minutes.

RPO is not configurable and grows when change rate exceeds replication rate. RPO may exceed one minute during initial copy, after a spike above 12.5 GB per minute, or for up to five minutes if a disk is detached or its virtual machine restarts. Monitor the disk/async_replication/time_since_last_replication metric before deciding to fail over. Placement also limits design: a primary disk can replicate only to a secondary disk within the same continent, such as from Frankfurt (europe-west3) to elsewhere in Europe but never to North America. Each disk supports up to 64 TiB, and each project supports at most 1000 disk pairs per region pair.

3.2Cloud SQL Disaster Recovery Replica Promotion

Promotion to primary in Cloud SQL 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, 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 to the replica through the failoverDrReplicaName field.

Switchover and replica failover both promote the DR replica but differ in safety:

Avoid switchover during high transaction volume or admin operations such as backup or HA changes. After promotion, backup configuration and log retention flow from the old primary to the new primary, but point-in-time recovery is available only after the first automated backup completes, which can take 5 to 15 minutes.

3.3Cross-Region Read Replicas and Data Residency Controls

A Cloud SQL cross-region read replica is a copy in a different region that reflects primary changes in near real time through asynchronous replication. Applications send reads to the nearby replica to lower latency while writes continue on the primary, and an operator can promote the replica to standalone primary during a regional outage. The replica is built from a backup of the primary and then starts continuous replication.

Region selection determines where data at rest is stored and which compliance rules apply. The primary region is chosen at creation and data stays only in configured regions, while each replica region is chosen at replica creation. Organizational policy with the resource locations constraint can allow or deny regions at the organization, folder, or project level. VPC Service Controls enforces these boundaries by creating a service perimeter that restricts Cloud SQL Admin API and Cloud Storage API use for import and export.

A designated DR replica must meet strict requirements: Enterprise Plus in a separate region, same major and minor version as primary on MySQL 8.0.31 or later, binary logging enabled, direct replica rather than cascading or external, transaction logs in Cloud Storage, and no filtering flags such as replicate_do_db or replicate_ignore_table. For consistent failover, match machine type and size, disk size or auto-growth, HA configuration, and data cache between primary and replica.

3.4Synchronous Regional Replication Versus Asynchronous Cross-Region Replication

Data consistency and replication mechanics describe how writes move from primary to replicas. Synchronous replication (opens in a new tab) waits for the replica to confirm each write before commit, which keeps copies identical but adds latency. Asynchronous replication lets the primary commit first and sends changes afterward, which reduces write latency over distance but creates lag and possible loss.

Synchronous regional replication uses Regional Persistent Disk with continuous block-level replication between two zones in the same region. Storage handles the copy without database-layer replication, and reads can automatically serve from the secondary zone. Write throughput is halved versus zonal disks with slightly higher latency, and this architecture alone cannot fail over to another region. Recovery uses a hot standby virtual machine in the backup zone with force-attach in under one minute.

Asynchronous cross-region replication is used by AlloyDB for PostgreSQL, Cloud SQL, and SQL Server Always On groups. Replication lag is the delay between primary commit and replica commit, while flush lag is the delay between write-ahead log (WAL) generation and WAL arrival. Promotion after an outage can lose transactions that never reached the secondary, and RPO grows with CPU load and distance.

Deploying and scaling read replicas means adding extra copies of data to serve reads near users and survive failures. An in-region replica lives in a different zone of the same region, while a cross-region replica lives in a different region. In Google Cloud, Cloud SQL (opens in a new tab), Bigtable, and Memorystore for Redis Cluster all support replicas, but they differ in whether replicas accept writes and how data is copied.

4.1In-Region and Cross-Region Replica Placement

In-region replicas keep data in the same region while spreading it across zones to protect against zonal outage. In Bigtable, a replicated instance in one region places clusters in different zones with an independent copy in each zone. A Memorystore for Redis Cluster secondary can sit in the same region as its primary, and a Cloud SQL read replica in the same region can offload reads without cross-region traffic. Because the copy stays nearby, in-region replication latency is typically lower.

Cross-region replicas bring reads closer to distant users and provide a recovery target if a region fails. In Memorystore, a primary cluster is read-write in one region and a secondary is read-only with asynchronous replication until detached or switched over. In Cloud SQL, the source is the primary instance and copies are read replicas that can be promoted to standalone if the original is corrupted. In Bigtable, every cluster accepts both reads and writes, so access continues through a surviving cluster. A Bigtable instance can use up to 8 regions with one cluster per zone, while a Memorystore primary supports up to two secondaries with the same shard count and slot assignment.

4.2How Replication Copies Data and Stays Consistent

Replication behavior controls what is copied, how the first copy is built, and how current replicas stay afterward. In Bigtable, adding a cluster to an existing instance first copies existing data from the original zone to the new zone and then synchronizes ongoing changes with eventual consistency. Bigtable replicates updates to existing data, new and deleted tables, added and removed column families, and changes to garbage collection policy.

In Memorystore for Redis Cluster, data flows from a replicator node in a primary shard to follower nodes in the secondary, and most settings sync automatically while the primary stays online. In Cloud SQL for SQL Server, first replica creation sets the primary to full recovery model, creates a temporary disk for a full backup, then deletes that disk. Placement trades speed against cost: in-region avoids transfer charges but may need more nodes, while cross-region adds transfer charges and higher replication latency but lowers read latency for remote users and survives regional outage.

4.3AlloyDB Read Pools for Scaling Read Capacity

A read pool instance is a read-only clone that increases read throughput and availability for the cluster. Data flows from the primary instance to each pool through asynchronous streaming replication, and applications send reads to pools so the primary can focus on writes. Each pool holds a full copy and uses the same storage as the instance it replicates. A cluster supports up to 20 read pool instances, with up to 20 nodes per pool in AlloyDB Omni.

Read pools must be added separately to each cluster where capacity is needed. Creating a secondary cluster copies settings such as point-in-time recovery and backups from the primary, but AlloyDB does not automatically copy read pools. Later primary changes are not automatically applied to the secondary, so the secondary must be updated manually. Sizing starts with workload type, latency and throughput targets, data size, and growth, with transactions per second (TPS) as the primary metric for OLTP and a steady-state target of 60-70% CPU.

Health is reported through two Kubernetes conditions: Available means the pool can serve queries, while Streaming means it is connected and receiving changes. A pool can stay readable while no longer fresh when Available is true and Streaming is false. After switchover or failover, the pool does not follow the new primary automatically and must be restarted by scaling down to zero and back up to the recorded node count.

4.4Cloud SQL Replica Lifecycle, Limits, and Promotion

Replica lifecycle management in Cloud SQL covers how a primary and its read replicas are created, maintained, and converted. The primary accepts all writes and streams changes, while each read replica stays read-only for queries and analytics. Promotion converts a replica into a standalone primary that accepts writes, used during regional outage, corruption, or migration. To avoid split-brain, the original primary must be made inaccessible before clients use the new primary.

Lifecycle rules protect the dependency where each replica depends on its primary:

• Backups cannot be configured on the replica; backup actions apply to the primary
• The primary cannot be deleted until all replicas are promoted or deleted
• The primary cannot be restored from backup or given point-in-time recovery while any replica exists
• Binary or write-ahead logs cannot be disabled on the primary until all replicas are promoted or deleted
• A replica cannot be stopped like a primary; it can only be restarted, deleted, or have replication disabled
• User tables cannot be changed on the replica; all changes are made on the primary

A single primary supports up to 10 direct replicas, with cascading hierarchies up to four levels including the primary. When the top of a cascading hierarchy is promoted, its child replicas continue replicating from it. During maintenance, Cloud SQL updates all read replicas first before updating the primary.

Automating database instance provisioning means defining database settings in repeatable scripts instead of manual console steps. Cloud SQL creation accepts compute sizing, database flags, high availability, backup, and private connectivity through gcloud commands and Terraform resources. Automation with infrastructure as code (IaC) reduces human error and keeps development, staging, and production consistent.

5.1Automated Cloud SQL Configuration and Sizing

Machine type and compute sizing controls the vCPUs and memory for a Cloud SQL instance. In gcloud, sizing uses --cpu and --memory with a unit such as 3072MiB, plus --database-version, --zone, and --root-password. In Terraform (opens in a new tab), the same sizing is set in the google_sql_database_instance resource with tier = "db-custom-2-7680", where the tier encodes CPUs and memory. Instance names are externally visible and must not contain sensitive information.

Database flags are key-value parameters applied at creation to change behavior. A gcloud example sets --database-flags=cloudsql.iam_authentication=on for PostgreSQL IAM authentication, while Terraform uses a database_flags block with the same name and value. High availability is controlled by availability_type, where REGIONAL creates a standby in a different zone with synchronous replication for zero or near-zero RTO and RPO, while ZONAL provides no failover and is the default.

Automated backups are enabled in Terraform with backup_configuration { enabled = true start_time = "20:55" }, while gcloud patches an existing instance with --backup-start-time=HH:MM in UTC for a 4-hour window. Private connectivity uses ip_configuration { ipv4_enabled = false } with Private Service Connect (PSC) so traffic flows from allowed consumer projects to the PSC endpoint without public IP.

5.2Infrastructure as Code Tools and Terraform Workflow

Infrastructure as Code (IaC) provisions infrastructure using code that declares the desired state of virtual machines, database connections, and storage. The same configuration can be reused to create reproducible development, test, and production environments for Cloud SQL, Spanner, and Bigtable. Benefits include consistent reuse across environments, automated creation, reviewed changes, kept history for audit and revert, and a single source of truth.

The Terraform provisioning workflow runs in order: first prepare Cloud Shell and set the project, then prepare the root module directory with main.tf and run terraform init, then run terraform plan to preview creates or updates, then run terraform apply and verify in the console, and finally set deletion_protection = "false", apply, then run terraform destroy to remove resources. Each configuration file must live in its own root module directory with a .tf extension.

5.3Security and CI/CD Integration for Provisioning

Integrating security and CI/CD into provisioning means defining infrastructure, access, and encryption in version-controlled code deployed through an automated pipeline. Cloud Build runs builds by importing Terraform code, executing steps, and producing versioned artifacts. Code flows from source control into Cloud Build, then to Infra Manager or Terraform, and finally to the target project and database instance.

A database pipeline moves through an ordered lifecycle:

1. Commit trigger: A commit to a configured branch triggers Cloud Build.
2. Validate and plan: The pipeline runs validate and plan to preview changes.
3. Version artifact: The tested configuration is stored in Artifact Registry or source control.
4. Promote environments: The same configuration is promoted through dev, staging, and prod with templating.
5. Apply deployment: Infra Manager or Terraform applies the approved configuration.

Automating IAM means enabling IAM database authentication (opens in a new tab) with the cloudsql.iam_authentication flag and granting the cloudsql.instanceUser role to users, service accounts, or groups. Best practice is a dedicated service account per service with least privilege. Automating encryption means declaring Customer-Managed Encryption Keys (CMEK), restricting public IPs, and defining VPC Service Controls perimeters in the same templates.

Configuring monitoring for highly available databases means choosing the right measurements and showing them together so teams can protect availability. In Google Cloud, Cloud Monitoring collects measurements and provides both a predefined Cloud SQL dashboard and custom dashboards. Cloud SQL uses built-in agents to collect query telemetry, so primary, standby, and replica signals can be viewed at database and instance level.

6.1Core Metrics and Dashboards for Primary and Replica Health

Centralized observability with Cloud Monitoring helps identify health and capacity issues without manual setup. Teams can create custom dashboards to monitor chosen metrics side by side and set alert policies for timely notifications. Third-party tools such as Datadog and Splunk are an alternative when teams already use them.

Core metrics for primary instances show whether an instance is constrained:

• CPU utilization: shows whether compute is saturated, with at least 60 GB memory recommended for intensive workloads
• Memory usage: shows pressure on buffers and cache that can slow queries
• Storage usage and disk utilization: shows consumed versus free space, with at least 20% free recommended
• Active connections: shows sessions against limits and detects exhaustion
• Read and write operations: shows volume that can point to hot tables
• Ingress and egress bytes: shows network flow, where distance adds latency
• Queries over time: shows trends such as 12-hour graphs to link spikes to resource changes

High-availability indicators differ by target. The standby uses disk-level replication and is not open for reads or writes, while a read replica uses asynchronous replication and is open for reads. Replication delay for PostgreSQL replicas is measured in bytes from the replica overview, with gcloud sql instances describe and select * from pg_stat_replication;. The standby fails over automatically after about 60 seconds of primary unresponsiveness.

6.2Alerting Policies for Lag and Storage Capacity

Alerting policies link monitoring to rapid response by evaluating measurements against thresholds and notifying on-call teams. A complete procedure tracks system-down events, CPU, memory, disk use, table and log alerts, backup success, replication lag, and disk limits. Data flows from the database instance to Cloud Monitoring to the notification method.

A replication lag alert uses Seconds Behind Master above 120 seconds for 3 minutes as an early warning. To create it, open Monitoring then Alerting, create a policy, select Metric Threshold, select Cloud SQL resource type, select the single failover replica, set metric to Seconds Behind Master above 120 for 3 minutes, connect notification, and save. For many replicas, use a group with a naming rule such as ending every failover name with -failover and select Any Member Violates. A storage-capacity alert on disk utilization warns when free space falls below 20% so maintenance and writes are not blocked.

6.3Log Analysis and Diagnostic Auditing with Trace Correlation

Log analysis and diagnostic auditing uses logs, metrics, and traces to audit what a database did and explain problems. Telemetry flows from the database and application into centralized observability, where logs show events, metrics show health over time, and traces show request latency. Collecting telemetry early establishes a baseline that makes anomalies easier to recognize.

Correlating application context links code to SQL statements. Cloud Trace compares latency distributions over time and alerts on significant shifts, while Sqlcommenter automatically instruments object-relational mappers (ORMs) to add tags to SQL statements. The System Insights dashboard shows throughput, latency, and cost for overall health, while the Query Insights dashboard identifies the most expensive commands. In a failover investigation, first confirm event timing in logs, then correlate with System Insights changes and Query Insights evidence to avoid treating post-failover slow queries as the root cause.

Key Facts

• Cloud SQL high availability uses a regional primary and standby in different zones with synchronous replication and automatic failover, while disaster recovery uses cross-region replicas with asynchronous replication and promotion.
• Bigtable multi-cluster routing sends traffic to the nearest healthy cluster with automatic failover and eventual consistency, while single-cluster routing pins traffic to one cluster with manual failover.
• Recovery Time Objective is maximum acceptable offline time and Recovery Point Objective is maximum acceptable data loss in time, with backup intervals at least as frequent as the RPO.
• A Cloud SQL primary cannot be deleted, restored, or given point-in-time recovery while any read replica exists, and a cascading hierarchy allows up to 10 direct replicas and four levels including the primary.
• Cloud SQL instances should keep at least 20% free disk space, with replication lag alerts starting at Seconds Behind Master above 120 seconds for 3 minutes.