GCP Professional Data Engineer Practice Question

Your company runs an ecommerce platform on Google Cloud. A Cloud SQL for MySQL database in us-central1 currently serves all writes. New objectives have been set:

Zero data loss and automatic fail-over if the primary zone in us-central1 becomes unavailable (RPO 0 s, RTO < 1 min within the region).
During a regional outage of us-central1, recover the service with at most 5 minutes of data loss and 15 minutes of downtime.
Analytics engineers in europe-west1 must be able to run read-only queries that will not affect transactional latency on the primary.
The solution should minimise operational overhead and cost.

Which design best satisfies the objectives?

Deploy separate Cloud SQL HA instances in us-central1 and europe-west1 and maintain bidirectional replication at the application layer for active/active writes.
Create a Cloud SQL instance with High Availability in us-central1 and add a cross-region read replica in europe-west1. Direct analytics traffic to the replica and promote it manually if the primary region becomes unavailable.
Migrate the database to a Cloud Spanner multi-region configuration spanning us-central1 and europe-west1.
Keep a single Cloud SQL instance in us-central1 and schedule hourly exports to Cloud Storage, then import the dumps into an instance in europe-west1 for disaster recovery.

Report Issue

Answer Description

A highly-available (regional) Cloud SQL instance keeps a synchronous standby in a different zone, so a zonal failure triggers an automatic fail-over with no data loss, meeting the intra-region RPO/RTO targets. Creating an asynchronous cross-region read replica in europe-west1 lets analysts issue queries locally without stressing the primary. Because replication is near-real-time, the replica's lag is normally well under the 5-minute RPO budget, and the replica can be manually promoted within 15 minutes to restore service after a regional outage. This solution relies only on managed Cloud SQL features, so operational effort and cost stay lower than running dual active/active primaries or moving to Cloud Spanner.

The dual-HA primary design forces application-level replication and doubles licensing and HA costs.
Spanner delivers automatic multi-region fail-over but is significantly more expensive and adds migration complexity not required by the objectives.
Hourly exports and imports incur far larger RPO (> 1 h) and RTO than allowed and provide no automatic fail-over.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.