HP high availability solutions for Microsoft SQL Server Fast Track Data Warehouse using SQL Server 2012 failover clustering

(1)

Technical white paper

HP high availability solutions for Microsoft SQL Server Fast Track Data Warehouse using SQL Server 2012 failover clustering

Executive summary 2

Fast Track reference architectures 2 Important points and caveats 2 Current HP SAN based reference

architectures 2

Solution overview 3

Prerequisites 4

Additional storage needs 4

Storage configuration 6

Manual mapping 7

Mount points and dependencies 8

TempDB 10

SQL Fast Track database creation and

load 10

Cluster validation 11

Fast Track HA checklist 11

Bills of materials 13

DL380p Gen8 (SQL 2008 R2 and SQL

2012) 13

DL385 G7 (SQL 2008 R2) 14

DL580 G7 (SQL 2008 R2) 15

DL585 G7 (SQL 2008 R2) 16

DL980 G7 (SQL 2008 R2) 17

Implementing a proof-of-concept 18

For more information 19

(2)

Executive summary

Microsoft® SQL Server Fast Track Data Warehouse for Hewlett-Packard (HP) servers, storage and networking products provides a prescriptive approach for balancing server, storage, network and software configurations for architecting Microsoft SQL Server 2008 R2 and 2012 data warehouse solutions. The reference architectures provide server and storage guidance for various data warehouse workloads – giving you the most efficient hardware for your solution, saving you time and cost in choosing the right technology, and giving you peace of mind that the right platform and architecture is in place.

As with any mission critical application, SQL Server Fast Track downtime should be kept to an absolute minimum. In order to prevent costly downtime due to network outages, hardware failures or other losses, high availability measures must be put in place. This white paper describes high availability architectures for HP’s SAN based SQL Server Fast Track reference architectures. These architectures are based on both SQL Server 2008 R2 and SQL Server 2012. The SQL Server failover clustering solution presented here is applicable to both versions of the SQL Server application.

Target audience: The target audience for this document consists of IT planners, architects, DBAs, CIOs, CTOs, and business intelligence (BI) users with an interest in options for their BI applications and in the factors that affect those options.

This white paper describes testing performed by HP in June 2012.

Fast Track reference architectures

This document describes a high availability solution companion document to the Microsoft SQL Server Technical Articles,

“Fast Track Data Warehouse 3.0 Reference Guide”, and “Fast Track Data Warehouse Reference Guide for SQL Server 2012” which describe repeatable architectural approaches for implementing a scalable model for a symmetric multiprocessor (SMP)-based MicrosoftSQL Server data warehouse. The end result of the process described in this companion guide represents a fully configured Shared Cluster based High Availability solution for all HP SAN based Fast Track architectures. These architectures described below are inclusive of all the software and hardware, required to achieve and maintain a baseline level of “out of box” scalable performance when deploying SQL Server 2008 R2 and SQL 2012 data warehousing (SSDW) sequential data access workload scenarios versus traditional random I/O methods.

Important points and caveats

 The architecture described here and the approach detailed in the reference architecture guide is exclusively designed for, and is only applicable to, sequential data workloads. Use of this approach on other workload types is not appropriate and may yield configurations that are inefficient.

 ALL recommendations and best practices defined in the reference architecture guide must be implemented in their entirety in order to preserve and maintain the sequential order of the data and sequential I/O against the data.

Current HP SAN based reference architectures

HP currently supports both Fast Track 3.0 and Fast Track 4.0. These architectures leverage the HP P2000 G3 MSA FC Dual Controller SFF array, which allows for dual reads when drives are mirrored. For sequential data reads from data warehouse queries, this capability enables tremendous throughput per storage volume – up to 450 MB/s. The Fast Track approach, and supporting storage array architecture, is optimized for sequential reads. To support a non-optimized, random I/O data warehousing workload, up to 2 to 3 times the number of drives would be required to achieve the same throughput. The current SAN based Fast Track architectures support scan rates from 2.2 to 19.2GB/sec. Each

architecture shown below details the average (lower figure) and peak (higher figure) observed SAN rates during our testing.

Current SAN based Fast Track 3.0 and 4.0 SAN based architectures are listed below in tables 1 and 2. For Fast Track 3.0

(3)

