Dell PowerEdge SP 575-2 Leitfaden

Gopu Narasimha Reddy

Dell Database Solutions Engineering

Dell High Availability and Disaster

Recovery Solutions Using Microsoft

SQL Server 2012 AlwaysOn Availability

Groups

Dell servers and storage options available for AlwaysOn Availability

Groups deployment.

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

This document is for informational purposes only and may contain typographical errors and

technical inaccuracies. The content is provided as is, without express or implied warranties of any

kind.

in typography or photography. Dell, the Dell logo, PowerVault, and PowerEdge are trademarks of Dell

Inc. Intel is a registered trademark of Intel Corporation in the U.S. and other countries. Microsoft,

Windows, Windows Server, and SQL Server are either trademarks or registered trademarks of Microsoft

Corporation in the United States and/or other countries. Other trademarks and trade names may be

used in this document to refer to either the entities claiming the marks and names or their products.

Dell disclaims proprietary interest in the marks and names of others.

August 2012| Rev 1.0

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

Contents

Executive summary ....................................................................................................... 4

Introduction ................................................................................................................ 4

Objectives................................................................................................................ 5

Why Dell server and storage hardware for AlwaysOn setup?.................................................... 5

Processors ............................................................................................................. 5

Memory ................................................................................................................ 5

Internal storage (HDD and SSD) ................................................................................... 5

Introduction to Microsoft SQL Server 2012 AlwaysOn Availability Groups (AVG)............................... 6

AlwaysOn Availability Groups Architecture ........................................................................ 6

Most common HA and DR deployments for SQL databases ...................................................... 7

Dell reference configuration for AVG ............................................................................... 8

AVG deployments on non-shared storage .......................................................................... 8

Major advantages and limitations of implementing AVG on non-shared storage ......................... 9

AVG deployments on shared storage .............................................................................. 10

Advantages and limitations of implementing AVG on shared storage .................................... 14

AVG deployments on hybrid (HDD and SSD) storage............................................................ 14

Benefits.............................................................................................................. 14

Advantages and limitations of implementing AVG on hybrid (SSDs and HDDs) storage ............... 15

Flexible failover policies and enhanced diagnostics for detecting failover conditions .................. 15

Summary .................................................................................................................. 16

Figures

Figure 1. Availability Groups on WSFC ............................................................................. 7

Figure 2. Simple AVG deployment on PE R720XD local storage ................................................ 9

Figure 3. Scenario 1 : SQL Server FCI with AVG on shared storage ......................................... 11

Figure 4. Scenario 2 : SQL Server FCI with AVG on shared storage ......................................... 12

Figure 5. Creating AVG with FCI................................................................................... 13

Figure 6. FCI with AVG deployment setting ..................................................................... 13

Figure 7. AVG deployments on SSD and HDD .................................................................... 14

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

Executive summary

Organizations typically require that their critical applications are available to end users on a

continuous basis, and thus the need for reliable solutions that provide High Availability (HA) and

Disaster Recovery (DR) continues to increase. Most HA and DR solutions available today use multiple

redundant components to extend these vital capabilities to hosted data. Several factors are important

when choosing the best and most appropriate HA and DR solutions for a given infrastructure, including

reliability, robustness, cost, efficiency, automation, ease-of-use, and related performance impacts to

the hosted application.

In this technical paper, we will discuss an important HA and DR feature, Microsoft SQL Server 2012

AlwaysOn Availability Groups (AVG), and how it addresses the issues mentioned above when deployed

on Dell™ 12th Generation PowerEdge™Servers. The guidelines and recommendations for implementing

AVG will be illustrated by a review of some of the possible user scenarios. We will also discuss how

PowerEdge Servers can be combined in a variety of server and storage configurations that make Dell

the right platform for deploying Microsoft AlwaysOn HA and DR solutions.

Introduction

Achieving the best possible level of availability and recovery of critical applications is a big challenge.

Many problems—software failures, hardware failures, planned and unplanned downtimes to name just a

few—can make the critical data unavailable.

High Availability features provide continuous access to the applications/infrastructure, and are

generally achieved by having redundant hardware and software combination located within the same

