What are the performance / manageability implications of running SQL Server on virtualised hardware vs directly on an operating system? Are there any ways of mitigating the downsides in both cases?
asked
What are the pros and cons of running SQL Server on a virtualised platform vs bare metal?
Pros
- Better use of memory / CPU. In other words, you don't have a bunch of CPUs running at 20% utilization any more.
- High availability because of the hypervisor, not because of clustering. For instance, ESX Server's ability to automatically move VMs when there's a problem.
- Easier to scale up. Since you're talking VMs, you can move the VM to bigger hardware if there's a need for more resources. In an ESX environment, the hypervisors can watch load and move VMs around to maximumize performance.
- Easier to scale down. Same reasons as the point above.
- Can be easier to recover at DR. I say can be. The reason is you can basically snapshot the VM and restart it at the DR site intact. You're not in the position to reload the OS, reload SQL Server, etc.
Cons:
- Very heavily loaded VMs have seen disk I/O issues, even with dedicated I/O paths, etc. Sometimes you need the physical hardware without interference.
- Overallocation of a virtual host. If you have too much running on a host, it's not just I/O that could be impacted. It could be memory and CPU bottleneck issues on the physical host which impairs the SQL Server.
- Harder to protect. Something that doesn't get a lot of press... But if it's easier for me at DR because I can snapshot the VM and copy it to somewhere, that means it's easier to take the system intact because I'm not longer having to breach physical security to get the system.
Pros:
- More efficient use of resources, you can put lightly loaded SQL Servers on big hardware (consolidate) and let the hypervisor manage resources.
Cons
- I/O issues. Most hypervisors seem to try and bundle I/O where possible and this can cause issues with performance. For real-time writes for a system like SQL Server, it means that you possibly could have data not written to disk that SQL Server thinks has been.
- Immature administrators - The technology is still new and relatively few people understand it well. They think that a VM is a free machine and is the same size as a physical of similar specs. It's not. It could be, but those resources get balanced, and the CPU still has to switch between VMs.
- Tendency to overload the physical - Often because managements wants to save $$
A VM other than hardware sharing/scheduling, is no different than having multiple physical boxes with separate SQL Servers. So any advantages/disadvantages of those will apply to VMs.
I like the idea of a db server on a VM, mostly because of recovery. I don't have any strange hardware drivers to worry about. And if I need to "upgrade" my physical hardware, this is much easier. not that it's hard with SQL Server, but it's easier and smoother to move a VM. Clustering is also simpler, can use disparate physical hardware.
The biggest thing that I've heard from friends with larger VMs for SQL Server is that you need dedicated I/O paths, preferably to a SAN, for the SQL Server VM. You also want to more lightly load your physical boxes, 1-4 VMs as opposed to 5-10.
I agree with previous posts, but I don't like the idea of moving large databases (more than 300Gb) under heavy load to a VM. The concept with VM is to share the resources between the the virtual instances and in some cases that is what you want to avoid.
It is very popular to move to VM and in the hype "everything" is consolidated into virtual machines and sometimes it is done without thougth of the consequences.
I use both models. We use SQL Server clustering for production servers because we want full performance with the high availability. We did consider using VMs for HA but we were not convinced that the VMware infrastructure could provide the performance we require in our production environment.
Having said that we do use VMs for Dev, integration testing environments to maximise the use of the hardware. You should consider that the ceilings for server resources on VMs are lower than on physical hardware. I.e. you cannot allocate 16 CPUs to a VM, and there's a limit on physical memory also.
For me, the Pros are:
- Rapid provisioning - new servers can be deployed very quickly
- Vmotion - spectacularly good high availability technology. However there are some drawbacks when used on over-committed hardware. Careful attention must be paid to this.
- Reduced costs through full use of hardware
- Easy management through the VMware infrastructure tools
- Reduced power consumption in the data center
Cons:
- Poor performance with heavy I/O
- Resources are limited by the VMware technology and not the hardware. See this document for more information on that. For example you can only assign a max of 4 vCPUs - but we found that performance went up in some situations when the number was reduced down to 1
- This immature technology requires extensive testing before it could be deployed to mission-critical production applications with confidence
Another solution we considered was to have one VM per ESX host to get the great high availability technology with near native performance. However we just weren't confident enough to put mission-critical stuff on it. I'm sure over time this will change and configurations like this may become popular.
