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?
Answer by K. Brian Kelley ·
Answer by Steve Jones - Editor ·
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.
Answer by Mark Allison ·
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:
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.
Answer by Håkan Winther ·
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.