High Availability (HA)
High availability solutions mask the effects of a hardware and software failure and maintain the availability of system so that the perceived downtime for users is minimized.
Zero downtime is only required for highly critical systems such as GPS and live broadcasting. Excessive downtime reduction can slow down the system and increase cost due to required redundancy. For most businesses an acceptable downtime can be determined by conducting a tradeoff analysis. We consider the following items while analyzing your business requirements to determine the appropriate HA solution for your business.
- Downtime Tolerance
- Level of failover expectations
- Budget
- Scope
- Network topology and capacity
- Number of required secondary nodes
- SQL Server Edition
- Business environment
- Database type and size
- Expertise level of customer support team
- Strengths and weaknesses of HA solutions
Other Advantages of High Availability solutions
- Transfer data to other locations
- Synchronize data among offline, online and isolated databases
- Recover data and database
- Handle read-only workloads such as reports, backups and BI data read
- Decrease blocks and deadlocks by avoiding read/write contention
The five existing SQL Server HA solutions bellow can be used individually or in combination to offer different level of availability, downtime and failover functionality.
- AlwaysOn (Available only on SQL Server 2012)
- Replication
- Mirroring
- Log Shipping
- Clustering
- VMware HA
Tolerance for downtime
Implementation methods
- AlwaysOn
- Mirroring
- Clustering
Features
- Quick recovery time
- Automatic failover
- Higher implementation cost
- More maintenance requirement
Implementation methods
- Replication
- Log shipping
- VMware
Features
- Medium recovery time
- Manual failover
- Medium implementation cost
- Medium maintenance requirement
Implementation methods
- Full back up
- Differential backup
- Transactional backup
Features
- Long recovery time
- Manual failover
- Lower implementation cost
- Less maintenance requirement
Relationship of Availability and Downtime
The following table shows the downtime per month that will be allowed for a particular percentage of availability.
| Availability % | Downtime/mo. |
|---|---|
| 90% | 72 hours |
| 95% | 36 hours |
| 97% | 21.6 hours |
| 98% | 14.4 hours |
| 99% * | 7.20 hours |
| 99.5% | 3.60 hours |
| 99.8% | 86.23 minutes |
| 99.9% | 43.8 minutes |
| 99.95% | 21.56 minutes |
| 99.99% | 4.32 minutes |
| 99.999% ** | 25.9 seconds |
| 99.9999% | 2.59 seconds |
| 99.99999% | 0.259 seconds |
(Source: Wikipedia)
* Availability limit of 99% would allow an average of 15 minutes per day which still can be handled by manual failover. Any downtime less than 15 minutes requires automatic failover.
** A widely-held but difficult-to-achieve standard of availability for a system or product is known as "five 9s" (99.999 percent) availability.
