Virtualizing Microsoft SQL Server on Windows Server 2012 #winserv #mssql #itpro #sqlpass

  • Article

This always makes for interesting IT Pro discussions … Best practices for virtualizing heavy-duty application workloads, such as large SQL Server installations.  With the launch of Windows Server 2012, we've greatly improved the ability to allocate large amounts of compute, network and storage resources to virtual machines.  As a result, more and more environments are looking back at the workloads that they had previously considered "un-virtualizable", and they are now planning to migrate them into virtualized Private Clouds in their datacenter to gain better management control, physical resource utilization, and time/cost savings.

Recently, I spoke at the local Professional Association for SQL Server (SQLPASS) user group in Northern Ohio on the topic of "Virtualizing SQL Server with Windows Server 2012 & Hyper-V".  It was a well-attended event with lots of interest in how best to take advantage of the new features in Windows Server 2012 for virtualized SQL Server database instance workloads.  Below, I've provided a copy of my deck along with key discussion points.  At the end of this article, I've also included a list of additional resources that I've found helpful when virtualizing SQL Server instances in my own projects.

HTH,

Keith

---- cut here ----

Virtualizing SQL Server with Windows Server 2012 & Hyper-V

Download this deck for offline viewing.

This presentation is organized into three sections below:

  • Why is Windows Server 2012 Important to SQL Server DBAs?
  • Sizing virtualized SQL Server workloads on Hyper-V
  • Configuring Hyper-V and SQL Server for virtualized workloads

