When to install SQL Server on a VM (and when not to)

A few years ago, I had a rather lively discussion with a sys-admin about whether to install SQL Server on a VM or not.  His opinion was that everything should always be installed on VM Ware and there were no reasonable exceptions.  Naturally, I did not accept that position.  I’ve found that “Never” or “Always” are words that are used by people who don’t know any better but are afraid to admit it.

Before I continue, let me set the record straight.  Personally, I love VMs.  They have changed my life (at work) and they have resolved or reduced some of the biggest, scariest problems in IT.  Every server that I have now, runs on a VM.  I would never want to go back to bare metal, except when it comes to a production DB and then, I’d say “it depends”.

Since my original discussion with that sys-admin, I have sought the answer to the question: “When should/shouldn’t you VM a SQL server?”  I have studied several articles, bounced this question off-of dozens of DBAs (database admins) and even discussed this with a few MVPs from Microsoft.  In my career, I consider it my responsibility to make informed decisions and would encourage others to do the same.  Based on that, I would like to present a summary of the information that I have gathered.

Benefits of VMs

  • You can snapshot a VM, then do anything (including mad-science) on it.  When you are done, you can roll-back the snapshot and undo any/all of your evil.  It is the easiest backup/plan that you could ever have.
  • You can shut-down a VM and make a copy of the entire machine (HDD, RAM, devices) with little effort.  I’ve even copied a few to my thumb-drive.
  • You can move a VM to any other physical machine (host) very easily with little or no side effects by simply moving/copying the VHD (Virtual Hard Drive).
  • You can run several VMs (servers) on one set of hardware (host).  They don’t even have to be the same OS.
  • If you only need a server for a few days, just make a copy of a reliable template and rename it.  When you are done, just delete the VHD (virtual hard drive).
  • You can snapshot an entire, existing/running server and turn it into a VM in mere hours.

Drawbacks of VMs

  • Over-provisioning is SO EASY that it is nearly impossible to not get carried away with it.  Especially if you don’t have a bottomless IT budget.
  • Contention for resources can be a problem.
  • When you have multiple machines on a host, there are some issues that are difficult to isolate.
  • Not all failures/crashes/errors are obvious to a host and so auto-fail-over (VMotion) does not always save you.
  • Snapshots only help you if you are using them properly.
  • Snapshots can eat up lots of HDD.
  • If you run out of HDD, it will pause all of your VMs instantly.  This makes it seem like all of them crashed at once.
  • If your team only cares about your VM (hosted server), then any other VMs on the host, are competitors of yours.

When you look at what a VM does, and you really think about it, you will see that a database server is meant to do nearly the same thing, but only for data.

How/why SQL server is not just another server

  • You can snapshot a DB (or make a backup), then do anything (including mad-science) on it.  When you are done, you can roll-back the snapshot (or backup) and undo all of your evil.
  • You can shut-down a DB and make a copy of the thing with little effort.  I’ve even copied a few to my thumb-drive.
  • You can move a DB to any other physical machine very easily with little or no side effects.
  • You can run several DBs on one set of hardware.
  • If you need a DB for a few days, just make a copy of a reliable DB and rename it.  When you are done, just delete the (copied) DB.
  • You can import data from any source, into your DB server.

Did you notice the similarities between “Benfits of VMs” and “How/why SQL Server is not just another server”.  The reasons are nearly identical.  Think about that for a moment.

SQL server also behaves like a VM host in the following ways:

  • Each DB has its own security that can be managed separately from others
  • Each DB has its own file, which is managed the same way that an OS manages a partition or a VM host manages a VHD
  • SQL Server pre-allocates lots of memory from the host and manages its own memory
  • SQL Server can host a variety of other processes (apps) and communicates via various protocols
  • SQL Server is rarely treated as an app, but rather it is the host/back-end for other apps
  • SQL Server has its own backup mechanism (which many people consider to be more reliable than an OS backup)
  • You can detach and re-attach a DB between servers with a quick command and a file move
  • SQL Server contains a fail-over mechanism called clustering, that allows it to automatically fail-over to a different host, while using the same file(s) (on a shared HDD)

When you think of it in those terms, you begin to wonder what you really have to gain from virtualizing a database server.  Running SQL server in a VM is very much like running a VM in another VM.  Are there good reasons for a double-abstraction like that?

When/why you should host SQL on a VM

