Dell POWEREDGE R720XD - Manual (Page 15)

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn
Availability Groups
15
In the above sample implementation, two PowerEdge R720 servers in the primary site were deployed
with PCIe SSD drives. The databases that are part of the AVG are stored on the SSD drives. Remaining
databases may be stored on the traditional hard drives. These two servers should be configured in
synchronous commit mode with automatic failover. In order to avoid network bottleneck between the
hosts, make sure that the enough network bandwidth is deployed between the hosts. This will help to
achieve high performing AVG databases, with fast failover/failback capabilities.
As per the sample implementation shown in Figure 7, the DR site may have less costly hard drives
(and/or storage) to host the secondary replica. The DR replicas should be configured in Asynchronous
commit mode. This implementation will help the customer reduce the overall cost of the entire HADR
configuration.
Advantages and limitations of implementing AVG on hybrid (SSDs and HDDs)
storage
Advantages:
 Simple and cost effective solution for SMB customers who cannot invest in SAN
 Improved DB performance with PCIe SSDs
 Quicker failover and failback times compared to other solutions
Limitations:
 Limited storage scalability
 No enhanced SAN capabilities
Flexible failover policies and enhanced diagnostics for detecting
failover conditions
SQL Server 2012 introduces more flexible failover polices and a rich set of diagnostics that enable WSFC
to detect failures and take required actions as soon as possible. The SQL Server setup periodically
reports a set of component diagnostics to the WSFC resource group. The WSFC resource group
maintains the failover policy which defines the failure conditions that trigger restarts and failovers.
SQL Server setup uses sp_server_diagnostics to collect the health status of the SQL Server components.
The diagnostic information that is collected by sp_server_diagnostics includes System, Resource, Query
process, io_subsystem and Events. The first three components’ information is used for failover
detection, while the last two components’ information is used for diagnostic purpose only.
Failure conditions are set on an increasing scale. For levels 1-5, each level includes all the conditions
from the previous levels in addition to its own conditions. This means that with each level, there is an
increased probability of a failover or restart. 3 is the default setting for AVG and FCI. We use either
Cluster Management tools or T-SQL scripts to set the failover condition for FCI and AVG. For more
information about the SQL server flexible failover polices and diagnostics, refer
http://msdn.microsoft.com/en-us/library/ff878664.aspx
Dell conducted a variety of tests to determine how effectively WSFC and SQL Server work together to
identify the failover condition and take appropriate action. We observed that an AVG took a maximum