Database high availability with replication and failover
When the database stops, the shop, the customer portal and the warehouse usually stop with it. Sortia Labs plans replication and automatic failover so that losing a server has as little impact as possible, and rehearses the switch-over before it matters. Behind this is experience from projects for banks, payment providers, retail and industry.
- Database cluster
- Failover
- Replication
- Disaster recovery
- Oracle Data Guard
- SQL Server Availability Groups
- PrimaryTakes the writes
- ReplicationContinuous
- ReplicaReady to take over
- FailoverPlanned and practised
Building blocks of a highly available database
How much availability you need
An online shop in the Christmas season needs something different from an internal archive. Every additional level of availability costs servers, network and support, so we weigh it against what an hour of downtime costs you.
Synchronous or asynchronous
Synchronous replication keeps committed transactions on a second server too, but costs write time and needs short network paths. Asynchronous replication is faster but can lose the last seconds on failover. We decide per application.
Clusters for your system
PostgreSQL with Patroni, MySQL and MariaDB with Galera or Percona XtraDB Cluster, MongoDB replica sets, Oracle RAC and Data Guard, SQL Server Availability Groups. We work with the tools that belong to your system.
Failover without new addresses
After a failover the application should keep running without anyone changing its configuration. Depending on the environment we use ProxySQL, a proxy in front of the database, a virtual IP or DNS records with a short lifetime.
A second site
A replica in another data centre is meant for the case where power, network or the whole site fails. We plan latency and bandwidth and define who decides on the switch-over in an emergency.
From the goal to a rehearsed failover
- 1
Requirements
Together with the business side and IT we define which systems may be down for how long and what data loss is acceptable. The result is a target design with costs.
- 2
Build
Clusters and replicas are built next to the running system. We synchronise the data, set up monitoring and alerts and write down every step for an emergency.
- 3
Rehearsal
Before go-live we simulate the failure of servers, network and storage, measure the switch-over time and repeat the rehearsal at fixed intervals so the routine stays practised.
Questions about High availability
What is the difference between high availability and disaster recovery?
High availability covers the failure of single servers or disks, usually automatically and within seconds to minutes. Disaster recovery applies when a whole site is lost or data is damaged: then you restore from backup or move to another site. A sound plan needs both.
Does replication replace backups?
No. Replication copies every change to all nodes, usually within seconds, including an accidental DELETE or a faulty import. What helps here is a backup with point-in-time recovery, or a replica that applies changes with a deliberate delay. The backup belongs outside the cluster.
How many servers does a cluster need?
For automatic failover usually three votes: two database servers and a third instance that decides which side carries on when the network splits. Without that majority you risk split brain, two servers that both accept writes. Galera forms the majority from its own nodes, Patroni uses a consensus service such as etcd.
Do we need high availability if the database runs in the cloud?
Servers and whole zones fail in the cloud too. Managed databases offer replicas across several zones, but they have to be booked, configured and tested. We check whether your settings match your goals and what a zone outage would mean for your application.
Read on.
How long may your database be down?
We answer that question together with you and propose an architecture that fits the answer and your budget.