Best Practices for Virtualizing and Managing SQL Server

1 Best Practices for Virtualizing and Managing SQL Server 2012 1 1 Best Practices for Virtualizing and Managing SQL Server May 2013 Best Practices for Virtualizing and Managing SQL Server 2012 2 2 Copyright Information 2013 microsoft Corporation. All rights reserved. This document is provided "as-is." Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it. This document does not provide you with any legal rights to any intellectual property in any microsoft product. You may copy and use this document for your internal, reference purposes. You may modify this document for your internal, reference purposes. Best Practices for Virtualizing and Managing SQL Server 2012 3 3 Table of Contents Introduction.

2 6 Executive Summary .. 6 Target Audience .. 6 Scope .. 7 Why Virtualize SQL Server ? .. 8 Why microsoft Virtualization and Management? .. 9 Fabric 11 Hardware Considerations .. 11 Scalability Maximums of Windows Server 2012 Hyper-V .. 12 microsoft Assessment and Planning 13 Compute Considerations .. 13 Logical Processors on Hardware .. 14 Virtual Processor or Virtual CPU .. 15 Non-Uniform Memory Access Host Perspective .. 16 Root/Host Reserve .. 17 Page File Guidance .. 18 Storage Considerations .. 19 Storage Options for Hyper-V Virtual 19 Storage Protocols and Additional Features .. 23 Networking Considerations .. 31 Host Resiliency with NIC 31 Hyper-V Extensible Switch .. 32 Virtual LANs .. 34 Converged Networking through Data Center Bridging .. 34 Host Resiliency and Virtual Machine Agility .. 36 Host Clustering.

3 36 Best Practices for Virtualizing and Managing SQL Server 2012 4 4 Failover Clustering .. 36 Cluster Shared Volumes .. 39 Clustering Recommendations .. 40 Virtual Machine Priority .. 41 Virtual Machine Affinity .. 42 Live Migration .. 42 Hyper-V Replica .. 44 Virtual Machine Configuration .. 45 microsoft Assessment and Planning Toolkit .. 45 SQL CPU Considerations .. 45 Non-Uniform Memory Access Virtual Machine Perspective .. 45 SQL Memory Considerations .. 47 Dynamic Memory .. 47 SQL Storage Considerations .. 50 Virtual Disks .. 50 Guest Storage .. 51 SQL Networking Considerations .. 53 Dynamic Virtual Machine Queue .. 53 Single Root I/O Virtualization .. 54 56 QoS Bandwidth Management .. 57 SQL Server Resiliency .. 58 High Availability with Failover Clustering .. 58 High Availability with Guest Clustering.

4 59 60 AlwaysOn Failover Cluster Instances .. 60 AlwaysOn Availability Groups .. 61 System Center 2012 SP1 .. 63 Comprehensive SQL Server Management .. 63 Virtual Machine Manager .. 64 Best Practices for Virtualizing and Managing SQL Server 2012 5 5 Centralized Fabric Configuration .. 64 Virtual Machine Creation .. 65 Virtual Machine Deployment .. 71 Dynamic Optimization .. 72 Virtual Machine Priority and Affinity .. 73 Availability 74 App Controller .. 76 Service Delivery and Automation .. 79 Service Manager .. 79 80 Cloud Services Process Pack .. 82 The IaaS Solution .. 82 Operations Manager .. 85 SQL Server 2012 Management 85 Data Protection Manager .. 87 Conclusion .. 90 Additional Resources .. 91 References .. 92 Best Practices for Virtualizing and Managing SQL Server 2012 6 6 Introduction This guide provides high-level best Practices and considerations for deploying and Managing microsoft SQL Server 2012 on a microsoft virtualization infrastructure.

5 The recommendations and guidance in this document aim to: Complement the architectural design of an organization s specific environment. Help organizations take advantage of the key platform features in SQL Server 2012 to deliver the highest levels of performance and availability. Executive Summary With the proliferation of applications in everyday use, enterprises have an increasing need for more instances of databases such as SQL Server . Most applications have their own set of database requirements, leading to the use of multiple versions of databases and to significant costs related to the databases and related hardware resources. More importantly, the hardware deployed for databases is not fully utilized or there is a demand to scale up hardware resources to meet peak utilization on certain databases. Therefore, it has become important to control the cost of databases and related hardware resources, and to optimize and scale the use of hardware resources in different scenarios to provide better flexibility and maintain service level agreements (SLAs).

