1© copyright 2012 emc corporation. all rights reserved. emc vnx5700, emc fast cache, sql server...

1© Copyright 2012 EMC Corporation. All rights reserved.

EMC VNX5700, EMC FAST Cache, SQL Server AlwaysOn Availability Groups

Strategic Solutions Engineering (SSE)EMC Solutions Group (ESG)

David RingMichael Morris

EMC MULTISITE DISASTER RECOVERY FOR MICROSOFT SQL SERVER 2012


Agenda

EMC Proven Solutions

SQL Server 2012 Overview

Solution Overview

Architecture Design

Test Results

Summary

Q&A


EMC Proven Solutions Approach

Capture and

define

Test and validate Document Publish

Singapore Shanghai, China

Cork, Ireland

Hopkinton, MA

Santa Clara, CA

Vienna, Austria

1 2 3 4

Req

uire

men

ts

…we champion the customers…and test the solutions you would like


SQL Server 2012 Overview

High Availability


SQL Server 2012 New Feature Overview


SQL Server AlwaysOn

High Availabilit

y

Disaster Recovery

Built on Windows Failover

Clustering•Shared Storage•Non-shared Storage


SQL Server High Availability Features

HA Level Before SQL Server 2012 SQL Server 2012

Instance SQL Failover Cluster AlwaysOn Failover Cluster

Database

Backup and Restore Log Shipping Transactional Replication SQL Snapshot SQL Mirroring

+ AlwaysOn Availability Group


AlwaysOn Failover Cluster

Microsoft SQL Server AlwaysOn Failover Cluster

Data

base

Pro

tect

ion

Shared storage

Passive secondary replicas

Protects server, instance, and database

Multi-subnet support now available

huang, haiyan

Notes second bullet, each hosting databases that could be configured as readable. Bruce, please check the wording...


SQL Server 2012:Multi-subnet Support

Clu

ster

Pro

pert

ies—

Dep

en

den

cies

Cluster Setup support—intelligently detects a multi-subnet environment

SQL Server Engine support—skips binding to any IP address that is not in an online state


AlwaysOn Availability Groups

Microsoft SQL Server AlwaysOn Availability Group

Data

base

Pro

tect

ion

Synchronous or Asynchronous

Up to four replicas

Active secondary replicas

Protects database only

huang, haiyan

Notes second bullet, each hosting databases that could be configured as readable. Bruce, please check the wording...


Solution Overview


Solution Purpose

SQL Server 2012 AlwaysOn– Characterize a geographically dispersed SQL Server 2012

environment protected by AlwaysOn technology, and highlight multi-subnet support at both synchronous and asynchronous distances.

EMC VNX5700 – Optimize database storage performance with EMC FAST Cache.– Integrate a simple, efficient, and powerful platform for enterprise-

class SQL Server 2012 infrastructures.– Validate the ability of the VNX5700 storage array to support SQL

Server 2012 instances running OLTP-like workloads that generate over 50,000 IOPS.


Solution Architecture• 2 * SQL Servers

• 1 * Primary (Production)• 1 * Secondary (DR)

• FAST Cache on production

• 4 * DB’s: 1.8 TB of data• Dataset: 60% hot

FAST Cache impact: x4 (TPS/IOPS)

latency of <3ms


Architecture Design


SQL Server LayoutDescription Details / Measurement

SQL Server physical server (instances) One production SQL Server instance (primary replica)

One read-only secondary SQL Server instance (secondary replica)

SQL Server hardware configuration 32 core/256 GB memory(Processor: Intel Xeon X7560)

SQL Server user databases

• 4 x Databases

• 180,000 users

• 1.8 TB of data

1 x 50 GB (5,000 users)

1 x 250 GB (25,000 users)

1 x 500 GB (50,000 users)

1 x 1 TB (100,000 users)

Read/Write workload OLTP-like workload - 50,000 IOPS

Read/Write ratio of approximately 9:1

VNX storage pools One primary/secondary replica

Backup staging area for availability group seeding

2 TB


Storage Design

Purpose Pool/RAID group Disk configuration RAID type

SQL System databases RAID group4 x 3.5”