Here are a few scenarios where you get a distinct benefit from virtualizing a database (server)

  • Development environments often need temporary resources.  Quick set-up and quick tear-down can greatly increase ROI.
  • The very nature of development and testing implies abuse and destruction.  You can expect any dev machine (OS/software) to receive damage and need repairs (or reinstalls) regularly.
  • Dev and test environments are not very resource intensive.  If your dev/test is running slow, then the developers just need to wait longer.  Heck, they might even be compelled to tune things better.
  • Developers need to do lots of experiments that usually don’t require much of a budget.  VMs make it cheap to bring up a quick server wherever/whenever you need one.
  • Dev, Testing and QA are VERY temporary/seasonal environments and usually rotate along with dev cycles.  It makes sense to power each one on during appropriate phases of development/testing and off when that phase is complete.
  • It is best to perform QA/Testing on machines that are identical (or as close as possible) to Prod.  A VM is a great way to mimic/preview/simulate prod.
  • If you are renting space in a rack at a hosting/data center and each U costs you $100/month, you are probably better-off with a VM.  From a business perspective, it can be hard to justify the extra pizza-box.
  • If you are still playing “towers of Hanoi” with your servers/hw and are still in the process of consolidating heavily, a VM is (pretty-much) required. This is especially true if you intend to go through a volley of OS upgrades (etc.) after you are done consolidating.  When, everything is in-flux, and it is difficult to predict how things will look in 24 months, stick with a VM.

When/why you should not

Before I talk about when to go with bare metal, let me start by saying this:  If performance isn’t important to you, then don’t bother reading the rest of this. I’m serious.  In this next section, I’m only talking about genuine “1337” stuff, where performance really matters and an A- or B+ just won’t make the cut.  If excellence is your target and “good enough” isn’t going to be good enough for you, then this info will apply.

  • Lack of benefit – What do you gain from running a SQL Server on a VM instead of bare metal?  Compare your answer to the list of ways that SQL server is already like a VM.  You might NOT be left with a strong reason to use a VM.
  • Right-sizing – For most companies, you want your prod DB to be as strong as you can afford.  If you grossly over-buy, then it makes sense to go with a VM. You have extra CPU ticks to burn. So, go ahead.  However, if you bought exactly what you need, then then turning it into a VM is simply robbing 5-10% performance from your SQL server.  For what?
  • Contention avoidance – If you run more than one VM (hosted server) on the same host along with your SQL Server, then you will experience contention.  If your SQL server is running peacefully and responds quickly, then you have no worries.  However, if you ever find yourself wishing that you had a little more juice for your DB, then you should already be blaming yourself for not having the foresight to expect this.
  • Option to up-size – This might sound familiar: “Right now, we will buy twice the box and run two VMs on it.  When we outgrow this machine, we can buy a second machine and split them up”.  I call this “Spinal Tap” logic. You are really buying a server that “goes to eleven” instead of getting a ten that is a little louder.  The primary flaw in this argument is that you are assuming that when you are ready to upgrade, you will still be satisfied with the old machine.  In reality, when it is time to upgrade, you will probably find a newer machine with twice the horsepower for half of the price and you will want to chuck the old one.  It won’t be cost-effective to repurpose the old one.  You would have saved money and gotten more performance by going smaller initially and buying an upgrade sooner.
  • Consistency – Some admins get out-of-hand with trying to be consistent.  A SQL Server is not just another server.  It should not be configured or maintained like all of the other servers.  This is a common mistake.  So, start by running it on bare metal.  In your mind, it should be clear that this DB server is not just another machine and it should always be configured, handled and managed differently.
  • Excellence – A VM admin will have a server template (maybe two) and all new servers get built from this template(s).  This results in a “one size fits all” mentality.  Most of the time, this will get you a solid B+ on your report card, but for SQL server, it will only get you a B-.  For some people, a B- is good enough.  Not me.  I care about my GPA more than that.  If you want an A, your SQL Server should be on bare-metal.  You provide more value to your employer by knowing how to build and configure a SQL server in the best way possible.
  • Availability – Yes, I know VMware has V-Motion.  If the hardware under your server dies, VMotion will magically switch it over to the other host and you will never know the difference.  Nothing in the world is capable of beating this, eh?  How about if the OS fails and VMotion doesn’t detect it?  If it does, then is VMotion going to move your dead OS to a different VM host and it will magically be not-dead?  How about if SQL Server itself crashes or hangs?  Will VMotion fix that?  No.  There are some things that VMotion does not insulate you against.  This is why SQL 2012 comes with 4 different fail-over mechanisms (each one is a completely outside-the-box strategy).  VMotion is great for hardware, but SQL Server clustering and DR fail-overs go completely beyond anything that a VM host could ever offer.

VMs are nice for experimenting and learning, but when you are ready for the big leagues, leave the whiffle ball and bat behind.  Reach for the classic hickory from Louisville.  Of course, you need more talent to wield that power, but that is why you get the big bucks.

Advertisements

About Tim Golisch

I'm a geek. I do geeky things.
This entry was posted in Architecture, Computer and Internet, Optimization. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s