Why is Windows Server 2012 Important to SQL Server DBAs?

  • What's New in Windows Server 2012? [ Slide #3 ]

    Why should SQL DBA's care about Windows Server 2012?  In Windows Server 2012, the product team has improved server capabilities across-the-board to greatly enhance scalability, high availability, storage, networking and management features to provide a server platform that can host the most demanding workloads, whether those workloads are physical, virtualized in a Private Cloud or running in a Public Cloud, such as Windows Azure.  Enterprise SQL Server workloads often push most or all of these resource areas to the max, so they are all incredibly important to SQL Server DBAs.  As we go through these areas, we'll be touching on the specific areas that I'm excited about as they pertain to hosting business-critical SQL database instances.
     

  • Massive Scalability [ Slide #5 ]

    Significant scale improvements have been made in Windows Server 2012 over prior versions of Windows Server.  A single physical server can now support up to 320 logical processors and 4TB of physical memory. In addition, to provide support for virtualizing heavy-duty workloads, such as SQL Server, each individual Virtual Machine (VM) running on Windows Server 2012 Hyper-V can be allocated up to 64 virtual processors and up to 1TB of virtual memory per VM.  Note that these levels of scalability are present on Windows Server 2012 Standard and Datacenter editions as well as the FREE Hyper-V Server 2012 product.  Windows Server 2012 Datacenter edition is recommended for heavily virtualized deployments, due to the included unlimited virtualization rights for Windows Server guest VM's.

    More importantly, not only can these tremendous increases in virtual processors and memory be allocated to each virtual machine, but the applications running inside each VM can scale to leverage these additional resources, when needed, in a near-linear fashion ( as demonstrated by the Task Manager screenshot in the slide of a large SQL Server instance running virtualized on Windows Server 2012 ).
     

  • High Availability [ Slide #7 ]

    When consolidating a number of business-critical application workloads on a smaller number of physical hosts, high availability (HA) becomes important to avoid the "all-my-eggs-in-one-basket" problems that could otherwise occur by having a single failure domain at each physical server.  In Windows Server 2012, HA is provided top-to-bottom starting from individual hardware components, and then progressing to server redundancy, application availability, and even entire datacenter resiliency.

    Some of the new Windows Server 2012 HA features that are important for large virtualized SQL Server deployments include:
     

    • Failover clusters of up to 64-nodes with up to 8,000 virtual machine instances in a single cluster, allowing the safe build-out of large, fault-tolerant "SQL utility" farms
    • Live Migration of virtual machines between hosts without application interruption between individual servers, within a cluster and between clusters using the new Shared Nothing Live Migration capabilities.  This allows you to easily move virtualized SQL database instances from dev > pilot > production servers or clusters without application downtime.
    • Network and Storage IO redundancy provided by integrated support for Network Load Balancing, NIC teaming, and Multi-path IO (MPIO).  These features offer both the potential for higher IO bandwidth as well as IO fault tolerance.  Ideal for SQL SSIS and Hadoop applications that require heavy network & storage IO for processing.
    • Application-aware Guest Clustering - providing the ability to build a virtualized SQL cluster, potentially using SQL 2012 AlwaysOn availability groups, on top of a Hyper-V host cluster.
    • Datacenter-to-Datacenter asynchronous replication of VM changes with the ability to easily test DR failover without interrupting access to the primary VMs.  Using Powershell scripts, you can easily replicate, test and enact failover of an entire set of VM's related to a multi-tier SQL database application.
       

  • Storage [ Slide #9 ]

    New storage features that are useful to SQL DBAs include:
     

    • Live Storage Migration - easily move virtualized SQL database and log disks between storage tiers during the application lifecycle without database downtime.
    • Storage Pools - providing storage virtualization, high availability and iSCSI target support without the need for dedicated SAN equipment - with any disk devices, such as inexpensive JBOD ("Just a Bunch Of Disks") storage.  Thin provisioning support is built-in.  Great for SQL database applications where tons of storage is required, but budget for dedicated SAN equipment isn't present - Hadoop, Staging tables, etc.
    • New VHDX virtual disk format - supports virtualized disks up to 64TB with block-level transaction logging for high resiliency - perfect for virtual SQL instances that need gobs of reliable virtualized disk.
    • High Storage IO performance - even from with a VM - Windows Server 2012 has been tested to ~1 million IOPS from inside a VM.  Even the most disk-intensive SQL applications can now be virtualized.
    • Offloaded Data Transfer (ODX) - if you do have a hardware SAN array that supports ODX, common tasks like database copies, fills, etc can be handled in seconds by offloading the bulk of the data transfer into the array itself.
    • Guest Fiber Channel - if you do have a fiber channel hardware SAN array, VM's can now be assigned their own virtual FC HBA's with their own worldwide names (WWNs) - simplifying the process of zoning and LUN masking with consistent WWN's regardless of which physical Hyper-V host the guest is running on.
       

  • Networking [ Slide #11 ]

    As a SQL DBA, you'll love these network improvements if you have network-bound SQL applications ...
     

    • NIC Teaming - to give you the ability to bond multiple high-speed network interfaces together into 1 logical NIC to support SQL applications that require heavy network IO, such as SSIS load/extract jobs.  This is supported by the OS using any common NIC that has drivers for Windows Server 2012.
    • SR-IOV - if you need to go even further, you can leverage NICs that support Single Root IO Virtualization (SR-IOV) to virtualize the NIC at a hardware level and pass that virtualized NIC directly into a VM without requiring interaction from the parent operating system - doing this substantially reduces the CPU overhead normally associated with the heavy network traffic found on some network-bound SQL applications.
    • IPsec Task Offload - are you supporting SQL database applications that transmit confidential or regulated information over the network?  With IPsec Task Offload, you can efficiently encrypt all data over the wire without the overhead normally associated with on-the-fly network layer encryption by leverage NICs with IPsec Task Offload support.
       

  • Management and Automation [ Slides #13 & #14 ]
     

    • PowerShell support is comprehensive throughout the entire Windows Server 2012 operating system.  All roles and features automatically include the related PowerShell cmdlets when they are added.  Overall, more than 2,400 cmdlets are included in Windows Server 2012, making it easy to script and automate pretty much any aspect of the Server operating system.  This enhanced PowerShell support is great for SSIS jobs where advanced script integration with the OS may be needed.
    • Multi-server GUI management - In addition, multiple Windows Servers can also be managed via GUI management tools using the new Server Manager dashboard and management interface.
      Enterprise Private Clouds - For managing large virtualized datacenters, or Private Clouds, System Center 2012 provides comprehensive capabilities to collect, organize and delegate datacenter compute, network and storage resources in a consistent manner - regardless of hypervisor - Hyper-V, VMware and Citrix hypervisors are all supported!

Sizing Virtualized SQL Server workloads on Hyper-V

  • Virtualization is not "FREE" [ Slide #16 ]
     
    • Actually, enterprise virtualization could be "FREE" from a cost perspective, if using the free Hyper-V Server 2012 product :-)
    • But … from a resources perspective, any type of virtualization adds an extra layer between hardware and applications … and this adds overhead
    • Based on our testing, this overhead can be approximately estimated as:
      • Processor and disk overhead - approximately 10% - 12% overhead
      • Memory overhead - 512MB for the parent "root" partition ( 80MB if using Server Core ) plus a small amount of overhead for each VM based on memory allocation
      • Additional memory will be required if you plan to regularly manage a Hyper-V host from the console of the parent "root" partition - consider adding an additional 1.5GB if you'll be managing Hyper-V hosts in this manner.
    • When sizing CPU and memory resources needed for virtualized SQL server instances ...
      • Base your initial calculations on traditional "physical" server requirements ( see below for a nice list of SQL Server sizing resources )
      • Add additional resources to account for processor, memory and disk overhead
    • For processors, focus on number of "real" cores
      • Keep in mind that "Hyper-threading" does not provide "extra cores"
      • Focus sizing on number of "real" cores needed
      • Hyperthreading can provide some performance benefits, generally 10% to 15% more processing capacity, for applications, but needs to be tested with your mix of applications.
      • Hyper-V in Windows Server 2012 understands the difference between processor sockets, cores and logical processors and tries to optimize itself automatically
        • As a result, turning Hyper-threading ON will never hurt performance - but actual performance gains need to be tested
    • To minimize processor overhead on memory and IO intensive database workloads, leverage hardware that offers offload capabilities, such as
      • Processors with large L2 caches - can help when running several VM's on the same host
      • Processor chipsets with Second Level Address Translation (SLAT) capabilities - Intel calls this "EPT" and AMD calls this "NPT"
      • Network chipsets that support network offload features, such as TCP offload, SRIOV and IPsec offload

Configuring Hyper-V and SQL Server for Virtualized Workloads

  •  Configuring the Hyper-V "Root" Partition [ Slide #17 ]
     
    • Setting up Hyper-V is incredibly easy - just install it as a new server role with the "Add / Remove Roles Wizard" in Server Manager
    • However, to get optimal results when virtualizing heavy-duty SQL Server workloads, there's a couple points to keep in mind
    • Dedicate the "root" (or Parent) partition only to Hyper-V - no other roles or applications should be installed
    • If the Hyper-V host has multiple physical NICs, consider using Hyper-V Manager to create a separate Virtual Switch per physical NIC
    • Reserve a dedicated NIC ( or NIC team ) for Live Migration, if you're planning to use it.
       
  • Configuring VM "Child" Partitions [ Slide #17 ]
     
    • Use Windows Server 2012 as the OS for each child VM for best performance
    • Set Power Scheme to "High Performance"
    • Minimize background activity and services
    • Team multiple Virtual NICs if your SQL application is network constrained - allows network load to be parallelized more efficiently
    • Use VMQ or SR-IOV, if supported by NICs
    • Disable IO Priority Management on dedicated SQL virtual disk devices ( see Performance Tuning document below )
    • Use Virtual FC SAN HBAs for Fixed VHDX virtual storage for SQL database and log files
       
  • SQL Workloads with High Memory, Transactions & CPU [ Slide #18 ] 
     
    • Except as noted, the details for each recommendation below are contained in the Performance Tuning document linked in the Additional Resources section
    • Assign "Lock Pages in Memory" to SQL Service Account
    • Set Max Worker Threads to # of maximum concurrent user connections
    • Consider leveraging Hyper-V Dynamic Memory to Reduce IO requirements ( see Dynamic Memory document below )
    • Generally, I don't recommend hard-setting Max Memory on SQL, but if you must use the formula on this slide to prevent
    • Generally, I recommend 1 SQL instance per VM, but if you must have >1 SQL instance in a VM, consider setting Affinity Mask to assign each SQL instance to particular virtual processors.
       
  • Building a New SQL VM in less than 5 minutes! [ Slide #19 ]
     
    • Build a new VM to be used as a base template - install Windows Server 2012 as the OS.
    • Prepare SQL for Imaging using Advanced options in SQL Server Setup
    • Run SYSPREP /generalize
    • Export VM using Hyper-V Manager
    • Copy VHD from exported VM as new template
    • Create New VM from copy of template VHD
    • Step-by-Step: https://aka.ms/SQL12ImagePrep
       
  • Or ... Provision SQL Server 2012 in the Cloud with Windows Azure Virtual Machines [ Slide #20 ]
     
    • Great option for quickly building test, study, pilot labs without requiring on-premise hardware.
    • Step-by-Step: https://aka.ms/SQL12CloudLab

Additional Resources

Start Your Journey!

Ready to try this out in your own lab?

  • Download Windows Server 2012 bits for FREE
  • Participate in the "Early Experts" Challenge for FREE to learn more about Windows Server 2012
  • Build your SQL Server 2012 Lab in the Cloud with Windows Azure