Managed Databases
Intermediate 14 min read Managed Databases

Database High Availability

A database that survives losing its host

Database HA means automatic failover with bounded downtime: synchronous standby, health-based promotion, application reconnect, and drained queueing. The trick is testing the failover, not assuming it.

high availabilityfailovermulti-AZstandbyRTOdatabase failure
Prerequisites: Relational Managed Databases
Before we start

What you will learn

  • Explain synchronous vs asynchronous replication for HA
  • Design multi-AZ/zone failover
  • Make applications survive a DB failover
In 60 seconds

A standby in another AZ replicates from the primary either synchronously (zero committed-write loss, higher latency) or asynchronously (fast, small lag window). On primary failure, readiness switches, DNS/endpoint repoints, and the app reconnects — if it's designed to.

Introduction

What is this?

AWS RDS Multi-AZ/ Aurora cluster failover (primary + replica promotion), Azure SQL geo/auto-failover groups, Cloud SQL high-availability configs (primary + failover replica in another zone). Semantics: synchronous standby commits with the primary (zero loss on failover); async replicas risk losing the lag window. RTO targets: seconds-to-minutes depending on detection, DNS TTL, and app reconnect behavior.

Motivation

Why do you need it?

  • A DB outage is a hard service outage; HA shrinks it to seconds.
  • Multi-AZ/zone placement protects against rack/power/linkfail.
  • Automated failover beats morning-pager-whack-a-mole.
  • Well-tested failover = confident releases and calmer incidents.
Think about it

Mental model

Imagine

Two pilots watching the same instruments, ready to take the yoke

Think of

Database primary + standby

One pilot flies (primary), the co-pilot mirrors every action (replication). If the pilot stops responding, the co-pilot takes over without the passengers even noticing the swap.

Mechanics

How it works

  • Synchronous standby (RDS Multi-AZ, Cloud SQL HA): transaction commits only after the standby acknowledges — no write loss, slight latency +.
  • Async replicas (read replicas promoted to primary): faster writes, accept up to seconds of lag.
  • Detection: health checks/watchdogs on heartbeat; promotion flips the endpoint/DNS.
  • Application reconnect: connection pools must retry with backoff and handle the new endpoint.
  • Failback: after recovery, re-sync and return roles (or let the promoted replica stay primary).

Failover flow.

Primary healthy
Primary fails
Detect
Promote standby
App reconnects
Hands-on

Examples

Everyday failover-safe connection pattern

Everyday failover-safe connection pattern
# python: reconnect with retry/backoff through the cluster endpoint
import time
def query_with_failover(sql, attempts=5):
    for i in range(attempts):
        try:
            return conn.execute(sql)   # conn = cluster endpoint
        except OperationalError:
            time.sleep(min(2 ** i, 30))  # exponential backoff
    raise
# writer uses the primary/cluster writer endpoint;
# reads may use a read replica endpoint

Retrying through the cluster endpoint hides the failover window from callers.

Real world

In production

  • A finance write path uses RDS Multi-AZ Postgres; failover drills keep RTO under 60s.
  • Azure SQL failover groups route DNS across regions with automatic failover on outage.
  • Cloud SQL HA holds p95 availability while a mobile backend reconnects via an HA proxy.
Guidelines

Best practices

  • Use cluster/writer endpoints and let the platform flip them at failover.
  • Test failover deliberately: once per release or quarterly — measure RTO.
  • Bound connection pool size and retry budget so failover doesn't stampede restarts.
  • Keep DNS TTL low on the endpoint for external clients.
Watch out

Common mistakes

  • Async-only HA and a 'we're fine' story — then losing committed writes to a lagging replica.
  • Connection pools that crash on transient failures instead of retrying.
  • Failover tests skipped 'because it's risky' — the risk is assuming it works.
  • Half the estate multi-AZ, the other half single zone, discovered mid-incident.
Root cause analysis

Common issues

Error
Failover takes minutes, not seconds
Root cause

DNS TTL, connection pools, or detection interval

Fix

Lower DNS TTL, use cluster endpoint, and check detection heartbeat cadence.

Error
Data loss on failover
Root cause

Async replication + lag window

Fix

Move to synchronous standby for write-critical workloads or accept the window explicitly.

When things break

Debugging tips

  • Failover didn't happen: check the health-check frequency vs TCP timeouts.
  • App stuck after failover: DNS TTL cached old IP — use the cluster endpoint and reconnect logic.
  • Write loss suspected: verify sync vs async on the replication config.
  • Promotion lag: monitor replica lag; set failover thresholds accordingly.
Interviews

Interview checkpoint

Practice

Hands-on lab

Plan and Simulate a Database Failover
25 min · intermediate
In progress
Objectives
  • Design HA topology with sync/async semantics
  • Simulate failover and reconnect
  • Measure implied RTO from the design

Plan and Simulate a Database Failover

25 min · intermediate

0/4 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/1
1

Synchronous replication trades ... for ...

Go deeper

Official documentation

AWS — RDS Multi-AZ
docs
Azure — Failover groups
docs
GCP — Cloud SQL HA
docs