Dell PowerEdge EL Dell High Availability and Disaster Recovery Solutions Using - Page 15

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn Availability Groups 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 15