How to Build a Hyper-V VM for SQL Server

There are many design considerations that can dictate how to build your virtual machines (VMs), a major consideration is storage. This blog will focus on using virtual hard disks (VHDs) stored locally on the hypervisor – either through direct-attached disks, shared storage or highly available cluster shared volumes (CSVs). Accessing storage directly from the VM via iSCSI targets or Fiber Channel mounts was popular prior to Hyper-V 2012. Oftentimes, the case for this design was due to the 2TB VHD limit imposed by all major hypervisor vendors. With Hyper-V 2012 and VMWare 5.5, this limit has been increased to 64TB!

Microsoft SQL Server can demand high IO and uses several different disks for multiple types of workloads. This guide will walk through building a SQL Server with different VHDs on separate controllers. These VHDs should not be stored on the same storage device unless it is an all-flash array if you want to maintain a high level of performance. If you deploy to a Storage Space or a SAN, use multiple arrays across separate sets of spindles to spread the IO. Designing that kind of architecture, to include Hyper-V Manager, is a career in itself!

The Hyper-V 2012 R2 environment can be controlled (preferably) from any Windows 8.1 workstation. To do this, install the RSAT Tools on your local workstation. Run Hyper-V Manager and connect to your hypervisor. Alternatively, Remote Desktop into the hypervisor and use Hyper-V Manager to connect to the local computer.

Let’s begin with a basic Virtual Machine configuration.

  1. Click New > Virtual Machine… A wizard will open and walk you through the process. For the steps below, configure the indicated page as described and click Next.pbitpro-sql-hyperv-vm-figure-1
  2. Specify Name and Location: Choose a name for the new VM and choose where to store the VM if desired.pbitpro-sql-hyperv-vm-figure-2
  3. VM Generation: If using Hyper-V 2012 R2, choose Generation 2 if you do not plan to move this VM to a Hyper-V 2012 or earlier server.pbitpro-sql-hyperv-vm-figure-3
  4. Assign Memory: Choose how much memory to assign the VM and do not choose Dynamic Memory. 4GB is a good starting point for most servers prior to running SQL Server with any expected workload. Remember to size the memory correctly after you know how much memory is required.pbitpro-sql-hyperv-vm-figure-4
  5. Configure Networking: Choose the proper network switch.pbitpro-sql-hyperv-vm-figure-5
  6. Connect Virtual Hard Disk: Choose where to create a new VHD, select an existing one, or choose to wait until later to attach a VHD. If you chose to create a new VHD, choose from where to install the operating system.pbitpro-sql-hyperv-vm-figure-6

Recommendation: Name the VHD “[HOSTNAME]-BOOT.vhdx” to identify the owner of the drive and provide a simple description.

Do this for all disks, even if added later, to ease maintenance of the system.

Storage Live Migration allows the Hyper-V Administrator to move VHDs around the system without downtime.

A simple naming scheme will help you identify VHDs and confirm they are properly located.

  1. Summary: Verify the settings you chose in the summary and click Finish to create the VM.

Now, we continue into more advanced settings of the VM.

  1. From the middle pane in Hyper-V Manager, click on the VM you just created and click Settings… on the lower right.pbitpro-sql-hyperv-vm-figure-7
  2. Add Hardware: Select SCSI Controller and add three new SCSI controllers. Multiple controllers allow more distribution of disk queues and IO. This design works best when you can put each controller on its own storage array.pbitpro-sql-hyperv-vm-figure-8
    1. 1st Controller: Add 2 VHDs and one DVD Drive
      1. SQL01-Boot.vhdx (dynamically provisioned)
      2. SQL01-Programs.vhdx (dynamically provisioned)
      3. DVD drive (likely used later in the lifetime of this server)
    2. 2nd Controller: Add 2 VHDs
      1. SQL01-Data.vhdx (fixed size, 150% of your current data set)
      2. SQL01-Logs.vhdx (fixed size, 50% of your data set)
    3. 3rd Controller: add 2 VHDs
      1. SQL01-TEMPDB-DATA.vhdx (fixed size)
      2. SQL01-TEMPDB-LOGS.vhdx (fixed size, 25% of TEMPDB-DATA)
    4. 4th Controller: add 1 VHD
      1. SQL01-Backups.vhdx (fixed size, 5X data set or sufficient size to contain local backups to meet recovery point objectives)
    5. Review your disk layout and confirm the design matches your requirements.pbitpro-sql-hyperv-vm-figure-9
  3. Processor: At a minimum, use 4 virtual processors.
    1. In multi-socket servers, expand processors and click NUMA.
    2. Set the number of processors per NUMA node to match the number of cores per processor on the host (not including hyper-threading).pbitpro-sql-hyperv-vm-figure-10
  4. Boot your VM: Install Windows Server 2012 R2.

This blog will get you started on the right foot when building a new VM with requirements for both high IO and high number of disks. It is very important to get the VM built correctly from the start, as making these changes to a current VM can be risky. Windows may mount the disks with new numbers (e.g. Disk 3 is remounted as Disk 7) and then re-allocate drive letters if you try to do this after the VM is deployed. SQL Server is not a fan of having drive letters changed!! Ask me how I know that…

To contact a Hyper-V Ninja, email, or call 1(972) 987-0637

For more information on Microsoft Server 2012 R2 and SQL Server 2012/2014, please visit our blog or follow me at @pdgb.

Leave a Reply

%d bloggers like this: