Home / server / Questions / 25840
Accepted
SQLRockstar
Asked: 2009-06-16 05:13:59 +0800 CST

Virtual vs. Named SQL Instances

  • 772

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

virtualization sql-server

1 Answers

  1. Best Answer

    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.

    • 11