room or a datacenter. Disaster Recovery, on the other hand, is a recovery plan which will be used when

the primary setup is not accessible. Usually a DR setup is located at the remote site rather than the

same site of primary setup.

Recovery Time Objective (RTO) and Recovery Point Objective (RPO) are the important metrics that

help customers in choosing the best suitable HA and DR solution for a given application. RTO refers to

how long a customer can afford to go without a given application being available for use. The

appropriate HA solution has to be implemented so that the application recovers from failure within an

acceptable time period.

RPO refers to the amount of data that a customer determines it can afford to lose in the case of a

disaster/outage. This dictates the kind of recovery solution that needs to be in place for a given

application. Achieving the required RPO and RTO goals involves ensuring continuous uptime of critical

applications and protection of critical data from unplanned and planned downtime.

Choosing the right hardware components such as servers, storage, and switches for implementing a HA

and DR solution can significantly benefit the customers in terms cost, performance, and availability of

the application. The servers and different storage options chosen for implementing the AlwaysOn AVG

have a great impact on performance and failback/failover times. In the following sections, we will

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

discuss various server and storage options available from Del that are most conducive to optimal AVG

deployments.

Objectives

Understanding how AlwaysOn Availability Groups may be the right choice for implementing

High Availability and Disaster Recovery solutions for business critical SQL databases based on

customer RTO and RPO requirements.

Exploring the various hardware combinations possible using the latest Dell PowerEdge servers

and available internal/external database storage options for HA and DR implementations.

Defining useful HA and DR implementation scenarios with AlwaysOn Availability Groups.

Discussing guidelines and recommendations for implementing AlwaysOn Availability Groups.

Why Dell server and storage hardware for AlwaysOn setup?

Dell recently launched its 12th Generation PowerEdge servers which bring to the market many new

features and enhancements that benefit the AlwaysOn Availability Group deployments. These features

and enhancements are detailed below.

Processors

By supporting Intel® E5-series processors, Dell’s 12th Generation PowerEdge servers provide the heavy-

duty computing power required by high performing AVG databases. Together with High Quick Path

Interconnect (QPI) links, 20MB L3 cache, and support for many 3rd Gen PCIe slots, this makes the Dell

12th generation PowerEdge servers the right platform for hosting high performing AVG databases.

Memory

AVG deployments benefit from the high speed memory DIMMs. Dell’s 12th Generation PowerEdge servers

support 1333 and 1600 MHz high speed memory DIMMS with ECC. Single, Dual and Quad rank DIMMs are

supported. With the high speed memory DIMMS, Dell’s solutions can achieve noticeable improvement in

the overall database throughput.

Internal storage (HDD and SSD)

Dell’s 12th Generation PowerEdge rack servers have a flexible storage chassis that supports 2.5- and

3.5-inch SAS HDDs and PCIe-based SSD drives with various capacities and speeds. This flexibility allows

customers to choose drive type and number of drives based on their application requirements.

For example, the PowerEdge R720xd can handle the storage-intensive databases needs of the entry-

level customer without demanding external storage. It supports 24 front-accessible 2.5-inch SAS drives,

and provides a single server and storage platform for deploying AlwaysOn AVG databases.

The Dell PowerEdge R720/R820 supports up to 4 front loading 2.5-inch PCIe SSD drives. This innovative

server architecture allows the deployment of high performing AVG databases on PCIe SSD drives of the

server, and achieves better throughputs and failover/failback times at lower latencies.

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

The PowerEdge R720 supports up to 7 PCIe slots and offers an interface for several available storage

categories such as fiber channel, iSCSI and Direct Attached Storage (DAS), making possible the

installation of applications that require high-bandwidth on the same system.

By combining the processing power of the multi-core Intel Sandy Bridge processors, high performing

internal storage, large levels of RAM and enough PCIe Cards, the PowerEdge 12th Generation rack

servers provide an excellent platform for hosting AVG database that require high processing power,

memory and storage.

Introduction to Microsoft SQL Server 2012 AlwaysOn Availability

Groups (AVG)

Microsoft® SQL Server 2012 introduces the AVG feature and a number of other high-availability

enhancements to provide more robust and reliable HA and DR solutions to SQL databases. AVG is the

