Database Disaster Recovery: Cloud SQL Replicas & Spanner Global Instances

Tại Sao Điều Này Quan Trọng

Database là stateful component khó nhất để protect trong disaster recovery. Mất database = mất mọi application state. Cloud SQL và Spanner cung cấp built-in replication, nhưng chúng có fundamental trade-offs:

• Cloud SQL HA replica: Same-region replication (auto-failover), không protection against region failure
• Cloud SQL read replica: Cross-region replication (async, manual failover), flexible nhưng complex
• Spanner multi-region: True active-active, zero RPO, nhưng write latency cao

---

Internal Model: Cloud SQL Replication

High Availability (HA) Replica

Architecture:

Primary Instance (us-central1-a)
├─ MySQL database
├─ Write accepted here
└─ Continuous replication to HA replica

HA Replica (us-central1-b, same region)
├─ MySQL database
├─ Synchronous replication (writes confirmed after both zones)
├─ Read-only (cannot accept writes)
└─ Used for automatic failover only

Replication mechanism:

Write from client:
1. Client sends write to primary
2. Primary executes write (transaction log updated)
3. HA replica receives transaction log
4. HA replica applies write (synchronously)
5. HA replica acknowledges
6. Primary confirms to client

Consistency: Strong (synchronous replication, write confirmed only after both zones).

Failover (automatic):

T=0:00  Primary fails
        (disk corrupted, network down, host crash)

T=0:30  Health check detects failure
        ├─ Primary not responding for 30 seconds
        └─ Failover triggered

T=0:35  Failover complete
        ├─ HA replica promoted to primary
        ├─ New HA replica created
        └─ Clients re-route (DNS TTL = 300 sec)

T=5:35  All clients using new primary
        RTO = 5 min + client reconnection time = 10 min

RPO: Near-zero (synchronous replication, no data loss).

Cost: 1.8× single instance (running replica continuously).

Limitation: Both zones in same region. If entire region down → both zones down.

Read Replicas: Cross-Region Replication

Architecture:

Primary (us-central1)
├─ Accepts writes
└─ Async replication

Read Replica 1 (us-east1)
├─ Async replication
├─ Read-only
└─ Can be promoted to primary (manual)

Read Replica 2 (asia-southeast1)
├─ Async replication
├─ Read-only
└─ Can be promoted to primary (manual)

Replication mechanism:

Write to primary (us-central1):
├─ T=0:00   Write confirmed immediately
├─ T=0:02   Replication to us-east1 (2 ms network latency)
├─ T=0:10   Replication to asia (10 ms network latency)
└─ Each replica might have different lag

Consistency: Eventual (async replication). Reads from replica might see stale data.

Promote to primary (manual):

Primary fails, decide to promote us-east1 replica:

1. Confirm primary is dead (manual decision)
2. Stop replication from primary
3. Promote replica to primary (takes 2–5 min)
4. Update DNS to point to new primary
5. Other replicas re-point to new primary
6. Resume replication

Total failover time: 15–30 min (manual decision + promotion)

RPO: Depends on lag at time of failure. Typical: 1–30 seconds.

Cost: 1.2× per replica (each replica is separate billing).

Use case: Disaster recovery (cross-region), read scaling (distribute reads).

Promotion Complexity

Promoting read replica to primary is not atomic:

Before promotion:
Primary (crashed)
├─ Transaction 1 (replicated to replicas)
├─ Transaction 2 (replicated to replicas)
└─ Transaction 3 (in-flight, not replicated)

Read Replica (before promotion):
├─ Transaction 1 ✓
├─ Transaction 2 ✓
└─ Transaction 3 ✗ (missing)

Replica promoted to primary:
├─ Transaction 1 ✓
├─ Transaction 2 ✓
├─ Transaction 3 ✗ (lost)

Other application code might reference transaction 3 ID
→ Orphaned references, potential data consistency issue

Mitigation: Application must handle promotion gracefully (retry, revalidate).

---

Internal Model: Cloud Spanner Multi-Region

Multi-Region Instance: True Active-Active

Architecture:

Region: us-central1 (leader or replica, chosen by Spanner)
├─ Zone A: primary replica
└─ Zone B: secondary replica

Region: us-east1 (leader or replica)
├─ Zone C: primary replica
└─ Zone D: secondary replica

Region: europe-west1 (leader or replica)
├─ Zone E: primary replica
└─ Zone F: secondary replica