Table 1. Fast Track 3.0 SAN based reference architectures with HP ProLiant servers and storage

Server CPU Total

Cores SAN Drive Count

(user data) Scan Rate Recommended Max Capacity DL380p Gen8 (2) Intel® Xeon®

Model E5-2690 16 (4) HP P2000 G3 MSA (64) 600GB

6G 10k SAS 4-6GB/sec 30TB

DL385 G7 (2) AMD Opteron

Model 6176SE 24 (3) HP P2000 G3 MSA (48) 600GB

6G 10k SAS 2.2-3GB/sec 20TB

DL580 G7 (4) Intel Xeon

6G 10k SAS 8-11.7GB/sec 60TB

DL585 G7 (4) AMD Opteron

Model 6176SE 48 (6) HP P2000 G3 MSA (96) 600GB

6G 10k SAS 4.4-8GB/sec 40TB

DL980 G7 (8) Intel Xeon

6G 10k SAS 12.3-19.2GB/sec 95TB

The DL380p Gen8 based architecture represents the first Fast Track 4.0 architecture as detailed in table 2 below.

Table 2. Fast Track 4.0 SAN based reference architectures with HP ProLiant servers and storage

Server CPU Total

Cores SAN Drive Count

(user data) Scan Rate Recommended Max Capacity DL380p Gen8 (2) Intel Xeon

6G 10k SAS 4-6GB/sec 30TB

HP solutions for Microsoft SQL Server Fast Track can be found at: hp.com/solutions/microsoft/fasttrack

Solution overview

This high availability solution utilizes SQL Server 2008 R2 and SQL Server 2012 failover clustering. Failover clustering provides local high availability through redundancy at the server level. SQL failover clustering leverages the failover clustering capability in Windows® Server 2008 R2. On the network a failover cluster appears the same as any other single server instance. The failover clustering capability provides transparency to all users of the system, and minimizes the downtime experienced during an outage or failover.

The focus of this white paper is to provide guidance in deploying a 2 node failover cluster based high availability solution for all HP SAN connected Fast Track reference architectures as shown in tables 1 and 2 of this document. All examples in this document utilize the HP DL380p Gen8 reference architecture, though specific design elements of each architecture will be noted.

(4)

Prerequisites

In order to build a SQL Server failover cluster, a Windows Server 2008 R2 failover cluster must be built first. The SQL Server enterprise application is “cluster aware”, and when installed in this fashion ensures that the SQL Server

application will failover during a fault. In addition, the hardware used for the failover cluster MUST be identical to ensure that in the case of failover, there is no loss of performance or capability. Table 3 below lists some prerequisites for building a Fast Track failover cluster.

Table 3. Fast Track Shared Cluster Pre-requisites

Component Requirement Comment

Hardware 2 Identical server models

Both servers have same CPUs, memory and NICs

Minimum 2 available NIC ports on each

server 1 for management and 1 for cluster communication

Identical number of HBAs in each system No loss of performance in case of failover

Storage All HBAs cross connected to multiple SAN switches to guard against loss of switch or network

Additional Disk for Quorum in Shared

SAN storage Quorum disk needed for cluster operation

Additional Disk for root disk needed in

shared SAN storage SQL system files and database mount point files housed in root disk which is accessible by both nodes in the cluster

Additional disks in shared storage for

transaction logging SQL Fast Track uses “Simple” transaction logging. However, “Full”

transaction logging is required when using SQL clustering. Additional log bandwidth and storage are required.

Additional software components and settings maybe required by Windows and SQL clustering. The following link provides a detailed guide for building a Windows Server 2008 R2 cluster

http://technet.microsoft.com/en-us/library/cc754482

Once the Windows failover cluster is built, the following guide can be used to install the SQL Server application in a clustered format

http://technet.microsoft.com/en-us/library/cc730647

Additional storage needs

Each Fast Track reference architecture requires additional storage to accommodate the quorum and cluster root disks.

In addition, additional storage is needed for the transition from Fast Track’s “Simple” logging methodology to the “Full”

logging methodology required by SQL clustering. All current SAN based Fast Track reference architectures have enough additional capacity for this without requiring additional storage arrays. Using our DL380p Gen8 configuration as an example, figure 1 below shows the storage configuration with the additional storage allocated.