one of the important features included in Microsoft SQL Server 2012, allowing customers to implement

HA and DR solutions for their business critical databases. This feature is available only with SQL Server

2012 Enterprise Edition; Standard Edition supports only SQL Failover Cluster Instances (FCI) for high

availability.

AlwaysOn Availability Groups Architecture

SQL Server 2012 AVG provides flexible design choices for selecting an appropriate high availability and

disaster recovery solution for specific database applications. AVG is a set of databases that can failover

together to an available secondary server. It supports a set of primary databases running on a primary

server, and one to four sets of corresponding secondary databases running on secondary servers.

The following are the important elements that are used in AVG implementation:

Availability Databases: Group of databases that are configured as an availability group and

that can failover together from a primary replica to any other secondary replica.

Availability Replicas:

oPrimary Availability Replica: An SQL instance that hosts the availability group for

read-write access. For a given Availability Group, only one primary replica is allowed.

oSecondary Availability Replica(s): An SQL instance that hosts the secondary sets of

databases for the primary set of databases. For a given availability group, up to four

secondary availability replicas are allowed. Secondary sets of databases are available

for either read-only access or no access at all.

Availability Mode: Two availability modes are supported.

oSynchronous-Commit Mode: In this mode, the primary replica commits the transaction

only when it receives an acknowledgement of writing (hardening) the transaction to T-

Log of the secondary replicas configured for Synchronous-Commit Mode. This mode

makes sure that primary and Synchronous-Commit secondary replicas are synchronized

with each other. Since the primary replica has to wait for arrival of acknowledgement

from the secondary replicas, this mode comes at the cost of increased transaction

latency. Up to two Synchronous-Commit mode replicas (one with automatic failover

and other with manual failover) are supported.

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

oAsynchronous-Commit Mode: The primary replica commits the transaction without

waiting for an acknowledgement from the secondary replicas that are configured for

Asynchronous-Commit mode. This mode minimizes the transaction latency, but might

cause Asynchronous-Commit mode replicas to lag behind the primary replicas. This

may, in turn, lead to some data loss in case of failover. Up to four Asynchronous-

Commit Mode replicas (with manual failover) are supported.

Windows Server Failover Cluster (WSFC): All availability replicas (primary and secondary)

participating in the AVG deployment must be installed on the different nodes of the same WFSC

cluster. However, it is not required that all the cluster nodes be part of AVG. Support for

Multi-Subnet clusters is introduced in SQL server 2012, and allows the SQL databases to span

long distances across datacenters.

Figure 1 shows an implementation of availability group that contains the maximum possible number of

availability replicas, i.e. one primary replica and four secondary replicas.

Availability Groups on WSFCFigure 1.

At any point in time, AVG can have only one primary replica serving read-write requests. It is possible

to configure up to four secondary replicas exclusively for read-only access.

Most common HA and DR deployments for SQL databases

Previous versions of SQL Server required that the customer combine one or more features together to

create a robust HA and DR solution for their critical SQL server databases. The most typical solutions

implemented with previous versions of SQL Server are:

Non-shared Storage Implementations: Implementing database mirroring (with witness and

automatic failover) for High Availability, and combining it with log shipping for Disaster

Recovery. This method uses local storage (non-shared) in the primary and DR sites. This setup

can easily be replaced by AlwaysOn Availability Groups and provides both HA and DR. This

setup is a right choice for SMB customers who cannot invest more in SAN implementations.

Shared Storage Implementations: Implementing FCI for High Availability and combining it with

Database Mirroring (Asynchronous) or Log Shipping for Disaster recovery. This method uses

shared storage in the primary site for HA and either shared or non-shared storage in the DR

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

site. This setup can be replaced by FCI for HA and AlwaysOn Availability Groups for DR. This

setup is a right choice for SMB and Enterprise customers who can invest to some extent in SAN

implementations.

Multi-site Implementations: Implementing Multi-site FCI across datacenters for both HA and

DR. Mostly this setup uses shared storage in both the sites. This method can be replaced by the

Multi-site FCI and Availability Groups for HA and DR. This setup is a right choice for large

Enterprise customers who have built a robust SAN implementation across multiple data centers.

