«Table of Contents 1. Introduction 1.1 Purpose 1.2 Target Audience 2. SQL Database Workload Characteristics and Considerations 2.1 Understand Database ...»
Architecting Microsoft SQL Server
on VMware vSphere
BEST PRACTICES GUIDE
Architecting Microsoft SQL Server on VMware vSphere
Table of Contents
1.2 Target Audience
2. SQL Database Workload Characteristics and Considerations
2.1 Understand Database Workloads
2.2 Understand Availability and Recovery Options
2.2.1 VMware Business Continuity Options
2.2.2 Native SQL Server Capabilities
3. vSphere Best Practices for SQL Server
3.1 Right Sizing
3.2 Host Configuration
3.2.1 BIOS and Firmware Versions
3.2.2 BIOS settings
3.2.3 Power Management
3.3 CPU Configuration
3.3.1 Physical, Virtual, and Logical CPUs and Cores
3.3.2 Allocating CPU to SQL Server Virtual Machines
3.3.4 NUMA Consideration
3.3.5 Cores per Socket
3.3.6 CPU Hot Plug
3.4 CPU Affinity
3.5 Memory Configuration
3.5.1 Memory Sizing Considerations
3.5.2 Memory Reservation
3.5.3 The Balloon Driver
3.5.4 Memory Hot Plug
3.6 Storage Configuration
3.6.1 vSphere Storage Options
3.6.2 Allocating Storage for SQL Server Virtual Machines Best Practices
3.6.3 All-Flash Arrays Considerations for SQL Design
3.7 Network Configuration
3.7.1 Virtual Networking Concepts
3.7.2 Virtual Networking Best Practices
4. SQL Server and In-Guest Best Practices
4.1 Windows Configuration
BEST PRACTICES GUIDE / PAGE 2 OF 54 Architecting Microsoft SQL Server on VMware vSphere 4.2 Maximum Server Memory and Minimum Server Memory
4.3 Lock Pages in Memory
4.4 Large Pages
4.5 CXPACKET, MAXDOP, and CTFP
4.6 Using Virus Scanners on SQL Server
5. VMware Enhancements for Deployment and Operations
5.1 VMware NSX for vSphere
5.1.1 NSX Edge Load balancing
5.1.2 VMware NSX Distributed Firewall
5.2 VMware vRealize Operations Manager
5.3 Site Recovery Manager
1. Introduction Microsoft SQL Server 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 combined with the low costs of x86 computing, has led to a wide variety of SQL Server installations ranging from large data warehouses to small, highly specialized departmental and application databases. The flexibility at the database layer translates directly into application flexibility, giving end users more useful application features and ultimately improving productivity.
Application flexibility often comes at a cost to operations. As the number of applications in the enterprise continues to grow, an increasing number of SQL Server installations are brought under lifecycle management. Each application has its own set of requirements for the database layer, resulting in multiple versions, patch levels, and maintenance processes. For this reason, many application owners insist on having an SQL Server installation dedicated to an application. As application workloads vary greatly, many SQL Server installations are allocated more hardware than they need, while others are starved for compute resources.
These challenges have been recognized by many organizations in recent years. These organizations are now virtualizing their most critical applications and embracing a “virtualization first” policy. This means applications are deployed on virtual machines by default rather than on physical servers and Microsoft SQL server is the most virtualized critical application.
Virtualizing Microsoft SQL Server with VMware vSphere® allows the best of both worlds, simultaneously optimizing compute resources through server consolidation and maintaining application flexibility through role isolation. Microsoft SQL Server workloads can be migrated to new sets of hardware in their current states without expensive and error-prone application remediation, and without changing operating system or application versions or patch levels. For high performance databases, VMware and partners have demonstrated the capabilities of vSphere to run the most challenging Microsoft SQL Server workloads.
Virtualizing Microsoft SQL server with vSphere enables additional benefits such as VMware vSphere vMotion®, which allows for seamless migrations of Microsoft SQL servers between physical servers and between data centers without interrupting the users or their applications. VMware vSphere Distributed Resource Scheduler™ (DRS) can be used to dynamically balance Microsoft SQL Server workloads between physical servers. VMware vSphere High Availability (HA) and VMware vSphere Fault Tolerance (FT) provide simple and reliable protection for SQL Server virtual machines and can be used in conjunction with SQL Server’s own HA capabilities.
For many organizations, the question is no longer whether to virtualize or not to virtualize the SQL server.
Rather, it is to determine the best strategy to virtualize it to achieve the business requirements while keeping operations overhead to a minimum for cost effectiveness.
1.1 Purpose This document provides best practice guidelines for designing Microsoft SQL Server on vSphere. The recommendations are not specific to any particular set of hardware or to the size and scope of any particular SQL Server implementation. The examples and considerations in this document provide guidance only and do not represent strict design requirements, as varying application requirements would result in many valid configuration possibilities.
BEST PRACTICES GUIDE / PAGE 5 OF 54 Architecting Microsoft SQL Server on VMware vSphere
1.2 Target Audience This document assumes a basic knowledge and understanding of VMware vSphere and SQL Server.
Architectural staff can use this document to gain an understanding of how the system will work as a whole as they design and implement various components.
Engineers and administrators can use this document as a catalog of technical capabilities.
DBA staff can use this document to gain an understanding of how SQL server might fit into a virtual infrastructure.
Management staff and process owners can use this document to help model business processes to take advantage of the savings and operational efficiencies achieved with virtualization.
BEST PRACTICES GUIDE / PAGE 6 OF 54 Architecting Microsoft SQL Server on VMware vSphere
2. SQL Database Workload Characteristics and Considerations When considering SQL Server instances as candidates for virtualization, you need a clear understanding of the business and technical requirements for each instance. These requirements span multiple dimensions, such as availability, performance, scalability, growth and headroom, patching, and backups.
Use the following high-level procedure to simplify the process for characterizing SQL Server candidates
1. Understand the database workload requirements for each instance of SQL Server.
2. Understand availability and recovery requirements, including uptime guarantees (“number of nines”) and disaster recovery.
3. Capture resource utilization baselines for existing physical databases.
4. Plan the migration/deployment to vSphere.
2.1 Understand Database Workloads The SQL Server database platform can support a wide variety of applications. Before deploying SQL Server on vSphere, you must understand the database workload requirements of the applications that your SQL Servers will support. Each application will have different requirements for capacity, performance, and availability, and consequently, each database should be designed to optimally support those requirements. Many organizations classify databases into multiple management tiers, using application requirements to define service level agreements (SLAs). The classification of a database server will often dictate the resources allocated to it.
• OLTP databases (online transaction processing) are many times also the most critical databases in an organization. These databases usually back customer facing applications and are considered absolutely essential to the company’s core operations. Mission-critical OLTP databases and the applications they support often have SLAs that require very high levels of performance and are very sensitive for performance degradation and availability. SQL Server virtual machines running OLTP mission critical databases might require more careful resource allocation (CPU, memory, disk, and network) to achieve optimal performance. They might also be candidates for clustering with Windows failover cluster or AlwaysOn Availability Groups. These types of databases are usually characterized with mostly intensive random write operation to disk and sustained CPU utilization during working hours.
• DSS (decision support systems) databases, can be also referred to as data warehouses. These are mission critical in many organizations that rely on analytics for their business. These databases are very sensitive to CPU utilization and read operations from disk when queries are being run. In many organizations, DSS databases are the most critical during month/quarter/year end.
• Batch, reporting services, and ETL databases are busy only during specific periods for such tasks as reporting, batch jobs, and application integration or ETL workloads. These databases and applications might be essential to your company’s operations, but they have much less stringent requirements for performance and availability. They may, nonetheless, have other very stringent business requirements, such as data validation and audit trails.
• Other smaller, lightly used databases typically support departmental applications that may not adversely affect your company’s real-time operations if there is an outage. Many times you can tolerate such databases and applications being down for extended periods.
Resource needs for SQL Server deployments are defined in terms of CPU, memory, disk and network I/O, user connections, transaction throughput, query execution efficiency/latencies, and database size.
BEST PRACTICES GUIDE / PAGE 7 OF 54 Architecting Microsoft SQL Server on VMware vSphere Some customers have established targets for system utilization on hosts running SQL Server, for example, 80 percent CPU utilization, leaving enough headroom for any usage spikes and/or availability.
Understanding database workloads and how to allocate resources to meet service levels helps you to define appropriate virtual machine configurations for individual SQL Server databases. Because you can consolidate multiple workloads on a single vSphere host, this characterization also helps you to design a vSphere and storage hardware configuration that provides the resources you need to deploy multiple workloads successfully on vSphere.
2.2 Understand Availability and Recovery Options Running Microsoft SQL Server on vSphere offers many options for database availability, backup and disaster recovery utilizing the best features from both VMware and Microsoft. This section covers the different options that exist for availability and recovery.
2.2.1 VMware Business Continuity Options VMware technologies such as vSphere HA, vSphere Fault Tolerance, vSphere vMotion, VMware vSphere Storage vMotion and VMware Site Recovery Manager™ can be used in a business continuity design to protect SQL databases from planned and unplanned downtime. These technologies also protect SQL databases from a hardware component failure to a full site failure, and in conjunction with native SQL business continuity capabilities, increase availability.
188.8.131.52. VMware vSphere High Availability vSphere HA provides easy to use, cost-effective high availability for applications running in virtual machines. vSphere HA leverages multiple VMware ESXi™ hosts configured as a cluster to provide rapid recovery from outages and cost-effective high availability for applications running in virtual machines.
vSphere HA protects application availability in the following ways:
• Protects against a physical server failure by restarting the virtual machines on other hosts within the cluster in case of a failure.
• Protects against OS failure by continuous monitoring using a heartbeat and by restarting the virtual machine in case of a failure.
• Protects against datastore accessibility failures by restarting affected virtual machines on other hosts which still have access to their datastores.
• Protects virtual machines against network isolation by restarting them if their host becomes isolated on the management network. This protection is provided even if the network has become partitioned.
• Provides APIs for protecting against application failure allowing third-party tools to continuously monitor an application and reset the virtual machine in the event that a failure is detected.
Figure 1. vSphere HA 2.
2.1.2. vSphere Fault Tolerance vSphere Fault Tolerance provides a higher level of availability, allowing users to protect any virtual machine from a physical host failure with no loss of data, transactions, or connections. vSphere FT provides continuous availability by ensuring that the states of the primary and secondary VMs are identical at any point in the instruction execution of the virtual machine. If either the host running the primary VM or the host running the secondary VM fails, an immediate and transparent failover occurs.
The functioning ESXi host seamlessly becomes the primary VM host without losing network connections or in-progress transactions. With transparent failover, there is no data loss, and network connections are maintained. After a transparent failover occurs, a new secondary VM is respawned and redundancy is reestablished. The entire process is transparent, fully automated, and occurs even if VMware vCenter Server™ is unavailable.
There are licensing requirements and interoperability limitations to consider when using fault tolerance as