Consensus protocol: Paxos (multi-region)
├─ Leader can be any region (load-balanced)
└─ Quorum: 2 out of 3 regions must agree

Write flow:

Client writes to region A (might not be leader):
1. Request routed to leader (Spanner routes automatically)
2. Leader executes write
3. Leader sends to other regions
4. Regions acknowledge (quorum = 2 regions)
5. Leader confirms to client

Latency: 10–100 ms (cross-region round-trip)

Replication: Synchronous (writes confirmed only after replication complete).

Consistency: Strong (CAP: consistency + partition tolerance, sacrifice availability under partition).

RPO: Near-zero (synchronous replication, quorum-based).

Failover: Automatic (Paxos protocol handles region failure).

Cost: Premium pricing (2.5–3× single-region database).

Advantage: True active-active, strong consistency across regions.

Disadvantage: Write latency (cross-region consensus overhead), cost.

Regional Instance: Single Region, Lower Cost

Spanner also offers regional instances (cheaper, faster):

Single region: us-central1
├─ Multiple replicas in same region (high availability)
└─ No cross-region replication

Cost: 1.5× Cloud SQL HA (but more reliable, managed).

Use case: Single-region deployments where DR handled separately.

---

Comparison: Cloud SQL vs Spanner

Aspect	Cloud SQL HA	Cloud SQL Read Replica	Spanner Multi-Region
RTO (primary failure)	5–10 min (auto)	30+ min (manual)	< 1 min (auto)
RPO	Near-zero	1–30 sec	Near-zero
Write latency	Low (same region)	Low (primary is primary)	High (cross-region consensus)
Consistency	Strong (sync)	Eventual (async)	Strong (sync)
Cost	1.8×	1.2× per replica	3×
Region protection	No (HA in same region)	Yes (cross-region)	Yes (true active-active)
Automatic failover	Yes (within region)	No (manual)	Yes (Paxos)
Complexity	Low	Medium	High

---

DR Patterns: Database Strategy

Pattern 1: Cloud SQL HA + Read Replica for DR

Production: Cloud SQL HA (us-central1)
├─ High availability replica (same region)
├─ Automatic failover within region (RTO = 10 min)
├─ Zero data loss (RPO = 0)

Disaster recovery: Read replica (us-east1)
├─ Cross-region copy
├─ Async replication (RPO = 15 sec typical)
├─ Manual promotion (RTO = 30 min)

Scenario 1: Zone failure in us-central1
├─ Automatic failover to HA replica
└─ RTO = 10 min

Scenario 2: Entire us-central1 region down
├─ Promote us-east1 replica to primary
├─ Application re-routes
└─ RTO = 30–60 min (manual decision + promotion)
└─ RPO = 15 sec (data loss acceptable)

Cost: 1.8× + 1.2× = 3× production database cost.

Decision driver: RTO < 1 hour, RPO < 1 min, cost budget $5K/month.

Pattern 2: Spanner Multi-Region for Zero Downtime

Spanner multi-region instance:
├─ Regions: us-central1, us-east1, europe-west1
├─ Quorum: 2 out of 3 regions
├─ Automatic failover (Paxos protocol)

Failure scenarios:
1. Single region down:
   ├─ Remaining 2 regions have quorum
   ├─ Application continues immediately
   └─ RTO < 1 min, RPO = 0

2. Network partition (2 regions isolated):
   ├─ Quorum lost (only 1 region accessible)
   ├─ Writes blocked (cannot achieve consensus)
   ├─ Reads from accessible region (stale data)
   └─ RTO = indefinite (until network heals)

3. Primary region traffic increase:
   ├─ Spanner auto-routes to secondary region leader
   ├─ Load balanced automatically
   └─ No RTO (transparent to client)

Cost: 3× single-region Spanner.

Decision driver: RTO < 5 min, RPO = 0 non-negotiable, can afford $15K+/month.

Pattern 3: Regional Cloud SQL + Separate Backup Snapshots

Production: Cloud SQL single-region (us-central1)
├─ No HA replica (save cost)
├─ Daily automated backup snapshots
├─ Snapshots stored in multi-region GCS bucket

Disaster recovery: Manual restore
├─ If region fails, restore from snapshot
├─ Create new instance in us-east1
├─ Restore from snapshot (15–30 min)
├─ Point application to new instance

RTO: 5 min detect + 30 min restore = 35 min
RPO: 1 day (last snapshot)
Cost: 1× production database (no replica)

