Database Failover and Disaster Recovery Drills
Database failover moves database service from a failed or planned-to-stop primary to a standby. Disaster recovery drills rehearse that move, validate the recovered data and application path, and prove that recovery objectives can be met.
itDatabases and data storage | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Intro
Database Failover and Disaster Recovery Drills
Database failover transfers the writable database role from a primary to another database instance. A disaster recovery drill rehearses that transfer, the surrounding application recovery, and the return to a protected state. The drill produces evidence about recovery time, recoverable data, application behavior, and operator readiness.
Failover and disaster recovery address related but different failure scopes. High availability usually handles a component, node, or availability-zone failure with a nearby standby. Disaster recovery handles loss or isolation of a larger fault domain, such as a site or region. A nearby synchronous standby can shorten interruption, but it can share the same disaster. A distant asynchronous replica limits shared risk, but replication lag can expose recent transactions to loss.
The recovery system
A working recovery design contains more than two database servers:
Clients -> stable endpoint or router -> current primary
| |
| +-> replication stream -> standby
|
+-> role and health control plane
Independent backup repository -> point-in-time restore target
Monitoring, identity, DNS, secrets, networks, and application services surround every path.
The data plane carries queries and replication. The control plane detects health, chooses a promotion target, changes roles, and redirects clients. The recovery plane supplies backups, archived logs, runbooks, credentials, and infrastructure when live replicas are unavailable or unsafe.
A replica is not a backup. Replication can copy an accidental deletion or corrupt change to every live replica. Independent backups and point-in-time recovery preserve an earlier state. A complete design tests both role transition and restoration.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://csrc.nist.gov/pubs/sp/800/34/r1/upd1/final
Supports
- Contingency planning lifecycle, recovery objectives, testing, training, exercises, and maintenance
- May 2010 publication record and authoritative primary grounding
- https://nvlpubs.nist.gov/nistpubs/legacy/sp/nistspecialpublication800-34r1.pdf
Supports
- Tabletop and functional exercise definitions, roles, test objectives, after-action review, and plan updates
- https://nvlpubs.nist.gov/nistpubs/Legacy/SP/nistspecialpublication800-84.pdf
Supports
- Test, training, and exercise program progression and full-scale readiness validation
- https://docs.cloud.google.com/architecture/framework/reliability/perform-testing-for-recovery-from-data-loss
Supports
- Data integrity, RTO, and RPO as recovery-test success criteria
- Regular restores, backup monitoring, documentation, and team training
- https://docs.cloud.google.com/architecture/dr-scenarios-planning-guide
Supports
- DR environment security, identity, audit, training, dependencies, and production-equivalent recovered-data protection
- https://docs.aws.amazon.com/whitepapers/latest/disaster-recovery-workloads-on-aws/disaster-recovery-options-in-the-cloud.html
Supports
- Backup and restore, pilot light, warm standby, and multi-site recovery strategies
- Need for regular DR assessment and testing
- https://docs.aws.amazon.com/prescriptive-guidance/latest/strategy-database-disaster-recovery/defining.html
Supports
- Database RPO, RTO, cost, and infrastructure tradeoffs across DR strategies
- https://www.postgresql.org/docs/current/warm-standby.html
Supports
- Primary and standby architecture, asynchronous and synchronous replication, lag, timelines, promotion readiness, and durability tradeoffs
- https://www.postgresql.org/docs/current/warm-standby-failover.html
Supports
- Fencing and split-brain risk, promotion, degenerate post-failover state, written procedures, regular switching, and standby reconstruction
- https://www.postgresql.org/docs/current/app-pgrewind.html
Supports
- Rejoining a diverged former primary to the new timeline after failover
- https://www.postgresql.org/docs/current/logical-replication-failover.html
Supports
- Logical slot readiness and downstream continuity across publisher failover
- https://dev.mysql.com/doc/refman/8.4/en/group-replication.html
Supports
- Group membership, single-primary election, consistency, network partitions, monitoring, and distributed recovery
- https://dev.mysql.com/doc/mysql-router/8.0/en/
Supports
- MySQL Router placement between applications and InnoDB Cluster members for topology-aware routing
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groups
Supports
- Planned manual, automatic, and forced failover conditions, synchronization, and possible data loss
- https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/availability-groups/troubleshooting-availability-group-failover
Supports
- Role transitions, application interruption, health detection, diagnostics, and recovery behavior
- https://docs.oracle.com/en/database/oracle/oracle-database/26/sbydb/managing-oracle-data-guard-role-transitions.html
Supports
- Data Guard switchover, failover, Flashback, and former-primary reinstatement
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/multi-az-db-clusters-concepts-failover.html
Supports
- Managed Multi-AZ writer failover, manual drill action, endpoint behavior, and client DNS considerations
- https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db
Supports
- Regional failover groups, failover policy, grouped databases, and business-continuity behavior
- https://docs.cloud.google.com/sql/docs/mysql/high-availability
Supports
- Cloud SQL zonal standby, automatic failover, backup prerequisites, and regional availability boundary
- https://www.ibm.com/docs/en/db2/12.1.0?topic=server-high-availability-disaster-recovery-hadr
Supports
- Db2 primary-to-standby log replication, site failure coverage, takeover, and client reroute
- https://www.cockroachlabs.com/docs/stable/disaster-recovery-planning/
Supports
- CockroachDB replication factors, zone and region survival boundaries, failure recovery, and backup restoration
- https://docs.yugabyte.com/stable/yugabyte-platform/back-up-restore-universes/disaster-recovery/
Supports
- Planned switchover, forced failover, asynchronous lag, RPO consequence, application connection transition, and failback
- https://github.com/sindresorhus/awesome
Supports
- Required Awesome-list discovery starting point and routes to PostgreSQL and MySQL lists
- https://github.com/dhamaniasad/awesome-postgres
Supports
- Discovery of Patroni, repmgr, pgBackRest, Barman, and WAL-G in high-availability and backup sections
- https://github.com/shlomi-noach/awesome-mysql
Supports
- Discovery of Galera Cluster, ProxySQL, and Percona XtraBackup in HA, proxy, and backup sections
- https://patroni.readthedocs.io/en/latest/
Supports
- PostgreSQL HA control plane, distributed configuration stores, switchover, failover, replication modes, watchdog, pause, and testing
- https://www.repmgr.org/docs/current/
Supports
- Standby clone, promote, follow, switchover, automatic failover, health check, rejoin, and event history
- https://pgbackrest.org/user-guide.html
Supports
- Full, differential, and incremental backups, WAL, restore, PITR, standby backup, and repository checks
- https://docs.pgbarman.org/
Supports
- PostgreSQL backup management, WAL archiving, recovery, and standby integration
- https://wal-g.readthedocs.io/
Supports
- Database backup and transaction-log archival to object storage across supported engines
- https://galeracluster.com/library/documentation/index.html
Supports
- Galera virtually synchronous multi-primary architecture, quorum, node state, and state transfer
- https://proxysql.com/documentation/
Supports
- MySQL backend host groups, monitoring, routing, and failover-related endpoint control
- https://docs.percona.com/percona-xtrabackup/8.4/
Supports
- MySQL physical hot backup, incremental backup, prepare, restore, and recovery workflows
- https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a88808/title.htm
Supports
- June 2001 Oracle9i Data Guard documentation milestone
- https://docs.oracle.com/cd/B10500_01/server.920/a96653/whatsnew.htm
Supports
- Oracle9i Data Guard physical and logical standby, broker, switchover, and protection features
- https://www.postgresql.org/about/news/postgresql-800-released-277/
Supports
- January 2005 PostgreSQL point-in-time recovery milestone
- https://www.postgresql.org/about/news/postgresql-90-final-release-available-now-1235/
Supports
- September 2010 PostgreSQL streaming replication and hot standby milestone
- https://www.postgresql.org/docs/9.0/release-9-0.html
Supports
- WAL streaming, hot standby behavior, and PostgreSQL 9.0 release date
- https://www.microsoft.com/en-us/sql-server/blog/2012/04/02/sql-server-2012-is-generally-available/
Supports
- April 2012 SQL Server AlwaysOn general availability milestone
- https://aws.amazon.com/blogs/aws/cross-region-read-replicas-for-amazon-rds-for-mysql/
Supports
- November 2013 RDS MySQL cross-Region replica promotion and DR milestone
- https://aws.amazon.com/blogs/aws/now-available-amazon-aurora
Supports
- July 2015 Aurora general availability, cross-zone replicated storage, replicas, and failover-target milestone
- https://www.postgresql.org/docs/9.5/release-9-5.html
Supports
- January 2016 pg_rewind release milestone and post-failover reintegration purpose
- https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-17.html
Supports
- December 2016 Group Replication milestone, membership, consistency, and automatic election
- https://azure.microsoft.com/en-us/blog/azure-sql-database-now-supports-transparent-geographic-failover-of-multiple-databases-featuring-automatic-activation/
Supports
- May 2017 Azure SQL auto-failover group general availability and stable endpoint milestone
- https://aws.amazon.com/about-aws/whats-new/2021/11/multi-az-deployment-amazon-rds-postgre-sql/
Supports
- November 2021 RDS three-zone readable-standby Multi-AZ cluster preview milestone
- https://aws.amazon.com/rds/
Supports
- Amazon RDS Landscape identity and managed database recovery placement
- https://azure.microsoft.com/en-us/products/azure-sql/database
Supports
- Azure SQL Database Landscape identity and regional failover placement
- https://cloud.google.com/sql
Supports
- Google Cloud SQL Landscape identity and zonal high-availability placement
- https://aws.amazon.com/rds/aurora/
Supports
- Amazon Aurora Landscape identity and replica or global recovery placement
- https://www.oracle.com/database/data-guard/
Supports
- Oracle Data Guard Landscape identity and role-transition placement
- https://www.microsoft.com/en-us/sql-server/sql-server-downloads
Supports
- SQL Server Landscape identity and availability-group placement
- https://www.ibm.com/products/db2
Supports
- IBM Db2 Landscape identity and HADR placement
- https://www.mysql.com/products/mysql-innodb-cluster/
Supports
- MySQL InnoDB Cluster Landscape identity and group replication plus routing placement
- https://www.cockroachlabs.com/product/
Supports
- CockroachDB Landscape identity and distributed SQL resilience placement
- https://www.yugabyte.com/yugabytedb/
Supports
- YugabyteDB Landscape identity and xCluster recovery placement