(5)

Figure 1. Example DL380p Gen8 HA storage layout

The “Root” disk in this configuration is a simple 2 disk RAID1 configuration. A minimal amount of storage is needed for this disk as it will only store SQL Server system files (including error logs), and the mount point hierarchy. An additional 2 disk RAID1 LUN is used for the Windows cluster quorum disk and does not house any files.

For SQL transaction logging, an additional 4 disk RAID10 LUN (Log3 in figure 1) was added for increased storage and bandwidth.

Table 4 below details the HA storage requirements for all SAN based Fast Track architectures.

Table 4. Fast Track HA disk requirements

Architecture Disk Requirements Total Disks Needed

DL380p Gen8 Data: 64, Log:12, Stage:6, Quorum:2, Root:2, Spare:8 94

DL385 G7 Data: 48, Log:8, Stage:6, Quorum:2, Root:2, Spare:6 72

Note: Storage requirements are identical for both SQL Server 2008 R2 and SQL Server 2012. Reference architectures that are certified for both SQL Server versions, such as the DL380p Gen8, use the same disk configurations when build an HA architecture.

(6)

These storage changes do not change the capacity or performance ratings of any of the SQL Server Fast Track systems.

Storage configuration

Each Fast Track HA architecture uses a minimum of 2 SAN switches for redundancy. These 2 switches are cross

connected between the HBAs in both servers and the storage array storage ports. The following table shows a balanced, redundant SAN cabling scheme for a 2 switch configuration:

Table 5. Fast Track HA Switch Connection Requirements

HBA Ports Server #1 All #1 HBA ports connected to SAN Switch #1 All #2 HBA ports connected to SAN Switch #2

HBA Ports Server #2 All #1 HBA ports connected to SAN Switch #2 All #2 HBA ports connected to SAN Switch #1

P2000 Storage Arrays All #1 ports (A1, B1) connected to SAN Switch #1 All #2 ports (A2, B2) connected to SAN Switch #2

Figure 2 below shows an example of the cable wiring for the DL380p Gen8 architecture.

Figure 2. DL380p Gen8 Fast Track HA Example cabling

(7)

Manual mapping

All Fast Track architectures require a Microsoft Multipath I/O (MPIO) manual mapping scheme. This manual mapping scheme uses the “Fail over Only” MPIO policy for SAN connected storage. Each LUN has a primary and one or more secondary maps. Since we are using redundant SAN switches for the Fast Track HA configurations, primary LUN mappings should be spread out over the available SAN switches. Careful attention should be paid to ensure that LUNs are mapped using controller ports and HBA ports that are physically connected to the same switch. Using the cabling scheme outlined in table 5 of this document, the following example could be used to map LUNs.

Table 6. DL380p Gen8 Example HA manual cross-mapping scheme

LUN Primary Map Secondary Map Comment

Array #1

Data LUN #1 Port A1, HBA 1 Port #1 Port B2, HBA 1 Port#2 In Case of Switch Loss, ½ of LUNs failover to secondary map

Data LUN #2 Port A2, HBA 1 Port #2 Port B1, HBA 1 Port#1

Data LUN #3 Port B1, HBA 2 Port #1 Port A2, HBA 2 Port #2

Log LUN #1 Port A1, HBA 1 Port #1 Port B2, HBA 1 Port#2 Each HBA will support 1 Log or Stage LUN

Cluster Root Disk Port A2, HBA 1 Port #2 Port B1, HBA 1 Port#1 Root disk holds only SQL and mount point files.

Array #2

Cluster Quorum Disk Port A2, HBA 2 Port #2 Port B1, HBA 2 Port#1 Quorum disk does not house any files; Cluster use only

Array #3

(8)

LUN Primary Map Secondary Map Comment Array #4

Stage LUN #1 Port A1, HBA 4 Port #1 Port B2, HBA 4 Port#2 Each HBA will support 1 Log or Stage LUN

Using this scheme, each HBA has 4 primary mapped Data LUNs, and 1 Log or Stage LUN. In addition, each Storage port on each array is only mapped to 1 Data LUN.

Mount points and dependencies

