ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE

BEST PRACTICES GUIDE – APRIL 2019ARCHITECTING MICROSOFTSQL SERVER ON VMWAREVSPHERE Best Practices Guide

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE Table of Contents1. Introduction 81.1 Purpose 91.2 Target Audience 92. SQL Server Requirements Considerations 102.1 Understand SQL Server Workloads 102.2 Business Continuity Options 112.2.1 VMware vSphere Features for Business Continuity 112.2.2 SQL Server Availability Features for Business Continuity 122.3 VMware Cloud on AWS 132.4 SQL Server on vSphere Supportability Considerations 143. Best Practices for Deploying SQL Server Using vSphere 153.1 Right-Sizing 153.2 vCenter Server Configuration 163.3 ESXi Cluster Compute Resource Configuration 173.3.1 vSphere High Availability 173.3.2 VMware DRS Cluster 193.3.3 VMware Enhanced vMotion Compatibility 203.3.4 Resource Pools 203.4 ESXi Host Configuration 213.4.1 BIOS/UEFI and Firmware Versions 213.4.2 BIOS/UEFI Settings 213.4.3 Power Management 223.5 Virtual Machine CPU Configuration 223.5.1 Physical, Virtual, and Logical CPU and Core 233.5.2 Allocating vCPU 243.5.3 Hyper-Threading 253.5.4 Cores per Socket 253.5.5 CPU Hot Plug 253.5.6 CPU Affinity 273.5.7 Per Virtual Machine EVC Mode 273.6 NUMA Considerations 273.6.1 Understanding NUMA 27B EST PR AC TICES G U IDE 2

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE Table of Contents, continued3.6.2 Using NUMA: Best Practices 283.7 Virtual Machine Memory Configuration 393.7.1 Memory Sizing Considerations 403.7.2 Memory Reservation 413.7.3 The Balloon Driver 423.7.4 Memory Hot Plug 433.7.5 Persistent Memory 433.8 Virtual Machine Storage Configuration 453.8.1 vSphere Storage Options 453.8.2 VMware vSAN 503.8.3 Storage Best Practices 553.9 Virtual Machine Network Configuration 603.9.1 Virtual Network Concepts 603.9.2 Virtual Networking Best Practices 613.9.3 Using multi-NIC vMotion for High Memory Workloads 623.9.4 Enable Jumbo Frames for vSphere vMotion Interfaces 633.10 vSphere Security Features 633.10.1 Virtual Machine Encryption 643.10.2 vSphere 6.7. New Security Features 643.11 Maintaining a Virtual Machine 643.11.1 Upgrade VMware Tools 653.11.2 Upgrade the Virtual Machine Compatibility 654SQL Server and In-Guest Best Practices 674.1 Windows Server Configuration 674.1.1 Power Policy 674.1.2 Enable Receive Side Scaling (RSS) 684.1.3 Configure PVSCSI Controller 694.1.4 Using Antivirus Software 704.1.5 Other Applications 704.2 Linux Server Configuration 704.2.1 Supported Linux Distributions 704.2.2 VMware Tools 704.2.3 Power Scheme 70B EST PR AC TICES G U IDE 3

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE Table of Contents, continued4.2.4 Receive Side Scaling 724.3 SQL Server Configuration 724.3.1 Maximum Server Memory and Minimum Server Memory 724.3.2 Lock Pages in Memory 734.3.3 Large Pages 734.3.4 CXPACKET, MAXDOP, and CTFP 754.3.5 Instance File Initiation 755.VMware Enhancements for Deployment and Operations 775.1 Network Virtualization with VMware NSX for vSphere 775.2 VMware vRealize Operations Manager 776. Resources 797.Acknowledgments 82B EST PR AC TICES G U IDE 4

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE List of FiguresFigure 1. vCenter Server Statistics 17Figure 2. vSphere HA Settings 18Figure 3. vSphere Admission Control Settings 18Figure 4. Proactive HA 19Figure 5. vSphere DRS Cluster 19Figure 6. VMware EVC Settings 20Figure 7. Recommended ESXi Host Power Management Setting 22Figure 8. Physical Server CPU Allocation 23Figure 9. CPU Configuration of a VM 24Figure 10. Disabling CPU Hot Plug (Uncheck Enable CPU Hot Add Checkbox) 26Figure 11. The vmdumper Command Provided VM Configuration for a VM with“CPU Hot Add” Enabled 26Figure 12. Intel-based NUMA Hardware Architecture 28Figure 13. Using esxcli and Shed-stats Commands to Obtain the NUMA NodeCount on an ESXi Host 29Figure 14. Using esxtop to Obtain NUMA-related Information on an ESXi Host 30Figure 15. VM Cores per Socket Configuration 31Figure 16. Checking NUMA topology with the vmdumper Command 35Figure 17. Windows Server 2016 Resource Monitor Exposing NUMAInformation 36Figure 18. Output of coreinfo Command Showing a NUMA Topology for 24cores/2socket VM 37Figure 19. Using the numactl Command to Display the NUMA topology 38Figure 20. Using dmesg Tool to Display the NUMA Topology 38Figure 21. Displaying the NUMA Information in the SQL Server ManagementStudio 38Figure 22. Errorlog Messages for Automatic soft-NUMA on 12 Cores per SocketVM 38Figure 23. sys.dm os nodes Information on a System with Two NUMA Nodesand Four Soft-NUMA Nodes 39B EST PR AC TICES G U IDE 5

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE List of Figures, continuedFigure 24. Memory Mappings Between Virtual, Guest, and Physical Memory 40Figure 25. Setting Memory Reservation 41Figure 26. Setting Memory Hot Plug 43Figure 27. Positioning PMem 44Figure 28. VMware Storage Virtualization Stack 46Figure 29. VMFS vs. RDM: DVD Store 3 Performance Comparison 48Figure 30. vSphere Virtual Volumes 49Figure 31. VMware vSAN Architecture 50Figure 32. vSAN Cluster Services 51Figure 33. Configure recommended SPBM 52Figure 34. Configure Object Space Reservation in SPBM 53Figure 35. Take Snapshot Options 59Figure 36. Virtual Networking Concepts 62Figure 37. vMotion of a Large Intensive VM with SDPS Activated 63Figure 38. Utilizing Multi-NIC vMotion to Speed Up vMotion Operation 67Figure 39. Windows Server CPU Core Parking 68Figure 40. Recommended Windows OS Power Plan 68Figure 41. Enable RSS in Windows OS 69Figure 42. Enable RSS in VMware Tools 67Figure 43. Updating the VMware Tools as Part of an Ubuntu Update 71Figure 44. Showing the VMware Tools Under RHEL 71Figure 45. Enable Instant File Initialization 76B EST PR AC TICES G U IDE 6

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE List of TablesTable 1. SQL Server 2012 High Availability Options 13Table 2. Standard VM Configuration: Recommended vCPU Settings for DifferentNumber of vCPU 32Table 3. Advanced vNUMA VM Configurations: Recommended vCPU Settings 33Table 4. Sample Overhead Memory on Virtual Machines 41Table 5. Typical SQL Server Disk Access Patterns 56B EST PR AC TICES G U IDE 7

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE 1. IntroductionMicrosoft SQL Server 1 is one of the most widely deployed database platforms in theworld, with many organizations having dozens or even hundreds of instances deployedin their environments. The flexibility of SQL Server, with its rich application capabilitiescombined with the low costs of x86 computing, has led to a wide variety of SQLServer installations ranging from large data warehouses with business intelligence andreporting features to small, highly specialized departmental and application databases.The flexibility at the database layer translates directly into application flexibility, givingend users more useful application features and ultimately improving productivity.Application flexibility often comes at a cost to operations. As the number ofapplications in the enterprise continues to grow, an increasing number of SQL Serverinstallations are brought under lifecycle management. Each application has its own setof requirements for the database layer, resulting in multiple versions, patch levels, andmaintenance processes. For this reason, many application owners insist on having aSQL Server installation dedicated to an application. As application workloads varygreatly, many SQL Server installations are allocated more hardware resources thanthey need, while others are starved for compute resources.These challenges have been recognized by many organizations in recent years. Theseorganizations are now virtualizing their most critical applications and embracing a“virtualization first” policy. This means applications are deployed on virtual machines(VMs) by default rather than on physical servers, and SQL Server is the mostvirtualized critical application in the past few years.Virtualizing SQL Server with vSphere allows for the best of both worlds,simultaneously optimizing compute resources through server consolidation andmaintaining application flexibility through role isolation, taking advantage of thesoftware-defined data center (SDDC) platform and capabilities such as network andstorage virtualization. SQL Server workloads can be migrated to new sets of hardwarein their current states without expensive and error-prone application remediation, andwithout changing operating system (OS) or application versions or patch levels. Forhigh performance databases, VMware and partners have demonstrated thecapabilities of vSphere to run the most challenging SQL Server workloads.Virtualizing SQL Server with vSphere enables many additional benefits. For example,vSphere vMotion , which enables seamless migration of virtual machines containingSQL Server instances between physical servers and between data centers withoutinterrupting users or their applications. vSphere Distributed Resource Scheduler (DRS) can be used to dynamically balance SQL Server workloads between physicalservers. vSphere High Availability (HA) and vSphere Fault Tolerance (FT) providesimple and reliable protection for virtual machines containing SQL Server and can be1Further in the document referenced as SQL ServerB EST PR AC TICES G U IDE 8

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE used in conjunction with SQL Server’s built-in HA capabilities. Among other features,VMware NSX provides network virtualization and dynamic security policyenforcement. VMware Site Recovery Manager provides disaster recovery planorchestration, vRealize Operations manager provides comprehensive analytic andmonitoring engine, and VMware Cloud on AWS can be consumed to take theadvantages of public cloud. There are many more benefits that VMware can providefor the benefit of virtualized applications.For many organizations, the question is no longer whether to virtualize SQL Server,rather, it is to determine the best architecture design to achieve the business andtechnical requirements while keeping operational overhead to a minimum for costeffectiveness.1.1 PurposeThis document provides best practice guidelines for designing and implementing SQLServer in virtual machine to run on vSphere (further referenced as vSphere). Therecommendations are not specific to a particular hardware set, or to the size andscope of

ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE 1. Introduction Microsoft SQL Server 1 is one of the most widely deployed database platforms in the world, with many organizations having dozens or even hundreds of instances deployed in their environments. The flexibility of SQL Server, with its rich application capabilities