--- PDF PAGE 471 ---
458
Availability Concepts
In order to analyze the HA and DR requirements of an application and implement the 
most appropriate solution, you need to understand various concepts. We discuss these 
concepts in the following sections.
Level of Availability
The amount of time that a solution is available to end users is known as the level of 
availability, or uptime. To provide a true picture of uptime, a company should measure 
the availability of a solution from a user’s desktop. In other words, even if your SQL 
Server has been running uninterrupted for over a month, users may still experience 
outages to their solution caused by other factors. These factors can include network 
outages or an application server failure.
In some instances, however, you have no choice but to measure the level of 
availability at the SQL Server level. This may be because you lack holistic monitoring 
tools within the Enterprise. Most often, however, the requirement to measure the level 
of availability at the instance level is political, as opposed to technical. In the IT industry, 
it has become a trend to outsource the management of data centers to third-party 
providers. In such cases, the provider responsible for managing the SQL servers may not 
necessarily be the provider responsible for the network or application servers. In this 
scenario, you need to monitor uptime at the SQL Server level to accurately judge the 
performance of the service provider.
The level of availability is measured as a percentage of the time that the application 
or server is available. Companies often strive to achieve 99%, 99.9%, 99.99%, or 99.999% 
availability. As a result, the level of availability is often referred to in 9s. For example, five 
9s of availability means 99.999% uptime and three 9s means 99.9% uptime.
Table 13-1 details the amount of acceptable downtime per week, per month, and per 
year for each level of availability.
Chapter 13  High Availability and Disaster Recovery Concepts

