EMC Extreme Performance and Efficiency for Microsoft SQL ...

1 Solution Guide EMC Extreme Performance AND. Efficiency FOR Microsoft SQL SERVER. EMC XtremIO, vmware vsphere , and Microsoft SQL Server 2014. Optimize very high throughput for SQL Server workloads Virtualize and consolidate database instances Minimize storage footprint for multiple database copies EMC Solutions Abstract This solution guide describes the operational advantages of virtualized Microsoft SQL Server 2014 databases deployed on an EMC XtremIO all-flash array, and how the solution enhances the capabilities of SQL Server database- dependent environments. April 2015. Copyright 2015 EMC Corporation. All rights reserved. Published in the USA. Published April 2015. EMC believes the information in this publication is accurate as of its publication date.

2 The information is subject to change without notice. The information in this publication is provided as is. EMC Corporation makes no representations or warranties of any kind with respect to the information in this publication, and specifically disclaims implied warranties of merchantability or fitness for a particular purpose. Use, copying, and distribution of any EMC software described in this publication requires an applicable software license. EMC2, EMC, and the EMC logo are registered trademarks or trademarks of EMC. Corporation in the United States and other countries. All other trademarks used herein are the property of their respective owners. For the most up-to-date listing of EMC product names, see EMC Corporation Trademarks on EMC Extreme Performance and Efficiency for Microsoft SQL Server Solution Guide Part Number H13870.

3 2 EMC Extreme Performance and Efficiency for Microsoft SQL Server EMC XtremIO, vmware vsphere , and SQL Server 2014. Solution Guide Contents Contents Chapter 1 Executive Summary 7. Document purpose .. 8. Audience .. 8. Business case .. 8. Solution purpose .. 9. Key 10. 10. Chapter 2 Solution Overview 11. Introduction .. 12. EMC XtremIO .. 12. Key advantages .. 12. vmware vsphere .. 13. Microsoft SQL Server 2014 .. 14. SQL Server clustering storage considerations .. 14. SQL Server Data 15. Chapter 3 Solution Architecture 16. Overview .. 17. Architecture diagram .. 17. Hardware 18. Software resources .. 19. Chapter 4 Storage Layer: EMC XtremIO 20. Storage design overview .. 21. Database considerations .. 21. Storage design details.

4 22. Chapter 5 Microsoft SQL Server Solution Design 24. Overview .. 25. SQL Server database solution storage design .. 25. SQL Server database solution profile .. 25. SQL Server database solution LUN design .. 26. Chapter 6 Network Layer Best Practices 27. Overview .. 28. EMC Extreme Performance and Efficiency for Microsoft SQL Server 3. EMC XtremIO, vmware vsphere , and SQL Server 2014. Solution Guide Contents SAN network .. 28. IP network .. 28. vmware vsphere network .. 28. Chapter 7 Physical Servers and Virtualization Layer 30. Overview .. 31. Compute and storage resources .. 31. Network virtualization .. 32. Storage management with ESI .. 32. Chapter 8 Design Considerations 33. Overview .. 34. XtremIO configuration best practices.

5 34. Configuring Fibre Channel switches .. 34. Configuring servers .. 34. Configuring vsphere Native Multipathing .. 36. Placing XtremIO devices under PowerPath's control .. 38. Configuring XtremIO storage for SQL Server using ESI .. 38. Provisioning XtremIO .. 39. Creating and mounting a snapshot using AppSync .. 42. Creating a snapshot of an existing SQL Server database with AppSync .. 42. Mounting a snapshot of a SQL Server database with AppSync .. 45. Chapter 9 Performance Testing and Validation 47. Overview .. 48. Notes on 48. Test 48. Test 48. OLTP workload Performance and scale test .. 49. Test methodology .. 49. Test procedure .. 49. Test results .. 50. XtremIO system Performance .. 51. XtremIO and SQL Server OLTP read/write workloads.

