Section 12.2: Backup, Recovery, and High Availability

12.2 Backup, Recovery, and High Availability

In database operations, protecting data assets from loss and minimizing downtime are critical responsibilities. High availability (HA) refers to architectures designed to prevent system outages from localized hardware or software failures. Disaster recovery (DR) refers to the policies and procedures required to restore systems following a catastrophic event, such as a site-wide power outage, natural disaster, or cyberattack.

Core Resilience Metrics: RPO and RTO

Database backup and recovery strategies must align with two metrics established by the business through Service Level Agreements (SLAs):

1. Recovery Point Objective (RPO): The maximum acceptable age of data that can be lost due to an outage. RPO is measured in units of time (e.g., minutes, hours, days). If a database has an RPO of 1 hour, the database administration team must design a backup and replication strategy that guarantees no more than 60 minutes of transactions are lost during a disaster. RPO determines how frequently data must be backed up or replicated.
2. Recovery Time Objective (RTO): The maximum acceptable duration of downtime before the database system must be restored to full operational status. RTO is measured in time units. If a system has an RTO of 4 hours, the DBA team must be capable of recovering the database, verifying integrity, and bringing it back online for applications within 4 hours. RTO determines the speed and method of recovery (e.g., restoring from tape vs. failing over to a mirror).

Timeline of a Database Failure:
=========================================================================> Time
[Last Backup] <------ RPO (Allowed Data Loss) ------> [Failure Event]
                                                      [Failure Event] <------ RTO (Allowed Downtime) ------> [System Restored]
=========================================================================> Time

Database Backup Strategies

To meet RPO and RTO targets, DBAs execute a combination of backup operations. Backups copy database files, schemas, and logs to secondary storage media.

• Full Backup: Copies the entire database, including schemas, tables, system tables, and transaction logs. A full backup is the foundation of all recovery plans. It offers the fastest restore time because only one file is needed, but it requires the most storage space and takes the longest time to run.
• Differential Backup: Captures only the data that has changed since the last full backup. As time passes between full backups, differential backups grow in size. To restore a database using a differential strategy, a DBA must restore the last full backup, and then apply only the most recent differential backup. This offers a balance between backup speed and restore speed.
• Incremental Backup: Captures only the data that has changed since the last backup of any type (full, differential, or incremental). Incremental backups are very small and run extremely fast. However, restoring is slow and complex: the DBA must restore the last full backup, followed by every sequential incremental backup in exact chronological order. If any incremental backup in the chain is corrupted or missing, the entire recovery fails.
• Transaction Log Backup: Copies the active portion of the transaction log. Transaction logs record all database transactions in a Write-Ahead Log (WAL) format before they are written to data files. Log backups are essential for databases that require point-in-time recovery (recovering the database to a specific minute or second just prior to a catastrophic query or user error).

Disaster Recovery Site Options

In the event of a total primary data center failure, operations must failover to a secondary recovery site. There are three primary types of disaster recovery sites:

• Cold Site: A secondary facility that has power, cooling, and network connections, but no pre-configured hardware, software, or data. In a disaster, hardware must be shipped to the site, software installed, and database backups retrieved from offsite storage to be restored. Recovery times (RTO) are measured in days or weeks, making this option suitable only for non-critical systems.
• Warm Site: A secondary site equipped with pre-installed hardware and database software. Data backups are restored to the warm site periodically (e.g., daily or weekly). In a disaster, the DBA must apply the most recent transaction logs to bring the data up to date before redirection. Recovery times are measured in hours.
• Hot Site: A fully operational replica of the primary database environment. Data is continuously replicated in real time from the primary site. In a disaster, database failover can occur automatically or within minutes. RTO and RPO are minimized, but hot sites are the most expensive DR option.

High Availability and Clustering

High availability systems are designed to eliminate single points of failure within a data center.

• Clustering: Grouping multiple servers (nodes) to act as a single database system.
  • Active-Passive Failover: One node actively handles queries, while a passive node stands by. If the active node fails, a clustering service automatically initiates a failover, shifting the IP address and storage access to the passive node.
  • Active-Active Clustering: Multiple nodes concurrently read and write to the database, distributing workloads and ensuring immediate resilience if a single node fails.
• Database Mirroring and Replication: Replicating transactions between database instances can be configured in two ways:
  • Synchronous Replication: A transaction is not confirmed to the application as committed until it is written to both the primary and secondary databases. This ensures zero data loss (RPO = 0) but introduces write latency due to network acknowledgment delays.
  • Asynchronous Replication: The primary database commits the transaction immediately and sends the change to the secondary database after a short delay. This provides maximum application write performance but introduces a replication lag, creating a risk of data loss if the primary database fails before the secondary receives the transaction.