In a typical Fast Track architecture, the LUNs are mounted in a mount point hierarchy located on the local C: drive. When using a shared storage failover cluster, the mount point directory MUST be located on a shared drive accessible by both servers. In our HA architecture, the mount points are created in the root (R: for simplicity) drive. This drive is a RAID1 set that we created and mapped on the second P2000 array. Once the mount point structure is created on the R: drive, the LUNs can be mounted into them on one of the cluster servers. The SQL Server shared files and error logs also reside on this drive.

Since all of the LUNs are dependent on the R: drive, in the cluster we have to create a dependency on the R: drive for all the other LUNs to ensure that the R: drive is online before the other drives come online so that mounting errors do not occur.

(9)

The SQL installation program does not create the dependencies automatically; we must manually create them for each LUN using the Failover Cluster Manager program. In the cluster manager program, we can highlight “Storage”, this will show us a list of the storage in the SQL Server resource group. If we highlight a drive, we can right-click, then select properties. In the property sheet, we go to the Dependencies tab, and click insert as shown below in figure 3.

Figure 3. Add Cluster Dependency

In our case, the Root disk is “Cluster Disk 13”, so we must add a dependency for this disk. This must be repeated for each Data, Log, and Stage LUN in our Fast Track configuration.

(10)

To verify that the dependencies are in place, the Windows Cluster Manager program contains a provision to run a Dependency Report. This report can be accessed using the “Actions” menu when selecting the SQL Server application in cluster manager. Figure 4 below depicts an example of the dependency report.

Figure 4. Cluster Dependency Report

“Cluster Disk 21” in this example, is the “Root” disk, and all other Fast Track disks have a dependency on this disk.

TempDB

The Fast Track architecture places TempDB on the same LUNs as the primary database files. Since the database disks are mounted into the Root disk in this Fast Track HA architecture, the TempDB files must also be placed on the same shared drives. Once the TempDB files are configured and allocated on one of the cluster servers, they will automatically be used when the cluster fails over to the second cluster node.

SQL Fast Track database creation and load

(11)

Cluster validation

Once the failover cluster is fully built, one of the final steps is to test the SQL Server application. SQL Server can be tested using the Cluster Manager program. After selecting the SQL Server application in the “Services and applications”

section of the program, the “Actions” menu in the lower right corner offers an option called “Simulate Failover of this resource”. This will simulate a failover of the SQL Server application from the current node to the second cluster node.

To ensure a smooth failover, queries can be run against the Fast Track databases while the failover is simulated. If the cluster is setup correctly, users should see a short pause of query processing during the failover without loss or corruption as the SQL Server application resumes on the secondary cluster node. Figure 5 below depicts the failover simulation option.

Figure 5. Cluster Failover Simulation

Fast Track HA checklist

The following table provides a checklist for building and configuring a Fast Track HA architecture for all SAN based HP Fast Track solutions.

Table 7. Fast Track HA Deployment Checklist

Action Comment

Build Cluster servers with identical hardware

Ensure both servers have same firmware levels

Install Windows Server on each server Ensure same OS and service pack levels on both servers.

Install MPIO feature on both servers.

(12)

Action Comment

Build shared storage arrays Create all Vdisks and volumes on each array

Connect server HBAs and storage ports to redundant SAN switches

Establish and connect server management and cluster Ethernet connections to switch

Create LUNs on P2000 arrays for Data, Log, Stage,

Root and Quorum Root and Quorum and be 2 Disk RAID1 arrays

Cross-map all LUNs to HBAs on both servers Use manual mapping scheme; Ensure its balanced

Mount and format Root (R:) and Quorum (Q:) drives on first cluster server

Create Fast Track mount point directories on R: disk

Mount and format remaining Data, Log and Stage LUNs into mount points on R: disk

Install the Windows Failover Cluster feature on both servers

Create a new Windows Cluster Refer to Windows clustering guide at:

http://technet.microsoft.com/en-us/library/cc754482 Specify the R: drive as the cluster root drive

Specify the Q: disk as the cluster quorum disk

Go to second cluster server and Add node to existing Windows cluster

Test basic cluster failover functionality

Install SQL Server on first cluster node Create new SQL failover cluster

Refer to: http://technet.microsoft.com/en-us/library/cc754482 Ensure all Fast Track disks are added to the application storage pool

Dependencies to R: disk should be created

Install SQL Server on second cluster node When running setup, select “Add node to existing cluster”

