This past weekend I asked the community(or at least my twitter feed) what they thought about virtualized database servers. Lots of people use it in Dev\Test\QA. Some in prod. Some thought it was pointless. Before I go over my lessons learned, let me address the “pointless” point.
It could be argued that it adds another unneeded level since you could do this with instances. For that matter, you can use one instance and permissions. This is true. If you are consolidating just a few instances, the cost and overhead probably doesn’t justify virtualization. However, there are other benefits like VMotion or adding capacity horizontally. You can’t easily move two instances off an over utilized box. With ESX, you can do this online while transactions are in flight. Added redundancy is also a benefit.
So here are my lessons learned:
Beware of CPU bound workloads
Most database workloads are IO bound even if it is logical IO’s. However, if your database is really small but you do lot of complex calculations, lots of business logic or string manipulations etc, your performance may suffer more than you would expect.
Use x64
This is just a vendor recommendation that suggests a 10% gain.
Don’t trust the CPU counters
Another thing I learned from the whitepaper. The CPU ready counter in Virtual Center is very useful.
Set a memory reservation
I would at least reserve half of the memory given to the guest especially if the host is busy. ESX has a balloon driver that will take memory from guests if it needs it and it thinks the guest is idle. This usually is not good for database servers.
The Microsoft Windows Server 2003 Scalable Networking Pack is evil
This is enabled by default with Windows Server 2003 sp2 and it doesn’t play well with SQL Server, VM’s and especially SQL Server on VM’s. See this post.
You have to sector align TWICE
First you have to sector align the vmfs and then at the OS level. Here is VMWare recommendations. I agree with Kendal’s recent finding of 128k offsets and 64k NT allocation unit based on my own testing.
If you have a lot of SQL VM’s on a host, see if EE makes sense
With Enterprise Edition, you only have to license the host. If you use standard edition, you must license each guest. If no EE features are needed, “lock pages in memory” alone, may be enough. In addition to other problems it solves, you can enable large page support.
Bad code usually runs worse on a VM
Man, the weather has been nice. How about them cowboys!
Anyway, do you have any lessons learned running SQL on VMWare..
posted @ Thursday, February 19, 2009 8:27 AM by bofe
posted @ Thursday, February 19, 2009 8:31 AM by JasonMassie
posted @ Thursday, February 19, 2009 9:23 AM by cosmin
posted @ Friday, April 30, 2010 4:40 AM by SQL Server Storage Engine & Tools (SSQA.net)
Name (required)
Email (required)
Website
Notify me of followup comments via e-mail