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
Applying concepts to implement scalable and highly available databases in Google Cloud means keeping databases reachable when a zone or region (opens in a new tab) fails while letting capacity grow with demand. A zone is a separate location inside a region, and high availability spreads copies across zones with automatic failover, while disaster recovery (opens in a new tab) spreads copies across regions with promotion of a replica to primary.
Provisioning highly available database solutions in Google Cloud means placing copies of data in separate failure domains so one zone or region failure does not stop service. Synchronous replication inside a region copies every write before commit, automatic failover moves traffic to a standby, and readable copies in other regions protect against wider outages. The choice depends on whether the goal is surviving a zonal failure or a full regional loss.
Cloud SQL high availability uses 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, 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.
Automatic failover in Cloud SQL follows a fixed order: first failure detection, then promotion of the standby to primary, then rerouting of client traffic, and finally rebuilding of the old primary as the new standby. The failed primary does not automatically take over when it recovers, so applications stay on the promoted instance until a planned failback. Cross-region replicas extend this beyond one region, where data flows from the regional primary to a readable replica in a different region, and promotion makes that replica the new primary if the 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.
Bigtable replication automatically synchronizes 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 with eventual consistency and no single-row transactions, while single-cluster routing pins all requests to one specified cluster for strong reads but requires manual failover by changing the app profile.
Cloud Spanner runs as a regional or multi-regional resource to survive zone and region failures. In the regional model, infrastructure is spread redundantly 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, such as four read-write replicas equally across two regions in separate zones plus a witness replica in a third region, where separate zones protect against zonal outage and the witness lets voting succeed if one main region is lost.
| Model | Failure protection | Latency and cost tradeoff |
|---|---|---|
| Regional Spanner | Survives zonal failure within one region | Lower latency, cannot serve if the whole region is lost |
| Multi-regional Spanner | Survives zonal failure and full region loss | Higher latency, protects cross-region workloads |
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. High availability (HA) protects a single region with synchronous replication and automatic failover for zero or near-zero Recovery Time Objective (RTO) and RPO, while disaster recovery protects two or more regions with typically asynchronous replication and manual promotion for larger, lag-dependent RTO and RPO.
Testing high availability and disaster recovery strategies means intentionally disrupting resources to prove failover works and to measure actual recovery against targets. Recovery Time Objective (RTO) is the maximum time an application can stay offline, while Recovery Point Objective (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 sets how much new data is at risk, because a disaster just before the next backup loses almost a full interval, and backup retention sets how far back recovery can go, which protects against logical errors. For RTO, the controlling variable is readiness at the recovery site, from stopped virtual machines up to a fully running hot standby. The interval between backup or transfer jobs must not be longer than the RPO, so an RPO of one day needs a job at least once a day.
Simulation of zonal and regional disruptions validates that detection, failover, and notification work under a real scope of loss. Test systems must be isolated from primary systems while primaries keep running, because isolation prevents split brain where two databases both accept writes. A well-designed simulation answers the same operational questions at every layer:
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.
| Model | Standby state | Recovery characteristics |
|---|---|---|
| Cold | No running servers; images, templates, and backups in Cloud Storage wait for use | Highest RTO and RPO, lowest running cost |
| Warm standby | Minimal database runs and receives replicated transactions | Smaller RTO and RPO than cold without full HA cost |
| Hot | Both environments serve production and stay synchronized | Near-zero RTO and RPO, highest cost and complexity |
Manual and automated failover 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:
Best practice is to start with a documented manual process, refine the playbook after each run, and then automate. Failback reverses the flow by backing up the temporary primary, restoring production, stopping new writes to the temporary system, copying final logs, redirecting DNS back, and deleting the temporary deployment.
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.
Replication latency 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 RPO is not configurable and grows when the change rate exceeds the replication rate. Placement also limits design: a primary disk can replicate only to a secondary disk within the same continent, each disk supports up to 64 TiB, each project supports at most 1000 disk pairs per region pair, and the disk/async_replication/time_since_last_replication metric should be checked before failover.
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. Switchover and replica failover both promote the DR replica but differ in safety, and after promotion, point-in-time recovery is available only after the first automated backup completes, which can take 5 to 15 minutes.
| Operation | Use when | Data and role behavior |
|---|---|---|
| Switchover | The primary is healthy and the move is planned | Secures binary logs to Cloud Storage before promotion, then reverses replication; split-brain cannot occur |
| Replica failover | The primary region is unavailable | Promotes without waiting for the old primary; split-brain can occur if the old primary kept accepting writes |
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.
Region selection also controls data residency and compliance. 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, and VPC Service Controls creates a service perimeter that restricts Cloud SQL Admin API and Cloud Storage API use for import and export. Synchronous regional replication waits for replica confirmation before commit for identical copies but higher latency, while asynchronous cross-region replication lets the primary commit first for lower write latency but creates replication lag and possible loss on promotion.
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, Bigtable, and Memorystore for Redis Cluster all support replicas, but they differ in whether replicas accept writes and how data is copied.
In-region replication keeps latency low and data in one region, while cross-region replication brings reads closer to distant users and provides 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.
| Placement | Replication and read latency | Cost and resilience tradeoff |
|---|---|---|
| In-region replicas | Lower replication latency; reads stay in same region | Avoids cross-region charges; survives zonal failure with data in one region |
| Cross-region replicas | Higher replication latency, but lower read latency near replica | Incurs networking charges; survives regional outage with data in multiple regions |
Replication behavior controls what is copied and how the first copy is built. In Bigtable, adding a cluster first copies existing data from the original zone to the new zone and then synchronizes ongoing changes with eventual consistency, including updates, new and deleted tables, and garbage collection policy changes. In Memorystore, data flows from a replicator node in a primary shard to follower nodes in the secondary while the primary stays online. 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.
AlloyDB read pools increase read throughput by holding a full read-only copy that receives asynchronous streaming replication from the primary instance. Applications send reads to pools so the primary can focus on writes, and a cluster supports up to 20 read pool instances. Health is reported through two conditions: Available means the pool can serve queries, while Streaming means it is connected and receiving changes, so a pool can stay readable while no longer fresh. 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.
Replica lifecycle management in Cloud SQL protects the dependency where each replica depends on its primary. A single primary supports up to 10 direct replicas, with cascading hierarchies up to four levels including the primary, and Cloud SQL updates all read replicas first before updating the primary during maintenance. Lifecycle rules include:
Promotion converts a replica into a standalone primary that accepts writes, and the original primary must be made inaccessible before clients use the new primary to avoid split-brain.
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.
Machine type and compute sizing controls the vCPUs and memory for a Cloud SQL instance, using --cpu and --memory in gcloud or tier = "db-custom-2-7680" in Terraform. Database flags are key-value parameters such as cloudsql.iam_authentication=on for PostgreSQL IAM authentication. Automated backups use a UTC start time for a 4-hour window, and private connectivity disables public IPv4 and uses Private Service Connect so traffic flows from allowed consumer projects to the endpoint without a public IP.
| Availability type | Failover behavior | Choose when |
|---|---|---|
| REGIONAL | Creates standby in different zone; fails over within region | Production instances that require HA |
| ZONAL | Provides no failover capability; default value | Non-production or cost-sensitive instances |
Infrastructure as Code provisions infrastructure using code that declares the desired state of machines, connections, and storage. The same configuration can be reused to create reproducible environments for Cloud SQL, Spanner, and Bigtable with consistent reuse, automated creation, reviewed changes, and history for audit.
| Tool | What it does | Choose when |
|---|---|---|
| HashiCorp Terraform with Google Cloud provider | Defines resources in versioned files and manages lifecycle | General code-based management is needed |
| Infrastructure Manager (Infra Manager) | Automates deployment from Terraform configurations without separate toolchain | Programmatic deployment to Google Cloud is needed |
| Terraform Cloud and Enterprise | Runs Terraform remotely and stores state and secrets | Full organizational change management is needed |
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.
Integrating security and CI/CD 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 that flow from source control into Cloud Build, then to Infra Manager or Terraform, and finally to the target project. A database pipeline moves through an ordered lifecycle:
validate and plan to preview changes.dev, staging, and prod with templating.Automating access uses IAM database authentication with the cloudsql.iam_authentication flag and the cloudsql.instanceUser role, ideally with a dedicated least-privilege service account per service. Automating encryption declares Customer-Managed Encryption Keys, restricts public IPs, and defines 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. 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.
Core metrics for primary instances show whether an instance is constrained, and teams can create custom dashboards to monitor chosen metrics side by side. Third-party tools such as Datadog and Splunk are an alternative when teams already use them. Core signals include:
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. The standby fails over automatically after about 60 seconds of primary unresponsiveness, and replication delay for PostgreSQL replicas is measured in bytes with gcloud sql instances describe and select * from pg_stat_replication;.
Alerting policies link monitoring to rapid response by evaluating measurements against thresholds and notifying on-call teams. 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, and for many replicas a group with a naming rule such as ending every failover name with -failover can use Any Member Violates. A storage-capacity alert on disk utilization warns when free space falls below 20% so maintenance and writes are not blocked.
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.
| Service | What it collects | Diagnostic use |
|---|---|---|
| Cloud Logging | Logging data and Cloud SQL instance logs | Audit operations and reconstruct event sequences |
| Cloud Monitoring | Measurements and query telemetry | Track health, compare trends, and trigger alerts |
| Cloud Trace | Latency data and executed query plans | Follow request paths and detect latency shifts |
Correlating application context links code to SQL statements, where Sqlcommenter instruments object-relational mappers 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.
Last updated