What are the benefits to using virtual as opposed to running multiple instances of SQL on a box?


If you break out your SQL Servers into individual virtual machines on the same host, the advantages are:

Better resource limiting. SQL 2008's Resource Governor is a good start, but still not that fine-grained, especially when it comes to limiting IO. With virtual servers, you can throttle CPU, memory and IO at the virtual machine level, thereby giving you the ability to limit resources even on older versions of SQL Server.

Easier performance upgrades and downgrades. If one virtual machine needs to be scaled up, like if its application suddenly becomes more popular, you can VMotion it to a more powerful machine without taking an outage. If you're using multiple instances, on the other hand, you're looking at a time-intensive and labor-intensive installation.

More flexible outage windows - if you have all of your databases on a single OS (multiple instances of SQL) then you have to do a lot of coordination to do Windows patches. If they're broken out onto different virtual guests, then you can do patching whenever it's most convenient for each individual guest (and its matching databases.)

Better security limits. If one SQL Server runs into problems and a third-party needs to get involved with troubleshooting, you can give them OS-level permissions without worrying about what they'll do to the other SQL Servers installed on the box.

Less problems with application compatibility. Some apps just aren't compatible with named instances of SQL Server.

It's not all unicorns and rainbows, though. Some drawbacks of the virtual server approach include:

  • Possibly more expensive - you have to pay for the virtualization layer, and depending on how some of your software is licensed, you may have to pay differently for it. Some utilities are licensed by the number of CPUs on the host machines, and not all of the CPUs may be allocated to SQL Server.
  • Possibly tougher troubleshooting - some vendors like to point fingers at the virtualization layer.
  • More OS management - every OS you add means more management and maintenance.

I did a webcast on consolidation vs virtualization with SQL Server experts Kevin Kline and Ron Talmage. Registration is required for that, though.

  • i can set the affinity for each named instance, as well as set the memory. is there any benefit to using VM in doing so? – SQLRockstar Jun 15 '09 at 13:29
  • 1
    Yeah, with VMware you don't have to pick the individual CPU. You can say, "Throttle this virtual machine to 50% of two CPU cores", and you don't have to micromanage which cores they are. VMware just picks what's available at the time you need 'em. This eliminates problems with trying to manage cores individually, which becomes really painful with active-active clusters and failovers. – Brent Ozar Jun 15 '09 at 13:32
  • Thanks! I love it when I can say, "I've done a presentation on that!" Hahaha. – Brent Ozar Jun 15 '09 at 14:22
  • Makes me want to go virtual. – Sam Jun 15 '09 at 23:58
  • Yeah, I'm totally sold on virtual servers now. I'm heading up the Professional Association for SQL Servers chapter for Virtualization, too, because I really believe in it. Solves a ton of problems. – Brent Ozar Jun 16 '09 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.