After some dealings with hosting SQL Server on VMWare a while back I thought I’d share a couple you can do to make your life easier.
First thing to do is make sure you use the newest Operating System you can for your virtual machine. Second is have the right license key for SQL Server to install the core edition and not the CAL one.
Third thing, and this is by far the most important, is to make sure you read the official VMWare documentation.
You might think this option is obvious, however VMWare’s recommendations have changed a lot over the years. Plus, other people still work based on their own assumptions.
What’s the worse that can happen you might be asking yourself. Well let me explain in this fictional scenario which is based on extract from a session I discuss here.
Worse case scenario
You’ve just been asked to look to improve the performance of a virtualized SQL Server. It’s due to be upgraded but it’s currently running SQL Server 2012. On top of this it’s running Windows Server 2008 R2 because somebody decided they did not like the 2012 codebase years ago.
CPU Configuration
You start looking and you notice that the CPU configuration looks unusual. The vSocket to vCore ratio looks unusual. It seems to have 7 vSockets and 3 vCores. So you get your server team to change it to resemble a common server CPU configuration in VMWare and extend it to 24 cores.
Ratio
However, shortly after the server restart you notice is performing badly. You look again, and you notice that the server now appears to be only running 8 cores instead of 24.
You ask if you can look at the config in VMWare and you notice that the ratio was set incorrectly. Instead of being set to 2 vSockets with 12 vCores in each one, 24 vSockets with 1 vCore in each were created.
Question
Still that begs the question, why was the server only showing 8 cores? Well after investigation you discover that was due to a limitation of the old Windows Server 2008 R2 Operating System.
So, you work closely with the Windows Server team to change the Virtual machine to have 2 vSockets with 12 vCores in each one.
Once this is done the server sees 24 cores and you’re very happy. You then query the dmv in SQL Server to check all 24 cores are being used. That’s when you get a very nasty surprise.
CAL Edition
To your dismay only 20 cores appear to be used. You check the SQL error log to see what’s going on. There you discover that the wrong license key was used and you’re running the CAL version of SQL Server 2012 which has a limit of 20 cores. Luckily you can correct this by running the setup command again and stating the right license key.
Host configuration
Finally, you get the server to recognize all the cores. However, you still think you can improve the performance a bit more. You discover that the VM hosts used for the environment this SQL Server runs on uses 16 cores and not the 24 that the virtual machine currently uses.
As an experiment you throttle back the cores and get them configured the same as the physical processors on the host. After you have done this you notice a performance improvement in certain areas.
Final word
As you can see in this worse case scenario, it pays to be prepared if hosting SQL Server on VMWare.
Make sure you’re using the license key for SQL Server. More importantly make sure you read the latest VMWare guidelines here.
[…] Hosting SQL Server on VMWare […]