For more information on the design patterns of AVG, refer to “AlwaysOn Availability Design Patterns”.

Legacy HA and DR features have their own pros and cons in terms of setup complexity, transaction

latencies, maximum number of secondary instances possible, readable secondary instances, automatic

failover/failback times, and other maintenance activities. SQL Server 2012 AVG combines the best of

legacy HA and DR features as well as providing a single feature with many enhancements in many areas

as pointed out above. Some of the benefits that AVG can offer are:

HA and DR solutions with AlwaysOn Availability Groups (with non-shared storage)

HA and DR solutions with FCI (with shared-storage) and multi-site cluster

Multiple secondary replicas and hence reduced overhead on primary replicas

Automatic routing of read-only connections to secondary replicas

Offloading backups to the secondary replicas and prioritizing secondary replicas for backup

operations

Automatic page repair and robust failure detection and correction when compared to

previous versions of SQL Server

Improved flexible failover polices and diagnostics

Flexible, integrated, easy and automated implementation with Power shell

Dell reference configuration for AVG

In the following sections, we will discuss the different AVG implementation scenarios that are possible

using Dell PowerEdge servers, incorporating internal/external storage options. We will explore the

AlwaysOn AVG deployments both on non-shared and shared storage configurations. The concepts of

AVG multi-site deployments are similar apart from the multi-site WSFC cluster setup. A detailed

description of the multi-site configuration is outside the scope of this paper.

AVG deployments on non-shared storage

In addition to SAN storage deployments, SQL Server 2012 enables us to use non-shared storage to host

databases. We can deploy AVG on local storage, or on the server’s directly attached. This feature

benefits the customer who cannot invest much on SAN deployments.

As discussed in the above sections, with its high performing internal storage and huge processing

power, Dell’s 12th Generation PowerEdge R720xd server is an excellent single platform choice for

hosting AlwaysOn databases that have large processing power, memory, and high-performing storage

requirements. R720xd supports a maximum of 38TB of internal storage.

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

The Dell H710 mini-integrated PowerEdge RAID Controller (PERC) card can be used efficiently to

manage internal storage of the server. The H710P mini-card is designed for enhanced performance,

increased reliability, and simplified management. For more details on the H710 PERC card refer

dell.com/perc.

In our test configuration, we used fully populated PowerEdge R720xd servers in the primary site and DR

site. Two RAID-10 (10 disks for each) virtual disks are carved out from 20 disks to store the database

files, and one RAID 1 virtual disk from the two drives for storing T-Log files. The last two hard drives

might be used for Global Hot spares as shown in Figure 2.

Simple AVG deployment on PE R720XD local storageFigure 2.

Figure 2 shows the basic AlwaysOn Availability Group deployment with two replicas configured for

Synchronous-Commit mode with automatic failover to provide High Availability. Another replica was

configured with Asynchronous-Commit mode to provide DR capabilities to the AVG databases.

Customers can add two more replica nodes (one in the primary and one in the secondary site) for

further HA and DR capabilities.

The quorum model and NodeWeight settings of the configuration should be based on the customer’s

specific application requirements. For more information on deploying AVGs, including prerequisites,

restrictions, and recommendations for host computers, Windows Server Failover Clustering (WSFC)

clusters, server instances, and availability groups refer http://msdn.microsoft.com/en-

us/library/ff878487.aspx.

Major advantages and limitations of implementing AVG on non-shared storage

Advantages:

A simple and cost effective solution for HA and DR

Perfectly suits SMB customer requirements

Quicker failover and failback operations because storage is locally attached

Reduced datacenter footprint and less power consumption when compared to shared storage

solutions

Dell High Availability and Disaster Recovery Solutions Using Microsoft SQL Server 2012 AlwaysOn

Availability Groups

Limitations:

Limits on storage scalability.

No enhanced SAN capabilities.

AVG deployments on shared storage

In addition to the failover capability provided by AlwaysOn Availability Group, we may also set up a

secondary layer of availability at the server instance level. This is done by implementing SQL Server FCI

together with the underlying WSFC cluster. FCI is configured with shared storage to host the SQL server

instances.