Modify TempDB to span all data disks

Test SQL Server failover capability

Connect to SQL cluster and build databases Failover cluster setup complete

(13)

Bills of materials

Bills of materials for each current HP SAN based Fast Track configuration are included in table 8.

Table 8. Fast Track HA Bills of materials

DL380p Gen8 (SQL 2008 R2 and SQL 2012)

Qty Part Number Description Server Configuration

2 662257-001 HP ProLiant DL380p Gen8 E5-2690 Hi Perf Server

16 652605-B21 HP 146GB 6G SAS 15K 2.5in SC ENT HDD

8 AJ763A HP 82E 8Gb Dual Port PCI-E FC HBA

40 647899-B21 8 GB Single Rank PC3-12800R Dimms

Storage Configuration

4 AP846A HP P2000 G3 MSA FC DC SFF Modular Smart Array

94 581286-B21 HP 600GB 6G SAS 10K SFF DP ENT HDD

Switch Configuration (Brocade)*

2 AM868A HP 8/24 24-Ports / 16-Active Enabled SAN Switch

32 AJ835A 2m LC-LC Cable

1 263474-B22 HP Cat 5e Cables – 8 Pack 6ft/2m Cable

1 263474-B23 HP Cat5e Cables – 8 Pack 12ft/3.7m Cable

32 AJ716A HP 8Gb Shortwave B-series FC SFP+ 1 Pack

1 J9279A HP 2510G-24 Switch

Switch Configuration (QLogic)*

2 AW575A HP SN6000 Stackable 8Gb 24 Port/20 Active Fiber Switch

1 263474-B22 HP Cat 5e Cables – 8 Pack 6ft/2m Cable

1 263474-B21 HP Cat5e Cables – 4 Pack 3ft/1m Cable

(14)

DL385 G7 (SQL 2008 R2)

Qty Part Number Description Server Configuration 2 573122-B21 HP DL385 G7 SFF CTO Chassis

2 585322-L21 AMD Opteron Processor Model 6176SE (2.3 GHz, 12MB Level 3 Cache, 105W)

2 585322-B21 AMD Opteron Processor Model 6176SE (2.3 GHz, 12MB Level 3 Cache, 105W)

24 593913-B21 HP 8GB (1x8GB) Dual Rank x4 PC3-10600R (DDR3-1333)

4 512327-B21 HP 750W CS HE Gold Power Supply 92% Efficiency

2 581783-B21 HP DL385 G7 PCI-E Riser Kit

2 534562-B21 1G FBWC (P410i)

6 AJ763A HP 82E PCIe FC HBA Dual Port (8Gb)

72

581286-B21

507127-B21

HP 600GB 6G SAS 10K SFF DP ENT HDD Or

HP 300GB 6G SAS 10K SFF DP ENT HDD

24 221692-B21 2m LC-LC Cable

1 263474-B22 HP Cat 5e Cables – 8 Pack

1 J9279A HP 2510-24G Switch

2 AW575A HP SN6000 Stackable 8Gb 24 Port/20 Active Fibre Switch

24 221692-B21 2m LC-LC Cable

(15)

DL580 G7 (SQL 2008 R2)

2 643086-B21 HP ProLiant DL580 G7 CTO Chassis

2 643067-L21 Intel Xeon E7-4870 Kit

4 643067-B21 Intel Xeon E7-4870 Kit

8 500172-B21 HP 1200W 12V AC Power Supply

2 534562-B21 1G FBWC Module

2 588137-B21 DL580G7 PCI-E Expansion Kit

8 644172-B21 DL580G7 E7 Memory Board

128 500658-B21 4 GB Dual Rank x4 PC3-10600R Dimms

2 T5519A HP 8/40 SAN Switch 8-Port Upgrade Activation License

2 263474-B22 HP Cat5e Cables – 8 Pack, 6ft/2m Cable

1 263474-B23 HP Cat5e Cables – 8 Pack, 12ft/3.7m Cable

1 J9279A HP Switch 2510G-24

(16)

Qty Part Number Description

4 AW576A HP SN6000 Stackable 8Gb 24 Port/20 Active Fibre Switch

2 263474-B22 HP Cat 5e Cables – 8 Pack, 6ft/2m Cable

