Virtualized SQL Server: Why not?

Question

The IT department where I work is trying to move to 100% virtualized servers, with all the data stored on a SAN. They haven't done it yet, but the plan eventually calls for moving the existing physical SQL Server machines to virtual servers as well.

A few months ago I attended the Heroes Happen Here launch event, and in one of the SQL Server sessions the speaker mentioned in passing that this is not a good idea for production systems.

So I'm looking for a few things:

1. What are the specific reasons why this is or is not a good idea? I need references, or don't bother responding. I could come up with a vague "I/O bound" response on my own via google.
2. The HHH speaker recollection alone probably won't convince our IT department to change their minds. Can anyone point me directly to something more authoritative? And by "directly", I mean something more specific than just a vague Books OnLine comment. Please narrow it down a little.

Answer 1

There is some information concerning this in Conor Cunningham's blog article Database Virtualization - The Dirty Little Secret Nobody is Talking About.... To quote:

Within the server itself, there is suprisingly little knowledge of a lot of things in this area that are important to performance. SQL Server's core engine assumes things like:

1. all CPUs are equally powerful
2. all CPUs process instructions at about the same rate.
3. a flush to disk should probably happen in a bounded amount of time.

And the post goes on elaborating these issues somewhat further also. I think a good read considering the scarcity of available information considering this issue in general.

Answer 2

The biggest concern to me when virtualising software is normally licensing.

Here's an article on it for MS SQL. Not sure about your situation so can't pick out any salient points.

http://www.microsoft.com/sql/howtobuy/virtualization.mspx

Answer 3

Old Question with Old Answers

The answers in this thread are years old. Most of the negative points in this entire thread are technically still correct but much less relevant. The overhead cost of virtualization and SAN’s is much less a factor now than it used to be. A correctly configured Virtualization host, guest, network, and SAN can provide good performance with the benefits of virtualization and operational flexibility including good recovery scenarios that are only provided by being virtual.

However, in the real world it only takes one minor configuration detail to bring the whole thing to its knees. In practice your biggest challenge with virtual SQL servers is convincing and working with the people responsible for the virtualization to get it set up just right.

Irony, in 100 percent of the cases where we took production off of the virtualization and moved it back to dedicated hardware performance went through the roof on the dedicated hardware. In all of these cases it was not the virtualization but the way it was setup. By going back to dedicated hardware we actually proved that the virtualization would have been a much better use of resources by factors of 5 or more. Modern software is usually designed to scale out across nodes so virtualization works to your advantage on that front as well.

Answer 4

Note there are some specialty virtualization products out there that are made for databases that might be worth looking into instead of a general product like VMWare.

Our company (over 200 SQL servers) is currently in the process of deploying HP Polyserve on some of our servers:

HP PolyServe Software for Microsoft SQL Server enables multiple Microsoft SQL Server instances to be consolidated onto substantially fewer servers and centralized SAN storage. HP PolyServe's unique "shared data" architecture delivers enterprise class availability and virtualization-like flexibility in a utility platform.

Our primary reason for deploying it is to make hardware replacement easier: add the new box to the "matrix", shuffle around where each SQL instance resides (seamlessly), then remove the old box. Transparent to the application teams, because the SQL instance names don't change.

Answer 5

SQL Server is supported in a virtual environment. In fact I would recommend it seeing that one of the licensing options is per socket. This means you can put as many SQL Server instances in a virtualized (e.g. Windows 2008 Server Datacenter) system as you like and pay only per processor socket the machine has.

It's better than that because DataCenter is licensed per socket with unlimited Virtual machine licenses as well.

I'd recommend clustering your Hyper-V on two machines however so that if one fails the other can pick up the slack.

Answer 6

I would think that the possibility of something bad happening to the data would be too great.

As a dead simple example, let's say you ran a SQL Server box in Virtual Server 2005 R2 and undo disks were turned on (so, the main "disk" file stays the same and all changes are made to a separate file which can be purged or merged later). Then something happens (usually, you run into the 128GB limit or whatever the size is) and some middle of the night clueless admin has to reboot and figures out he can't do so until he removes the undo disks. You're screwed - even if he keeps the undo disk files for later analysis the possibilities of merging the data together is pretty slim.

So echoing the other posts in this thread - for development it's great but for production it's not a good idea. Your code can be rebuilt and redeployed (that's another thing, VM's for source control aren't a good idea either) but your live production data is way more important.