FCIs are basically designed to provide High Availability at instance level whereas AlwaysOn Availability

Group (AVG) is designed to provide HA and DR capabilities at database level.

- Consider the following when you plan to combine the FCI with AVG.

AVG alone does not require shared storage, but when we use FCI to host one or more

availability replicas, each of the nodes which are part of FCIs will require shared storage as per

standard SQL Server failover instance installation.

An FCI may be used together with an availability group to enhance the availability of an

availability replica. However, to prevent potential race conditions in the WSFC cluster,

automatic failover of the availability group is not supported to or from an availability replica

that is hosted on a FCI.

Since SQL Server FCIs do not support automatic failover by availability groups, any availability

replica that is hosted by an FCI can only be configured for manual failover.

All the prerequisites and restrictions that need to be addressed before proceeding with AVG

deployment may be found at http://msdn.microsoft.com/en-

us/library/ff878487.aspx#FciArLimitations. Care should be taken to make sure all the specified

hotfixes are installed on the appropriate nodes.

Figure 3 shows the typical AVG deployment along with SQL Server FCI.

Andere Handbücher für PowerEdge SP 575-2

Dieses Handbuch passt für folgende Modelle

Inhaltsverzeichnis

Andere Dell Server Handbücher

Dell

Dell PowerEdge 650 Leitfaden

Dell

Dell PowerEdge 2650 Bedienungsanleitung

Dell

Dell EMC PowerEdge MX750c Handbuch

Dell

Dell EMC DSS 9630 Bedienungsanleitung

Dell

Dell PowerEdge C8000 Bedienungsanleitung

Dell

Dell PowerEdge T320 Kurzanleitung

Dell

Dell DR4000 Restore Manager Bedienungsanleitung

Dell

Dell PowerEdge R520 Kurzanleitung

Dell

Dell poweredge VRTX Bedienungsanleitung

Dell

Dell PowerEdge M420 Allgemeine Anweisungen

Dell

Dell EMC PowerEdge R750xs Handbuch

Dell

Dell PowerEdge XR8000r Handbuch

Dell

Dell PowerEdge C6145 Bedienungsanleitung

Dell

Dell External OEMR R910 Kurzanleitung

Dell

Dell PowerEdge R610 Bedienungsanleitung

Dell

Dell PowerEdge C6320 Bedienungsanleitung

Dell

Dell PowerEdge 6650 Bedienungsanleitung

Dell

Dell EMC E39S Series Handbuch

Dell

Dell EMC PowerEdge E45S Series Handbuch

Dell

Dell POWEREDGE 1900 Bedienungsanleitung

Dell

Dell PowerEdge 2500 Leitfaden

Dell

Dell EMC DSS 9000R Bedienungsanleitung

Dell

Dell PowerEdge 2410 Bedienungsanleitung

Dell

Dell 100 Series Handbuch

Beliebte Server Handbücher anderer Marken

HP AlphaServer ES47 Bedienungsanleitung

PowerNAS

PowerNAS CMA Bedienungsanleitung

Supero

Supero FatTwin F617R3-FT Bedienungsanleitung

EtroVISION

EtroVISION EV3151 Bedienungsanleitung

HP ProLiant Gen9 Bedienungsanleitung

Compaq

Compaq ProLiant 3000 Handbuch

iRobo

iRobo IPC2U Bedienungsanleitung

Nortel

Nortel 1000 Con?guration guide Bedienungsanleitung

Asus

Asus AP7500 Bedienungs- und Wartungshandbuch

Avid Technology

Avid Technology AirSpeed 5000 Bedienungsanleitung

HP Integrity rx2600 Installationsanleitung

Milestone

Milestone Husky IVO 350T Bedienungsanleitung

Planet

Planet NAS-7410 Bedienungsanleitung

Lenovo

Lenovo ThinkSystem SR635 V3 Bedienungsanleitung

Fujitsu

Fujitsu PRIMERGY BX900 Bedienungsanleitung

Asus

Asus RS700DA-E6 PS4 Bedienungsanleitung

Vertiv

Vertiv Avocent ACS6000 Montage- und Wartungsanleitung

A & T Internationale

A & T Internationale Xserve Service-Handbuch