SQL Server virtualization pros and cons: Weigh the performance impact

VIEW FEEDBACK TO THIS TIP

 With Microsoft SQL Server, you can create virtual machines using VMware or Hyper-V, installing Windows on each virtual server and installing SQL Server on each virtual machine. Another option is to install separate instances of SQL Server on a single physical -- or virtual -- server. Both methods have their upsides and downsides -- and sometimes systems shouldn't use either method because they simply aren't a good candidate for virtualization.

Independent virtual machines

Setting up independent virtual machines is probably the easiest way to virtualize SQL Server systems. Each machine, which would have normally been a physical machine, gets its own virtual machine and you manage everything the same. The upside to this is that memory management is the same as it is in the physical server world. You set up each virtual machine with the correct amount of memory and you are good to go. Because each machine is allocated its own amount of RAM, the amount of cache each database has access to is known, which allows you to make better judgments as to how much RAM to allocate to the server.

There are some downsides to this kind of virtualization method. The largest of the downsides is the hard drive configuration. For the most part when you set up virtual servers, you'll use the native virtual storage technique that comes with the virtual server software. This means that the hard drive that the virtual machine sees is actually a single file stored on the host machine's hard drive. The host's hard drive can be a SAN (storage area network), NAS (network-attached storage) or DAS (direct-attached storage) depending on your solution options and setup configuration.

Because you are now sharing storage with the other virtual servers hosted by the host machine, you will be at the mercy of these other machines -- just as they will be at the mercy of your SQL Server -- when accessing the hard drive. If another machine starts performing a high

More on SQL performance and virtualization: PerfMon counters for Windows operating system (OS) Virtual database storage for SQL Server: Friend or foe What are the best practices for using virtualization such as VMWare for a SQL Server cluster?

 I/O operation, such as a defrag operation, then your SQL Server will feel this impact as well. All isn't lost, as some virtualization solutions give you the option of presenting a LUN for physical DAS array directly to the guest OS. Basically, it bypasses the host OS and takes this layer out of the picture, allowing you to control the performance of the guest operating system's hard drives.

There is an upside to this kind of virtualization as well. When you use a VMware or Hyper-V virtualization server to host your SQL Servers and you need to increase the amount of storage the virtual machine has access to, it's easy to set up. You simply shut down the guest OS, run a command on the host OS -- or use the UI to make the change depending on the solution -- then start the guest OS and use diskpart on the guest OS to extend the hard drive.

Using instances for virtualization

In the SQL Server world, we've been able to virtualize since SQL Server 2000 by using SQL Server's ability to install multiple instances on the same server. This gives you the separation of instances but doesn't require running more than one OS, and it saves a large amount of RAM if you are consolidating several machines. If you install eight instances of SQL Server on a single machine, you only need to run a single Windows 2003 or 2008 OS. Yet, if you put each instance into its own guest OS on a server virtualization system, you have to install eight Windows 2003 or Windows 2008 operating systems.

Another upside to installing several instances on a single server over installing multiple virtual machines is that you can retain better control of the disk I/O. Because it's typically going to be a physical server running all these instances, you can assign each instance the correct number of drives from your SAN or DAS solution that the server needs.

The big downside to using several instances on a single server is that the memory starts to become much harder to manage as you now have to balance the memory requirements of all the instances within the RAM allocation of the physical server. You can easily over subscribe the memory and end up with Windows not having enough memory for itself and the other software running on the server. And that becomes more problematic when you start backing up your server to a network share or when your backup software copies the backup files from the server to the backup server.

Doing either of those operations on large files requires a large amount of system cache -- sometimes several gigs depending on the size of the database backup files. If you don't allocate enough RAM for Windows to do that, then Windows will have to take the memory from the SQL Servers, which can impact database performance.

Conclusion

If you can work within these limitations, then your server is probably a good candidate for virtualization. In most cases, the limitations mean that larger, busier SQL Servers aren't good candidates for virtualization. Most companies have smaller systems that do not have much of a CPU load or memory requirement and can easily virtualize SQL Servers using one of these methods. It's up to you, the DBA, and your coworkers to weigh the SQL Server virtualization pros and cons and decide which solution is correct for your specific environment.

ABOUT THE AUTHOR

Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

Check out his blog: SQL Server with Mr. Denny.

MEMBER FEEDBACK TO THIS TIP

Do you have a comment on this tip? Let us know.

We recently looked at virtualizing some of our SQL Server instances internally; one of the downsides we came away with is that CPU usage can become a limiting factor. For example, in both VMWare ESXi and Microsoft's Hyper-V, there are limitations to no more than four virtual CPUs. Even if your system is able to oversubscribe the virtual CPU utilization, you still have to take this into account if you are CPU bound in any way.
—Andre H.

******************************************

I have one minor correction regarding virtual licensing. With Windows Server 2003 Enterprise Edition, you can have up to four virtual machines using the same license. With SQL Server 2005 Enterprise Edition, you can install unlimited copies on virtual machines within a single physical server..
—Hugh T.

******************************************

This article was interesting, but it failed to highlight a key difference between virtualizing and using multiple instances: the license implications. For more information, consult these SQL Server licensing considerations.
—Murray C.