High availability concepts: RPO and RTO

Elena starts the second part of the course with a question rather than a technology. "If StockLine stopped at ten o'clock on a Saturday morning in May," she asks the clinic, "how long could the stores keep selling, and how much of the morning's data could we afford to lose?" Walt answers at once: the tills can sell offline for about an hour, but after that they cannot check stock or prices for special orders, and losing even a few minutes of sales would mean stock counts that no longer match the shelves. Keiko Tanabe, who runs reporting, answers differently for the sales history database: a day without it would be annoying, and data loss of a day could be reloaded from the stores. Those two answers are the whole of this lesson. Before choosing any technology, a DBA must know how much downtime and how much data loss each database can tolerate, because those numbers decide what to build and what it will cost.

Two numbers for every database

The recovery time objective, or RTO, is the longest time a service may be unavailable after a failure before the business is seriously harmed. The recovery point objective, or RPO, is the largest amount of data, measured as time, that may be lost. An RPO of 5 minutes means that after recovery, the database may be missing at most the last 5 minutes of changes before the failure. An RTO of 1 hour means that within an hour of the failure, users must be working again. The two are independent: a reporting database might accept a day of data loss but need to be back within an hour for a board meeting, while an archive might accept a week of downtime but no data loss at all.

Two related ideas often appear beside them. The service level agreement, or SLA, is Microsoft's commitment for the availability of a service over a month, with service credits if it falls short; it is not a promise that any single failure will be short. The service level objective that the business sets for itself is usually stricter and covers the whole application, not just the database. And there is the difference between high availability and disaster recovery. High availability keeps a database running through local failures, such as a failed server, disk or rack, usually automatically and within seconds or minutes, inside one region. Disaster recovery restores service after a larger event, such as a whole data center or region becoming unavailable, a serious human error or corruption, usually in another region and often with a decision by a person.

Where the RTO goes

An RTO is not the time a failover takes. It is the time from the failure to the moment users are working again, and the failover is usually only a small part of it. Someone, or something, must notice the failure. Someone must decide that this is a disaster rather than a blip, which on a Saturday morning means reaching the on-call person and possibly a manager. Then the recovery itself runs, applications reconnect, and someone checks that the data and the application are right. Walt measured each of these steps during an old server-room test, and his conclusion still holds: the technology was the fastest part.

Setting objectives with the business

Recovery objectives are business decisions, not DBA decisions. The DBA's job is to ask the right questions and show the cost of each answer. Elena uses a short business impact interview for each system: what stops working when this database is down, who notices first, what it costs per hour (lost sales, staff idle, penalties, reputation), whether data lost in a failure can be recreated from somewhere else, and whether there are busy periods when the impact is much higher. The answers are grouped into a few tiers, because a handful of standard designs is easier to build, test and explain than a custom design for every database.

The cost of each minute

Lower RPO and RTO cost more. A standby database in another region doubles the compute bill if it is kept the same size, although a passive disaster-recovery replica that is used only for failover can be exempt from SQL Server license charges when it is designated as a standby under Microsoft's failover rights. Zone redundancy adds a smaller premium in General Purpose and none in Business Critical for SQL Database. Every option also costs people's time: designs must be tested, runbooks kept current and staff trained. The DBA's task is to show the business the cheapest design that meets the objective, and the next cheaper design and what it would give up.

Availability, SLAs and composite systems

An SLA expressed as a percentage translates into minutes of allowed downtime. A 30-day month has 43,200 minutes. An SLA of 99.99 in every 100 allows about 4.3 minutes of downtime a month; 99.995 allows about 2.2 minutes; 99.9 allows 43.2 minutes. When an application depends on several services in a chain, all of which must be up, their availabilities multiply, so the whole is less available than any part. When services are redundant, so that either of two copies is enough, the chance that both are down at once is the product of their unavailabilities, and the whole is more available than either.

minutes = 30 * 24 * 60
def downtime(avail):
    return (1 - avail) * minutes

chain = 0.9999 * 0.9995 * 0.9999        # database, app service, gateway all needed
print(f"Chain availability {chain*100:.3f} in 100, about {downtime(chain):.1f} minutes down a month")

pair = 1 - (1 - 0.9999) * (1 - 0.9999)   # either of two regions is enough
print(f"Two independent regions {pair*100:.6f} in 100, about {downtime(pair)*60:.2f} seconds down a month")

Chain availability 99.930 in 100, about 30.2 minutes down a month
Two independent regions 99.999999 in 100, about 0.03 seconds down a month

The second result is optimistic, because two regions are rarely fully independent: a bad deployment, a shared identity service or a person's mistake can affect both. Walt notes that in the margin of his decision log. The SLA percentages themselves come from Microsoft's published service level agreements, which change over time and differ by tier and configuration, so the lesson that follows gives the current figures with their conditions rather than treating any number as permanent.

Hybrid and Azure-specific options

The study guide asks a DBA to evaluate HA and DR for hybrid deployments as well as Azure-only ones. During Project Skylight, Harrow & Finch will be hybrid for at least a year. Several designs span the boundary. The Managed Instance link replicates databases from SQL Server on premises to a managed instance, first for migration and, with SQL Server 2022 or later, as a disaster-recovery replica that can fail over to Azure and back. An Always On availability group can include an on-premises replica and an Azure VM replica. Backups to Azure Blob Storage from on-premises servers give an offsite copy for little effort. And Azure Site Recovery can replicate whole virtual machines, although for SQL Server the database-level options usually give better RPO.

Recovery objectives also change over time. StockLine's RTO was four hours in 2015, before the stores began selling online with in-store pickup; it is one hour now because a web order that cannot be confirmed is a lost sale. Elena schedules a review of every tier each year with the business owners, and whenever a system takes on a new role.