--- PDF PAGE 472 ---
459
To calculate other levels of availability, you can use the script in Listing 13-1. Before 
running this script, replace the value of @Uptime to represent the level of uptime that you 
wish to calculate. You should also replace the value of @UptimeInterval to reflect uptime 
per week, month, or year.
Listing 13-1.  Calculating the Level of Availability
DECLARE @Uptime        DECIMAL(5,3) ;
--Specify the uptime level to calculate
SET @Uptime = 99.9 ;
DECLARE @UptimeInterval VARCHAR(5) ;
--Specify WEEK, MONTH, or YEAR
SET @UptimeInterval = 'YEAR' ;
DECLARE @SecondsPerInterval FLOAT ;
--Calculate seconds per interval
SET @SecondsPerInterval =
(
SELECT CASE
        WHEN @UptimeInterval = 'YEAR'
                THEN 60*60*24*365.243
Table 13-1.  Levels of Availability
Level of Availability
Downtime per Week
Downtime per Month
Downtime per Year
99%
1 hour, 40 minutes,  
48 seconds
7 hours, 18 minutes,  
17 seconds
3 days, 15 hours,  
39 minutes, 28 seconds
99.9%
10 minutes, 4 seconds
43 minutes, 49 seconds
8 hours, 45 minutes,  
56 seconds
99.99%
1 minute
4 minutes, 23 seconds
52 minutes, 35 seconds
99.999%
6 seconds
26 seconds
5 minutes, 15 seconds
All values are rounded down to the nearest second.
Chapter 13  High Availability and Disaster Recovery Concepts

--- PDF PAGE 476 ---
463
 Other intangible costs can include loss of staff morale, which leads to higher staff 
turnover or even loss of company reputation. Because intangible costs, by their very 
nature, can only be estimated, the industry rule of thumb is to multiply the tangible costs 
by three and use this figure to represent your intangible costs.
Once you have an hourly figure for the total cost of downtime for your application, 
you can scale this figure out, across the predicted lifecycle of your application, and 
compare the costs of implementing different availability levels. For example, imagine 
that you calculate that your total cost of downtime is $2,000/hour and the predicted 
lifecycle of your application is 3 years. Table 13-2 illustrates the cost of downtime for your 
application, comparing the costs that you have calculated for implementing each level 
of availability, after you have factored in hardware, licenses, power, cabling, additional 
storage, and additional supporting equipment, such as new racks, administrative costs, 
and so on. This is known as the total cost of ownership (TCO) of a solution.
In this table, you can see that implementing five 9s of availability saves $525,474 over 
a two-9s solution, but the cost of implementing the solution is an additional $802,000, 
meaning that it is not economical to implement. Four 9s of availability saves $520,334 
over a two-9s solution and only costs an additional $354,000 to implement. Therefore, 
for this particular application, a four-9s solution is the most appropriate level of service 
to design for.
Classification of Standby Servers
There are three classes of standby solution. You can implement each using different 
technologies, although you can use some technologies to implement multiple classes 
of standby server. Table 13-3 outlines the different classes of standby that you can 
implement.
Table 13-2.  Cost of Downtime
Level of Availability
Cost of Downtime (3 Years)
Cost of Availability Solution
99%
$525,600
$108,000
99.9%
$52,560
$224,000
99.99%
$5,256
$462,000
99.999%
$526
$910,000
Chapter 13  High Availability and Disaster Recovery Concepts

--- PDF PAGE 476 ---
463
 Other intangible costs can include loss of staff morale, which leads to higher staff 
turnover or even loss of company reputation. Because intangible costs, by their very 
nature, can only be estimated, the industry rule of thumb is to multiply the tangible costs 
by three and use this figure to represent your intangible costs.
Once you have an hourly figure for the total cost of downtime for your application, 
you can scale this figure out, across the predicted lifecycle of your application, and 
compare the costs of implementing different availability levels. For example, imagine 
that you calculate that your total cost of downtime is $2,000/hour and the predicted 
lifecycle of your application is 3 years. Table 13-2 illustrates the cost of downtime for your 
application, comparing the costs that you have calculated for implementing each level 
of availability, after you have factored in hardware, licenses, power, cabling, additional 
storage, and additional supporting equipment, such as new racks, administrative costs, 
and so on. This is known as the total cost of ownership (TCO) of a solution.
In this table, you can see that implementing five 9s of availability saves $525,474 over 
a two-9s solution, but the cost of implementing the solution is an additional $802,000, 
meaning that it is not economical to implement. Four 9s of availability saves $520,334 
over a two-9s solution and only costs an additional $354,000 to implement. Therefore, 
for this particular application, a four-9s solution is the most appropriate level of service 
to design for.
Classification of Standby Servers
There are three classes of standby solution. You can implement each using different 
technologies, although you can use some technologies to implement multiple classes 
of standby server. Table 13-3 outlines the different classes of standby that you can 
implement.
Table 13-2.  Cost of Downtime
Level of Availability
Cost of Downtime (3 Years)
Cost of Availability Solution
99%
$525,600
$108,000
99.9%
$52,560
$224,000
99.99%
$5,256
$462,000
99.999%
$526
$910,000
Chapter 13  High Availability and Disaster Recovery Concepts

--- PDF PAGE 477 ---
464
Note  Cold standby does not show an example technology because no 
synchronization is required and, thus, no technology implementation is required. 
For example, in a cloud scenario, you may have a VMWare SDDC in an AWS 
availability zone. If an availability zone is lost, automation spins up an SDDC in a 
different availability zone and restores VM snapshots from an S3 bucket.
High Availability and Recovery Technologies
SQL Server provides a full suite of technologies for implementing high availability and 
disaster recovery. The following sections provide an overview of these technologies and 
discuss their most appropriate uses.
AlwaysOn Failover Clustering
A Windows cluster is a technology for providing high availability in which a group of up 
to 64 servers works together to provide redundancy. An AlwaysOn Failover Clustered 
Instance (FCI) is an instance of SQL Server that spans the servers within this group. If 
one of the servers within this group fails, another server takes ownership of the instance. 
Its most appropriate usage is for high availability scenarios where the databases are large 
Table 13-3.  Standby Classifications
Class Description
Example Technologies
Hot
A synchronized solution where failover can occur automatically or 
manually. Often used for high availability.
Clustering, AlwaysOn 
Availability Groups 
(synchronous)
Warm A synchronized solution where failover can only occur manually. 
Often used for disaster recovery.
Log Shipping, AlwaysOn 
Availability Groups 
(asynchronous)
Cold
An unsynchronized solution where failover can only occur manually. 
This is only suitable for read-only data, which is never modified.
–
Chapter 13  High Availability and Disaster Recovery Concepts

--- PDF PAGE 483 ---
470
This can have unpredictable and undesirable consequences for any application that 
successfully connects to one or the other partition. The Quorum = (Voting Members / 2) + 1 
formula protects against this scenario.
Tip  If your cluster loses quorum, then you can force one partition online, by 
starting the cluster service using the /fq switch. If you are using Windows Server 
2012 R2 or higher, then the partition that you force online is considered the 
authoritative partition. This means that other partitions can automatically rejoin the 
cluster when connectivity is reestablished.
Various quorum models are available and the most appropriate model depends on 
your environment. Table 13-4 lists the models that you can utilize and details the most 
appropriate way to use them.
Although the default option is one node, one vote, it is possible to manually remove 
a node vote by changing the NodeWeight property to zero. This is useful if you have a 
multi-subnet cluster (a cluster in which the nodes are split across multiple sites). In this 
scenario, it is recommended that you use a file-share witness in a third site. This helps 
you avoid a cluster outage as a result of network failure between data centers. If you have 
an odd number of nodes in the quorum, however, then adding a file-share witness leaves 
you with an even number of votes, which is dangerous. Removing the vote from one of 
the nodes in the secondary data center eliminates this issue.
Table 13-4.  Quorum Models
Quorum Model
Appropriate Usage
Node Majority
When you have an odd number of nodes in the cluster
Node + Disk Witness Majority
When you have an even number of nodes in the cluster
Node + File Share Witness 
Majority
When you have nodes split across multiple sites or when you have 
an even number of nodes and are required to avoid shared disks∗
*Reasons for needing to avoid shared disks due to virtualization are discussed later in this chapter.
Chapter 13  High Availability and Disaster Recovery Concepts