EMC MISSION CRITICAL INFRASTRUCTURE FOR MICROSOFT SQL SERVER 2012

1. EMC MISSION CRITICAL INFRASTRUCTURE FOR MICROSOFT SQL SERVER 2012 EMC Symmetrix VMAXe, EMC FAST VP, SQL Server AlwaysOn Availability Groups, VMware vSphere Technical Presentation EMC Solutions Group

2. Agenda • Solution Overview • Architecture Design • Test Results • Findings and Recommendations • Summary • Q&A

3. Solution Overview

4. Solution Purpose • SQL Server 2012 AlwaysOn • Characterize the performance and functionality of enterprise-class Microsoft SQL Server 2012 in a virtualized environment with AlwaysOn technology. • Demonstrate the easy access to readable secondary replica with the new SQL Server 2012 AlwaysOn transaction-level replication technology that has minimum impact on the primary databases. • EMC Symmetrix VMAXe FAST VP • Optimize database storage performance with FAST VP on the Symmetrix VMAXe storage (Flash, FC, and SATA tiers). • Integrate simple self-tuning in an advanced enterprise-class storage system cost-effectively. • Virtualization integration • Showcase Symmetrix VMAXe’s integration with VMware, which simplifies the array management and reduces total cost of ownership. With this integration, SQL Server 2012 can be easily virtualized with VMware vSphere.

5. Key Components • Microsoft SQL Server 2012 • AlwaysOn feature set: Readable secondary replica on availability groups (AG) • EMC Symmetrix VMAXe storage array • EMC Fully Automated Storage Tiering for Virtual Pools (FAST VP) • VMware vSphere 5 • More manageable resources for virtual machines • EMC Virtual Storage Integrator (VSI) • Easy viewing and provisioning of Symmetrix LUN information to virtual machines • EMC Symmetrix Management Console (SMC) and Symmetrix Performance Analyzer (SPA)

6. Microsoft SQL Server 2012 AlwaysOn • Primary replica • Primary role • One replica for each availability group • Secondary replica • Secondary role • Up to four replicas • Readable secondary replica (read-only secondary replica) • Readable secondary replicas on Availability Groups

7. FAST VP • Built-in automation and intelligence • Optimized performance and reduced cost • Simplified management and increased storage efficiency • More granular movement of sub-LUN data between tiers • Support for two or three storage tiers

8. EMC Virtual Storage Integration with VMware • VMAXe integrated with vSphere 5 EMC Virtual Storage Integrator • VSI is a vCenter plug-in • Built-in storage insight • Bi-directional “server-to-storage” and “storage-to-server” capability

9. EMC Symmetrix Management Console • Browser-based graphical interface • Windows/Linux server hosted • Web browser access • Light-weight architecture

10. EMC Symmetrix Performance Analyzer • Automated monitoring and trending tool • Heat map • Snapshot view • Trend and forecast • Real-time monitoring • Diagnostics

11. Architecture Design

12. Solution Architecture AlwaysOn Replication Two vSphere ESXi hosts Two SQL Server virtual machines (one primary and one secondary) Symmetrix VMAXe served as the SAN storage. FAST VP has three tiers enabled to serve the storage needs of the environment.

13. Storage Design • FAST VP thin pools (on both primary and secondary replicas) are placed across all the following tiers: • These pools served the SQL Server OLTP database, log, tempDB, and log LUNs for each site of the availability group. • 16 SATA disks of 2 TB served as the operating system volumes for SQL Server virtual machines and the operating system volumes of the testing client.

14. Microsoft SQL Server

15. Storage design for SQL Server

16. Hardware Profile

17. Software Components

18. Test Results

19. Test Results—FAST VP Before enabling FAST VP, the storage was bound to the FC tier After enabling FAST VP, the storage was in a balanced state and the data was on three tiers

20. Test Result–FAST VP • FAST VP sampling : 2 hours before analysis starts • FAST VP analysis: 2 hours before initial data movement • FAST VP data move stabilized: 4 hours

21. Test Results—Effects of Indirect Checkpoint Manual checkpoint with high I/O spikes Indirect checkpoint without I/O spikes

22. Test Results—Effects of AG Secondary Copy • With the same load, upon making the same database part of AG with the secondary replica in synchronization mode: • Total IOPS decreased slightly • Transactions per second (TPS) for load clients decreased slightly • Disk latency kept the same

23. Test Results—AG Readable Secondary Copy

24. Test Results—AG Readable Secondary Copy (continued)

25. Test Results—AG Readable Secondary Copy (continued)

26. Test Results—AG Readable Secondary Copy (continued) Symmetrix VMAXe Performance • Back end and front end showed no sign of stress • Sixteen ports used with multipath load-balancing (PowerPath/VE); IOPS were evenly distributed

27. Test Results—AG Readable Secondary Copy (continued) Symmetrix VMAXe Performance • With up to 30,000 host IOPS, the Symmetrix VMAXe front-end and back-end CPU utilization was under 70 percent. • Disk utilization was also under 50 percent, indicating the Symmetrix VMAXe system was capable of handling further load.

28. Test Results—AG Readable Secondary Copy (continued) VMware vSphere Performance CPU usage for ESXi of primarySQL Server virtual machine CPU usage for ESXi of secondarySQL Server virtual machine • For the primary ESXi server, the CPU usage was approximately 55 percent; for the secondary ESXi server, it was approximately 25 percent.

29. Findings and Recommendations • Recommendations • EMC Symmetrix VMAXe storage array: • Factory pre-configured setting should be used for most setups • Virtualization integration makes it easy to provision storage for virtual machines • VMware paravirtual SCSI (PVSCSI) adapters are best suited for SANs with high demands of I/O throughput. There is up to 25 percent improvement over legacy VMware virtual LSI Controllers.

30. Findings and Recommendations (cont’d) • Findings • By moving OLAP read-only workload from the primary replica to the secondary, CPU usage was offloaded greatly. More TPS and IOPS for both OLTP and OLAP loads were achieved. • The process of creating availability groups requires enough storage to hold the compressed backups and completed within 6 hours. • Reducing HBA ports queuing for vSphere ESXi servers by changing the queue depth improved I/O performance by 30 percent. • Indirect checkpoints could be used to reduce the I/O spike • Enabling availability groups in synchronous mode has very little impact on the OLTP load.

31. Summary • Symmetrix VMAXe can easily service over 30,000 OLTP IOPS for SQL Server 2012 Availability Group with a readable secondary copy. • A readable secondary copy in SQL Server 2012 Availability Group can offload OLAP load from primary database with minimal impact on the system. • EMC FAST VP maximizes storage efficiency by auto-tiering of SQL Server database storage to the most suitable storage tier. • Symmetrix VMAXe’s integration with VMware simplifies provisioning, managing, and monitoring the virtualized SQL Server 2012 environment.

32. Q&A