x

What are the pros and cons of running SQL Server on a virtualised platform vs bare metal?

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?

more ▼

asked Oct 09, 2009 at 08:15 AM in Default

Neil Davidson gravatar image

Neil Davidson
92 2 2 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered Oct 09, 2009 at 11:50 AM

K. Brian Kelley gravatar image

K. Brian Kelley
933 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 09, 2009 at 11:42 AM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 06, 2009 at 07:22 AM

Mark Allison gravatar image

Mark Allison
479 1 1 4

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 06, 2009 at 06:45 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x249
x15
x4

asked: Oct 09, 2009 at 08:15 AM

Seen: 10150 times

Last Updated: Oct 09, 2009 at 08:15 AM