6 An ideal solution to this problem is virtualization. Virtualization is fairly common now. Many organizations worldwide have moved beyond the nascent stage into being more advanced users of Server virtualization, specifically. These organizations have gained benefits in the areas of costs, efficiency, operations, availability, agility, and resiliency. microsoft SQL Server 2012 and Windows Server 2012 provide a host of new features that can be used to effectively virtualize demanding database workloads that previously were not considered for virtualization. This guide explains these new features in the context of how organizations should consider Virtualizing SQL Server 2012 on Windows Server 2012 and the benefits of Managing this virtualized environment with microsoft System Center 2012 . Working together, these industry-leading products deliver an integrated platform with a low total cost of ownership (TCO) as well as mission-critical scale, performance, and availability.

7 The platform also provides enhanced end-to-end security, management, and monitoring capabilities. Further, many organizations now want to go a step beyond and adopt an IT infrastructure that is optimized for and ready to work in the cloud. They need an IT infrastructure that can seamlessly span from a private to a public cloud. To achieve this goal, organizations require a common virtualization platform that can increase performance and efficiency across the infrastructure. In this regard, Windows Server 2012 Hyper-V offers an ideal virtualization platform for SQL Server 2012 . Target Audience This guide is intended for IT professionals and technical decision makers (TDMs), including IT consultants, architects, database administrators, and IT managers. They can use this guide to understand how to set up an environment for Virtualizing SQL Server 2012 using an integrated virtualization platform built on some Best Practices for Virtualizing and Managing SQL Server 2012 7 7 of the latest microsoft technologies, such as Windows Server 2012 Hyper-V and System Center 2012 .

8 Understanding key considerations and best Practices can help TDMs effectively plan and deploy SQL Server 2012 virtualization using Windows Server 2012 Hyper-V. This guide serves the following purposes for these key roles: Architects: Understand how the entire virtualization environment will work as they design the architecture. IT Managers: Design processes to fit the overall virtualization environment so that costs are reduced and efficiency is increased as much as possible. Database Administrators: Understand how SQL Server 2012 can be set up and function in the virtual environment. Scope This guide focuses on providing an understanding of the key considerations for Virtualizing SQL Server 2012 on a Windows Server 2012 host system, virtual machine, or other environment. At a broad level, the guide is divided into the following sections: Fabric Configuration: Covers the key requirements, features, and considerations for infrastructure that are necessary to set up the virtualization environment.

9 This includes best practice considerations for physical hosts and compute requirements for processors, memory, storage, and networks. Fabric/Host Resiliency: Provides information related to Hyper-V host clustering and resiliency, and introduces features that enable resiliency on host systems, such as failover clustering and Cluster Shared Volume. Virtual Machine Configuration for SQL Server and SQL Server Resiliency: Highlights best practice considerations related to configuring virtual machines for SQL Server 2012 . Provides information related to SQL Server resiliency in different scenarios, including a single cluster, clustering across multiple hosts, and Hyper-V Replica. System Center Enhancements: Provides an overview of how System Center 2012 SP1 supports deploying and Managing SQL Server 2012 across the infrastructure (that is, on-premises and in the cloud).

10 Best Practices for Virtualizing and Managing SQL Server 2012 8 8 Why Virtualize SQL Server ? In the past, database workloads were the exception for virtualization and were not viewed as prime candidates for consolidation because of their higher performance and scalability Previous virtualization solutions had limitations that hindered their ability to meet the high performance and massive scalability requirements for workloads like SQL Server 2012 . With Server virtualization today, organizations are able to lower IT capital and operational costs and attain greater IT efficiency with improved application provisioning, maintenance, availability, and backup/recovery processes. With recent technological advances, complex database workloads can be more readily consolidated using virtualization. The improvements in Windows Server 2012 Hyper-V and its super-sized virtual machine capabilities have eliminated most of the limits related to performance in a virtualized environment.