6 52. VMDK and RDM Performance comparison .. 52. Test methodology .. 52. Test procedure .. 53. Test results .. 53. XtremIO data reduction analysis .. 54. Snapshot data reduction .. 54. 4 EMC Extreme Performance and Efficiency for Microsoft SQL Server EMC XtremIO, vmware vsphere , and SQL Server 2014. Solution Guide Contents Production database data reduction rate .. 55. XtremIO Data reduction with SQL Server AlwaysOn Availability Groups .. 55. SQL Server row and page compression on XtremIO .. 57. Test methodology .. 58. Test procedure .. 58. Test results .. 58. SQL Server Failover Clustering on vmware setup and analysis .. 59. LUN setup .. 59. Cluster setup validation .. 61. Chapter 10 Conclusion 62. Summary .. 63. Findings.

7 63. Chapter 11 References 64. EMC documentation .. 65. vmware 65. Microsoft SQL Server documentation .. 65. Figures Figure 1. XtremIO Storage Management Application dashboard .. 12. Figure 2. XtremIO all-flash array family .. 13. Figure 3. AlwaysOn FCI versus AAG .. 14. Figure 4. Solution architecture .. 17. Figure 5. XtremIO storage capacity consumption planning .. 22. Figure 6. XtremIO Management Application dashboard storage panel .. 22. Figure 7. XtremIO dual X-Brick FC switch configuration .. 34. Figure 8. Changing the I/O Throttle Count setting for Cisco UCS Server .. 35. Figure 9. Storage device path management configuration .. 37. Figure 10. XtremIO SSD LUN under PowerPath management .. 38. Figure 11. Setting up XtremIO in ESI.

8 39. Figure 12. Creating a new XtremIO volume in ESI .. 40. Figure 13. Viewing XtremIO storage capacity in ESI .. 40. Figure 14. Viewing XtremIO storage groups in ESI .. 41. Figure 15. XtremIO volume mappings in ESI .. 41. Figure 16. Assigning an RDM to SQL Server for database storage in ESI .. 42. Figure 17. Adding XtremIO to AppSync .. 43. Figure 18. Configuring SQL Server in AppSync .. 43. Figure 19. Selecting the database and the protection plan for the snapshot .. 44. EMC Extreme Performance and Efficiency for Microsoft SQL Server 5. EMC XtremIO, vmware vsphere , and SQL Server 2014. Solution Guide Contents Figure 20. XtremIO snapshots created with AppSync .. 44. Figure 21. Selecting SQL Server mount copy 45. Figure 22.

9 Mounted snapshot ready for read/write operation on the recovered database copy .. 45. Figure 23. Mounted database recovered as a read/write copy ready for database operations .. 46. Figure 24. SQL Server and XtremIO scalability test .. 50. Figure 25. XtremIO Performance with full OLTP work load on SQL Server 2014 .. 51. Figure 26. SQL Server 2014 disk I/O Performance .. 51. Figure 27. SQL Server 2014 database transactional Performance .. 52. Figure 28. IOPS for the same workload against RDM and VMDK .. 53. Figure 29. SQL Server and XtremIO latency with RDM and VMDK .. 53. Figure 30. XtremIO snapshot data Efficiency .. 55. Figure 31. Deduplication ratio and physical space used before and after creating the AAG .. 56. Figure 32.

10 Volume and physical space used for SQL Server AAG instance .. 56. Figure 33. XtremIO physical storage usage during a bulk 57. Figure 34. Space savings from SQL Server native compression .. 58. Figure 35. Performance improvement from SQL Server compression and CPU usage change .. 59. Figure 36. vmware virtual machine disk configuration for an AlwaysOn FCI .. 60. Figure 37. XtremIO and SQL Server OLTP Performance with an AlwaysOn FCI on vsphere .. 61. Tables Table 1. 10. Table 2. Hardware 18. Table 3. Software resources .. 19. Table 4. Microsoft SQL Server storage design on XtremIO .. 23. Table 5. Volume assignments for OLTP Databases .. 23. Table 6. SQL Server 2014 OLTP database profiles .. 25. Table 7. LUN design details for the SQL Server 2014 OLTP databases.