600 GB 10k SASRAID 5

SQL TempDBs RAID group4 x 2.5”

600 GB 15k SASRAID 1/0

OLTP database logs RAID group

8 x 2.5”

600 GB 10k SAS

RAID 1/0

OLTP database data files Storage Pool40 x 2.5”

600 GB 10k SASRAID 5

FAST Cache pool FAST Cache pool

14 x 3.5”

100 GB Flash drives

RAID 1

Production array storage configuration


Storage Design for SQL Server


Test Results


0

10,000

20,000

30,000

40,000

50,000

11,537

19,340

32,928

45,193

50,043 50,085 51,848 52,147

1,483 2,313 3,1155,426 5,580 5,478 6,509 6,087

Primary ReplicaSecondary Replica

Baseline 2hr FAST Cache warmup FAST Cache Steady State

Transfers/sec (IOPs)

...800km …4,000kmDistance ...80km

Throughput in IOPS (transfers/sec)


Comparison of Disk Reads/Writes On Primary And Secondary Replicas


Baseline Synchronous 80km

Asynchronous 800km

Asynchronous 4,000km

0

25

50

75

100

76.48% 76.07% 80.09% 80.17%

1.70% 1.69% 1.97% 1.64%

Primary ReplicaSecomdary Replica

SQL Server CPU Utilization

SQL Server CPU impact across synchronization state and distance



...800km ...4,000km

Distance ...80km

0

5,000

10,000

15,000

20,000

25,000

30,000

4,969

12,076

17,682

21,87024,007 23,959

25,517 25,317

324724 1,024 1,303 1,443 1,454 1,406 1,164

Primary Replica

Throughput in Transactions/Sec (TPS)

Disk transactions per sec (TPS) for both primary and secondary replicas


Physical Disk Utilization

Physical disk utilization for storage pool on production and DR arrays

0%

25%

50%

75%

100%90% 90% 89% 86%

57% 56% 59% 56%

18% 17%

27%

52%

71% 69% 72% 69%

Primary Replica Secondary Replica



Storage Processor Utilization

0%

25%

50%

75%

100%

14%

25%

41%

51%51%

15%

26%

43%

56%58%

5% 6%10% 10%

10%5% 6%13% 12%

13%

SPA - Pool 1: Production SPB - Pool 1: Production

SP utilization for SPA and SPB on production and DR storage arrays



Availability Group Creation Process

1• Create Availability Group

2• Add Availability Group Listener

3• Backup Database from Primary Replica

4• Backup Log from Primary Replica

5• Restore Database to Secondary Replica(s)

6• Restore Log to Secondary Replica(s)

7• Join Secondary Replica(s) to Availability Group

8• For Additional Databases Repeat from Step 3


Shared Storage Requirements For Seeding

A shared storage space is required when performing a Full Initial Data Synchronization as part of availability group creation.

Users should consider the storage used for these database backups and restorations.

The backup process has high bandwidth requirements for storage.


Availability Group Creation Times

Synchronous and asynchronous availability group creation at 80, 800, 4,000 km

Availability Group

Mode Distance Duration

Max Bandwidth (MB/sec)

Backup Restore

OLTP_AG1 Synchronous 80 km 4hrs 14m 101.718 242.344

OLTP_AG2 Synchronous 80 km 4hrs 16m 109.683 254.839

OLTP_AG1Asynchronous 800 km 4hrs 48m 101.63 179.683





Summary


Summary• AlwaysOn Availability Groups

provide protection at database level• Synchronous – commit mode• Asynchronous – commit mode

• Power of the EMC VNX to host mission critical SQL Server 2012 environments

• FAST Cache • significantly boost performance• Incredibly low SQL Server datafile

latency <3 ms

FAST Cache increased SQL Server throughput by x4 (TPS/IOPS) while maintaining a very low

latency of <3ms


Q&A?


THANK YOU

1© copyright 2012 emc corporation. all rights reserved. emc vnx5700, emc fast cache, sql server...

Documents

emc corporation

emc designs

microsoft sql server

caps emc vnx5700

test solutions

overview high availability

design useable

emerging technologies