There are many design considerations that can dictate how to build your virtual machines (VMs), but a major consideration is storage. This post will focus on using virtual hard disks (VHDs) stored locally on the hypervisor—either through direct attached disks, shared storage, or a 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. Often times, the case for this design was due to the 2 TB VHD limit imposed by all major hypervisor vendors. With Hyper-V 2012 and VMWare 5.5, this limit has been increased to 64 TB!
Microsoft SQL Server can demand high input/output (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 if you want to maintain a high level of performance, unless it is an all-flash array. 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 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.
2. Specify Name and Location: Choose a name for the new VM and choose where to store the VM if desired.
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.
4. Assign Memory: Choose how much memory to assign the VM and do not choose Dynamic Memory. 4 GB 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.
5. Configure Networking: Choose the proper network switch.
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.
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.
7. Summary: Verify the settings you chose in the summary and click Finish to create the VM.
MORE ADVANCED SETTINGS OF THE VM
8. From the middle pane in Hyper-V Manager, click on the VM you just created and clickSettings… on the lower right.
9. 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.
a) First Controller: Add two VHDs and one DVD Drive
- SQL01-Boot.vhdx (dynamically provisioned)
- SQL01-Programs.vhdx (dynamically provisioned)
- DVD drive (likely used later in the lifetime of this server)
b) Second Controller: Add two VHDs
- SQL01-Data.vhdx (fixed size, 150% of your current data set
- SQL01-Logs.vhdx (fixed size, 50% of your data set)
c) Third Controller: add two VHDs
- SQL01-TEMPDB-DATA.vhdx (fixed size)
- SQL01-TEMPDB-LOGS.vhdx (fixed size, 25% of TEMPDB-DATA)
d) Fourth Controller: add one VHD
- SQL01-Backups.vhdx (fixed size, 5X data set or sufficient size to contain local backups to meet recovery point objectives)
e) Review your disk layout and confirm the design matches your requirements.
10. Processor: At a minimum, use four virtual processors.
a) In multi-socket servers, expand processors and click NUMA.
b) Set the number of processors per NUMA node to match the number of cores per processor on the host (not including hyper-threading).
11. Boot your VM: Install Windows Server 2012 R2.
This post 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. SQL Server is not a fan of having drive letters changed!! Ask me how I know that…
To contact a Hyper-V Ninja, email info@credera.com, or call 972.759.1836. For more information on Microsoft Server 2012 R2 and SQL Server 2012/2014, please visit our blog or follow us at @CrederaMSFT and @CrederaIT.