1 263474-B23 HP Cat5e Cables – 8 Pack, 12ft/3.7m Cable

1 J9279A HP Switch 2510G-24

DL585 G7 (SQL 2008 R2)

2 601361-001 HP ProLiant DL585 G7 6176 Server

2 590485-B21 DL585 G7 PCI-E Option Kit

64 593911-B21 4 GB REG PC3-10600 4 GB Kit

138

581286-B21

507127-B21

HP 600GB 6G SAS 10K SFF DP ENT HDD Or

HP 300GB 6G SAS 10K SFF DP ENT HDD

44 221692-B21 2m LC-LC Cable

(17)

2 AW576A HP SN6000 Stackable 8GB 24 Port/20 Active Fiber Switch

2 BK780A HP SN6000 Stackable 8GB 12 port/8 Active Switch

44 221692-B21 2m LC-LC Cable

DL980 G7 (SQL 2008 R2)

2 AM451A HP ProLiant DL980 G7 CTO Chassis

2 650770-L21 Intel Xeon E7-4870 4P Kit

2 650770-B21 Intel Xeon E7-4870 4P Kit

2 AM450A DL980 CPU Assembly

8 AM470A HP 1200W 12V HE AC Power Supply

2 534562-B21 1G FBWC Module

2 588137-B21 DL980 G7 PCI-E Expansion Kit

2 AM434A DL980 G7 LP PCIe Expansion Kit

16 A0R60A DL980 G7 Memory Board

192 A0R56A 8 GB Dual Rank x4 PC3-10600R Dimm

(18)

2 AM868A HP 8/24 24-Ports/ 16-Active Enabled SAN Switch

4 T5518A HP 8/40 SAN Switch 8-Port Upgrade Activation License

108 221692-B21 2m LC-LC Cable

3 263474-B22 HP Cat 5e Cables – 6ft 8 Pack

108 AJ716A HP 8Gb Shortwave FC SFP+ 1 Pack

4 AW576A HP SN6000 Stackable 8Gb 24 Port/20 Active Fiber Switch

4 BK780A HP SN6000 Stackable 8Gb 12 Port/8 Active Fiber Switch

108 221692-B21 2m LC-LC Cable

108 AJ718A HP 8Gb Shortwave FC SFP+ 1 Pack

*Note: Only 1 switch configuration is required; please select either Brocade or QLogic.

Implementing a proof-of-concept

As a matter of best practice for all deployments, HP recommends implementing a proof-of-concept using a test environment that matches as closely as possible the planned production environment. In this way, appropriate

performance and scalability characterizations can be obtained. For help with a proof-of-concept, contact an HP Services representative (hp.com/large/contact/enterprise/index.html) or your HP partner.

(19)

For more information

Fast Track Data Warehouse 3.0 Reference Guide, http://technet.microsoft.com/en-us/library/gg605238.aspx Fast Track Data Warehouse Reference Guide for SQL Server 2012, http://msdn.microsoft.com/en-

us/library/hh918452.aspx

HP ActiveAnswers, hp.com/solutions/activeanswers

SQL Server solutions on ActiveAnswers, hp.com/solutions/activeanswers/microsoft/sql HP Business Intelligence Solutions for Microsoft SQL Server, hp.com/solutions/microsoft/sqlbi

HP solutions for Microsoft SQL Server Fast Track Data Warehouse, hp.com/solutions/microsoft/fasttrack HP Converged Applications SQL: Business Insight and Information Management, hp.com/solutions/microsoft/sql HP and Microsoft, hp.com/go/microsoft

HP ProLiant DL380p Gen8 server, hp.com/servers/dl380 HP P2000 storage, hp.com/go/p2000

HP Networking, hp.com/go/networking How to buy, hp.com/buy

To help us improve our documents, please provide feedback at hp.com/solutions/feedback.

Get connected

hp.com/go/getconnected

Current HP driver, support, and security alerts delivered directly to your desktop

© Copyright 2012 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein.

Microsoft and Windows are U.S. registered trademarks of Microsoft Corporation. AMD is a trademark of Advanced Micro Devices, Inc. Intel and Xeon

HP high availability solutions for Microsoft SQL Server Fast Track Data Warehouse using SQL Server 2012 failover clustering