Decision driver: RTO = 1 hour acceptable, RPO = 24 hour acceptable, cost-sensitive.

---

Replica Lag Analysis

Replica lag = difference between primary write time and replica apply time.

Example:

Primary (us-central1):
T=0:00  Write transaction 1
T=0:05  Write transaction 2
T=0:10  Write transaction 3

Read replica (us-east1):
T=0:00  (replicating transaction 1...)
T=0:02  Applies transaction 1
T=0:07  Applies transaction 2
T=0:12  Applies transaction 3

Lag at each time:
T=0:00  lag = 0 ms (no write yet)
T=0:02  lag = 2 ms (transaction 1 replicated)
T=0:07  lag = 2 ms (transaction 2 replicated)
T=0:12  lag = 2 ms (transaction 3 replicated)

But if network slow:
T=0:00  Write transaction 4 (large, 100 MB)
T=0:15  Replication still in progress
T=0:30  Finally applied
Lag = 30 seconds (large write caused lag)

Factors affecting lag:

• Network bandwidth (cross-region links)
• Write volume (large transactions)
• Replica load (other queries on replica)
• Replication buffer (queue of waiting transactions)

Monitoring lag:

In Cloud Console:
Replica instance → Replication tab → "Replication Lag"
Shows: X seconds (updates every 10 seconds)

Set alert: if lag > 60 seconds → investigate

---

Failover Decision Tree

When primary fails:

1. Can auto-failover work?

   • HA replica available (same region)? → Use HA failover (RTO = 10 min)
   • Spanner multi-region? → Automatic via Paxos (RTO < 1 min)
   • Read replica only? → Cannot auto-failover

2. Can manual failover work?

   • Read replica healthy and caught up? → Promote (RTO = 30 min)
   • Backup snapshot available? → Restore (RTO = 45 min)
   • Neither? → Data loss possible

3. What about data in transit?

   • Transactions in-flight (confirmed to client but not replicated)
   • If promote read replica → those transactions lost (RPO = lag at failure time)
   • Application must be prepared to retry/reconcile

---

Common Failure Scenarios

Scenario: Network Partition Between Primary and Replica

Primary (us-central1) isolated from replicas
├─ Cannot reach us-east1 (network down)
├─ Cannot reach us-west1 (network down)

HA replica (same zone as primary) still alive:
├─ Can continue operations
├─ Clients connect successfully

Read replicas disconnected:
├─ Replication stops (lag increases)
├─ Replicas don't receive new writes

When network heals:
├─ Replicas catch up (replication resumes)
├─ Lag returns to normal

If whole region fails (not just network):
├─ Primary + HA replica both down
├─ Application can promote read replica manually

---

Anti-Patterns

Anti-pattern 1: "Cloud SQL HA Means No DR Needed"

Symptom: Team uses HA replica, assumes disaster recovery covered.

Problem:

• HA replica is same-region only (cannot protect against region failure)
• Region disaster → HA replica also down

Right approach: HA replica for high availability (quick recovery within region). Read replica in different region for DR (recovery from region failure).

Anti-pattern 2: "Replica Lag Doesn't Matter"

Symptom: Team relies on read replica for disaster recovery but doesn't monitor lag.

Problem:

• If primary fails during high lag (30 sec), RPO = 30 sec (data loss)
• If lag > 1 minute during heavy writes, might not notice

Right approach: Monitor replica lag continuously. Alert if lag > threshold (e.g., 60 sec).

Anti-pattern 3: "Spanner Multi-Region for All Databases"

Symptom: Team promotes all databases to Spanner multi-region for "maximum safety."

Problem:

• Cost 3× for every database
• Write latency increases (cross-region consensus overhead)
• Only needed if RTO < 5 min + RPO = 0

Right approach: Use Spanner multi-region selectively (critical transaction databases). Use Cloud SQL HA + read replica for typical workloads.

---

Summary

Cloud SQL:

• HA replica: High availability within region, auto-failover
• Read replica: Cross-region DR, manual failover, eventual consistency

Spanner:

• Multi-region: Active-active, automatic failover, strong consistency
• High cost, write latency overhead

Choose based on RTO/RPO targets and cost budget.

---

References

• Cloud SQL High Availability
• Cloud SQL Read Replicas
• Cloud Spanner Multi-Region
• Spanner Regional Configuration
• Replica Lag Monitoring