Reliability

Database High Availability and Disaster Recovery: A Practical Guide

How to design database high availability and disaster recovery that actually holds: agree recovery objectives, choose the architecture pattern, pick the platform building blocks, design the backup layer, build failover into the application, and test on a schedule. Covers SQL Server, Oracle, PostgreSQL and MySQL.

Need a hand with this? Talk to a senior database DBA ↓

By RadixTrie3 September 20264 min read

Ask most organisations whether their databases are highly available and the answer is yes, followed by a diagram. Ask when the failover was last performed on purpose, and the room goes quiet. High availability and disaster recovery are not features you configure once; they are capabilities you prove regularly. This guide sets out how to design them step by step, platform by platform, and how to tell whether your protection would actually hold.

Step 1: Agree two numbers per database

Every database needs a recovery point objective, how much data the business could afford to lose, and a recovery time objective, how long it could be unavailable. Written down, per system, and agreed with the people who own the business process. Everything else follows from these two numbers: whether you need synchronous replication or a nightly backup is enough, whether a second data centre or cloud region is justified, and how much the protection may cost. Without them, HA and DR designs are guesses dressed as architecture.

High availability, disaster recovery and backup are three different things

High availability handles the failure of a server or service with minimal interruption, usually within one site or cloud region, using replicas and automatic or scripted failover.
Disaster recovery handles the loss of a whole site, region or environment, usually with a longer recovery time, from a separate location.
Backups handle the cases the other two cannot: corruption, accidental deletion, ransomware and human error, all of which replicate happily to every standby you own.

A sound design has all three, and a support team that tests all three.

Step 2: Choose the architecture pattern

The recovery objectives point at one of a handful of patterns. Match the pattern to the numbers before choosing any product feature.

Single site, automatic failover. Two or more nodes in one site or availability zone with synchronous replication and a quorum or witness deciding who is primary. Covers server failure with seconds to a few minutes of interruption and no data loss. Does not cover the site.
Active site plus warm standby site. Asynchronous replication to a second site or region, with a planned or scripted switchover. Covers site loss with minutes to hours of recovery and a small window of data loss equal to replication lag.
Synchronous across two nearby sites. No data loss on site failure, at the cost of write latency on every transaction and a strict distance limit. Only justified where the recovery point objective is genuinely zero.
Backup and restore only. For systems whose recovery objectives tolerate hours of downtime and the loss of a day's changes. Legitimate, provided the restore time has been measured against the objective.
Read replicas and reporting standbys. Not high availability on their own, but often part of the design, offloading reporting and giving a candidate for promotion if the primary is lost.

Step 3: Pick the platform building blocks

SQL Server: Always On availability groups for readable, fast-failover replicas; failover cluster instances for instance-level protection on shared storage; log shipping as a simple, robust disaster recovery layer. Edition determines what is available.
Oracle: Data Guard physical standby databases for disaster recovery and switchover, and Real Application Clusters for node-level availability, both Enterprise Edition capabilities. Standard Edition estates need a different design.
PostgreSQL: streaming replication to hot standbys, with cluster managers such as Patroni or repmgr handling failover, plus write-ahead log archiving for point-in-time recovery.
MySQL and MariaDB: asynchronous or semi-synchronous replication, Group Replication and InnoDB Cluster for MySQL, Galera Cluster for MariaDB, with binary log retention supporting point-in-time recovery.
Managed cloud services: zone-redundant and cross-region options exist for most managed database services. They remove the build work; they do not remove the need to agree objectives, test the failover and check what the application does when it happens.

Step 4: Design the backup layer properly

Replication protects against hardware loss and copies your mistakes faithfully. Backups are the only protection against corruption, deletion and ransomware, so they get their own design: full, differential and log or archive backups on a schedule that meets the recovery point objective; retention that matches legal and business needs; at least one copy stored away from the database's own storage and, increasingly, an immutable copy that an attacker with your credentials cannot delete; and a documented point-in-time recovery procedure. Then measure how long a full restore plus point-in-time recovery actually takes, because that number, not the backup schedule, is your real recovery time.

Step 5: Build failover into the application

A database that fails over to a standby the application cannot find has not failed over. Applications should connect through a listener, virtual name or DNS entry with a short time to live rather than a server name; they should retry transient connection errors rather than crash; read-only traffic should be routed to replicas deliberately; and scheduled jobs, integrations, linked servers and credentials must exist on every node that could become primary. Most of what breaks in a real failover is on this list, not in the database.

The technology matters less than the drill. A standby that has never been promoted is a hypothesis. Schedule the switchover, time it, involve the application team, and write down what broke.

Step 6: Test on a schedule

Restore tests on a schedule: a full restore plus point-in-time recovery to a test server, with the elapsed time recorded against the recovery time objective.
Planned switchovers of high-availability replicas during agreed windows, with applications reconnecting and being checked, not just the database coming up.
Disaster recovery exercises at least annually, invoking the secondary site or region as if the primary were gone, including DNS, connection strings, jobs, integrations and the people who need to be reachable.
Evidence kept for each: what was tested, when, by whom, how long it took and what was fixed afterwards. Auditors ask for this; so should the board.

Common failures found in drills

Replicas that stopped replicating months ago without anyone noticing. Standby servers patched to a different version from the primary. Application connection strings hard-coded to one server. Jobs and integrations that only exist on the primary. Backups stored on the same storage as the database. Credentials and firewall rules that only work from the primary site. None of these appear on a diagram; all of them appear in a drill.

Step 7: Monitor the protection itself

Protection degrades quietly between drills, so it needs its own monitoring: replication lag against the recovery point objective, standby databases that have fallen behind or stopped applying changes, backup jobs that failed or ran but produced nothing, configuration drift between primary and standby, and certificate or credential expiry on the secondary site. An alert on any of these is cheaper than discovering it during an outage.

How RadixTrie approaches HA and DR

High availability, disaster recovery and backup validation are part of RadixTrie's support and managed DBA services across SQL Server, Oracle and PostgreSQL. Senior engineers design to the recovery objectives the business actually agrees, and then run the restore tests and switchovers on a schedule so the capability stays real. Our SQL Server, Oracle and PostgreSQL service pages describe the cover, and a database health check is the usual starting point when nobody is sure what the current protection would do.

Related reading: The Database Health Check: What a Good One Covers and What It Finds · SQL Server Support in South Africa: What Good Looks Like · Oracle Database Support in South Africa: Keeping Licensed Estates Healthy and Compliant · MySQL and MariaDB Support: Where Small Databases Cause Big Outages

When did your last failover actually work? If you are not sure, let's find out safely.

Need a hand with this?

Tell us what you run today and when it has to move. A senior database DBA will reply directly, usually within one working day. Prefer email or phone? Write to expert@radixtrie.com or call +27 10 143 0304, or use the contact page to book a 30-minute call.

TAGSHigh AvailabilityDisaster RecoveryBackupSQL ServerOraclePostgreSQLMySQL