Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Cloud SQL Cross-Region Replicas for DR and Read Scaling
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
An enterprise runs an online transaction processing (OLTP) database on Cloud SQL in us-central1. The engineering team is designing a disaster recovery (DR) and global read-scaling architecture for downstream analytics and regional services located in europe-west1.
The architecture must satisfy the following criteria:
- Disaster Recovery: Provide a cross-region failover target to maintain low Recovery Point Objective (RPO) and Recovery Time Objective (RTO).
- Network Cost & Latency: Minimize inter-region network egress costs and read latency for analytical clients located in Europe.
- Replication Performance: Prevent high replication lag caused by heavy bulk update batches executed on the primary instance.
Which strategy should the data engineering team implement?
Explanations
Deploy a minimal shared-core read replica in europe-west1 and route both write transactions and heavy analytical read queries through a single Cloud SQL Auth Proxy located in us-central1.
Deploy a minimal shared-core read replica in europe-west1 and route both write transactions and heavy analytical read queries through a single Cloud SQL Auth Proxy located in us-central1.
What a Shared-Core Replica with Centralized Proxy Is
This configuration deploys a low-tier shared-core read replica in the remote region while channeling application database connections through a Cloud SQL Auth Proxy running centrally in the primary region.
Why It Doesn't Meet the Requirements
- Replication Performance: Shared-core instances have strictly constrained CPU, memory, and I/O throughput. When massive transactional updates occur on the primary, a shared-core replica quickly experiences severe replication lag because its resources cannot process incoming transactions.
- Network Egress and Latency: Routing European traffic through a proxy situated in
us-central1introduces an unnecessary cross-region network hop before reaching the database, degrading latency and increasing network routing overhead. - Write Routing: Read replicas are read-only; directing write transactions to a read replica will cause transaction failures.
Key Characteristics and Limitations
- Resource Bottlenecks: Shared-core tiers are unsuitable for production replication streams with high write throughput.
- Inefficient Proxying: Placing the connection proxy away from the client increases round-trip times.
- Lag Accumulation: Insufficient CPU/IOPS on the replica guarantees replication timeouts during bulk DML.
This Answer Would Be Correct If
The database workload had extremely low write throughput, negligible analytical demands, and budget constraints requiring minimal non-production sizing.
Deploy a cross-region read replica in europe-west1, direct European analytical read queries to this replica, and resize the replica's compute resources to match the primary while breaking bulk transactional updates into smaller batches.
Deploy a cross-region read replica in europe-west1, direct European analytical read queries to this replica, and resize the replica's compute resources to match the primary while breaking bulk transactional updates into smaller batches.
What Cloud SQL Cross-Region Replication Is
Cloud SQL cross-region read replicas enable asynchronous replication of relational data across distinct Google Cloud regions. This architecture serves a dual purpose: localizing read traffic to geographically distributed clients and serving as a warm standby failover target for disaster recovery (DR).
How It Meets the Requirements
- Disaster Recovery (DR): Placing an asynchronous replica in
europe-west1establishes an independent regional copy that can be promoted to a standalone primary database in the event of a regional outage inus-central1, achieving stringent RTO and RPO objectives. - Network Egress and Latency: Routing European analytical queries locally to the
europe-west1replica eliminates repetitive inter-region network egress charges fromus-central1to Europe and drastically reduces read latency for European users. - Replication Performance: Replication lag occurs when the replica's SQL thread cannot keep up with incoming transactions. Sizing the replica's compute resources (vCPU, memory, IOPS) adequately and breaking large row updates into smaller batches prevents massive write backlogs on the replica.
Key Advantages
- Regional Data Locality: Serves read requests closer to users, improving application responsiveness.
- Cost Efficiency: Incurs one-time cross-region egress for replication rather than recurring egress fees per user query.
- Operational Resilience: Provides a clear, native promotion path for regional disaster recovery.
- Lag Mitigation: Prevents single massive DML operations from stalling the replication stream.
Why This Is Optimal
Locating a properly sized read replica in the destination region balances disaster recovery readiness with localized query performance while explicitly addressing the root causes of replication lag.
Keep all database instances solely in us-central1 with High Availability (HA) enabled across two zones, and configure European analytical clients to query the standby instance directly over Cloud Interconnect.
Keep all database instances solely in us-central1 with High Availability (HA) enabled across two zones, and configure European analytical clients to query the standby instance directly over Cloud Interconnect.
What Cloud SQL High Availability (HA) Is
Cloud SQL High Availability (HA) uses regional persistent disks and synchronous replication between a primary instance and a standby instance located across two different zones within the same region.
Why It Doesn't Meet the Requirements
- Disaster Recovery (DR): Zonal HA protects against single-zone failures within
us-central1, but provides zero disaster recovery protection if the entireus-central1region experiences an outage. - Network Egress and Latency: Every analytical query executed by European applications incurs continuous cross-continent network egress fees and high network round-trip latency.
- Direct Querying Restriction: Cloud SQL standby instances in an HA configuration are completely passive failover targets and cannot serve read traffic directly.
Key Characteristics and Limitations
- Intra-region Only: HA instances are confined to a single geographic region.
- Non-queryable Standby: The standby instance cannot be accessed directly for read offloading.
- Egress Accumulation: Query payloads sent across continents repeatedly generate egress costs.
This Answer Would Be Correct If
The workload only required protection against localized data center zone outages within a single region and had no European users or cross-region DR mandates.
Configure automated hourly Cloud SQL export dumps to a multi-region Cloud Storage bucket in Europe, and restore the bucket dump to a new Cloud SQL instance whenever European analytics runs.
Configure automated hourly Cloud SQL export dumps to a multi-region Cloud Storage bucket in Europe, and restore the bucket dump to a new Cloud SQL instance whenever European analytics runs.
What Automated Database Export and Restore Is
This approach relies on batch database exports (such as SQL or CSV dumps) written to a Cloud Storage multi-region bucket, followed by on-demand provisioning and restoration of an instance to run queries.
Why It Doesn't Meet the Requirements
- Disaster Recovery (DR): An hourly export process creates an RPO of up to one hour, and provisioning/restoring a new database instance results in an unacceptably high RTO (often hours for large datasets).
- Network Cost and Performance: Repeatedly exporting and importing multi-gigabyte or terabyte database files incurs continuous storage read/write operations and compute provisioning overhead.
- Analytical Availability: Analytical clients cannot query near-real-time data, as they must wait for lengthy import processes to complete before executing queries.
Key Characteristics and Limitations
- High RTO/RPO: Backup/restore strategies cannot match the near-zero RTO/RPO of warm read replicas.
- Operational Overhead: Requires complex orchestration to automate instance creation, import, and teardown.
- No Continuous Replication: Data remains stale between export intervals.
This Answer Would Be Correct If
The organization only required cold data archiving for compliance or regulatory audit purposes where multi-hour data staleness and long recovery times are acceptable.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
BigQuery Partitioning and Clustering
Planning for storage costs and performance
AdvancedProfessional Data Engineer
Cloud Storage Object Lifecycle Management
Planning for storage costs and performance
AdvancedProfessional Data Engineer
Dataflow FlexRS Data Locality
Running jobs in multiple regions or zones
AdvancedProfessional Data Engineer
Dataflow DLP Vertex AI Dataplex
AI data enrichment
AdvancedProfessional Data Engineer
IAP TCP Forwarding VPC Service Controls Ingress Rules
Defining data sources and sinks
IntermediateProfessional Cloud Architect
Intra-Region Synchronous, Inter-Region Asynchronous Replication
Familiarity with the Google Cloud Well-Architected Framework
Last updated Validated