Server 4024 part 1 – Virtualisation

  • Article

A database is only as good as the operating system it resides on. SQL Server only runs on Windows and given that Windows Server 2012 (WS2012) is a huge change from what there was in Server 2008 R2 what does this new OS mean for the DBA.  The reference to Server 4012 is because that would be WS2012 + SQL 2012 which is what you want be running to get the most out of your modern hardware and push that through to your database engine.

In this particular post I wanted to look at what Hyper-V in Windows Server 2012  does for SQL Server.

It used to be that SQL Server was limited by what the operating system could surface but in a virtual world this becomes what the hypervisor provides to the virtual machine (VM). In WS 2012 all of the limits for VM’s have gone up by at least 4X..

image

 

In many cases these limits are higher than the specifications of many physical servers that you are running SQL Server on today.  There’s also lots of use made of the latest developments in hardware that you may not have on your servers yet, for example:

  • NUMA support , so you can pass through NUMA nodes to a VM for optimal VM performance or allow your VMs to span NUMA nodes..
  • SR-IOV is away of making a PCI card look like multiple PCI devices each of which can be presented to a particular virtual machine.  In WS2012 there is SR-IOV support for network cards (NICs) so that the virtualisation is handled by the card not the hypervisor.  The clever bit that’s currently unique to WS 2012 is that you can live migrate a VM using SR-IOV to server that doesn’t have it , i.e. you don’t have to stop the VM.
  • IPsec can also be offloaded to NICs that support it
  • Support for 4 x Fibre Channel HBAs in a VM.  Note this doesn’t prevent live migrations either but the setup of the Virtual SAN must be the same on the source and target hosts.

While I am on about hardware, Hyper-V also introduces a new hard disk format, VHDX which can be up to 64TB (Note the VHD format is still supported) which also allows you to efficiently use the 4KB sector size on the newer larger hard disks by having a logical sector size of 4KB as well.

On a physical server running lots of VMs you’ll want to ensure that SQL Server gets a predictable set of resources, such as CPU, RAM.  Hyper-V has always allowed you to set and prioritise all these, and in WS2012 you can also set Network bandwidth maximum and minimum for each Virtual NIC.

In the past SQL Server hasn’t been virtualised because of concerns about performance. As I have shown that doesn’t really apply to WS2012, but you will need to follow best practice for setting it up and confirm that you are getting the performance you are expecting.  The best practice is on the SQL CAT (Customer Advisory Team) blog, and your testing should show that you are getting about 93% of the performance you had on your equivalent physical hardware.

So please  ensure your SQL Server is given a good home and try it on Windows Server 2012

 

note: I’ll be discussing this during my sessions at SQL Relay (in Glasgow, Leeds, Birmingham & Norwich)