Views from the Field: Expert Advice on Virtualizing SQL Server

When it comes to virtualizing high-transaction and high I/O SQL Server installations, Allen encourages DBAs to evaluate them on a case-by-case basis. "Virtualization adds overhead with respect to memory, CPU, network and disk I/O operations and there are inherent memory and CPU limitations for VMs," Allen says. "High-transaction applications need to be tested to ensure they meet their operational SLAs in virtualized production environments. For applications that require native environment, multi-instance consolidation could be one of the solutions. SQL Server 2008 support for hardware hot-plug (Memory/CPU) is a very useful feature for such scenarios."

For more information about using SQL Server with Hyper-V, Allen suggests reading the Microsoft article "Running SQL Server 2008 in a Hyper-V Environment," which provides several tips and suggestions for virtualizing SQL Server using Hyper-V, including recommending the use of emulated devices for SQL Server deployments and configuring pass-through disks in the guest VM root partition as offline using the Hyper-V Volume Manager or the DiskPart utility.

Allen points out that although both SQL Server 2008 and SQL Server 2005 can be used in virtual environments, SQL Server 2008 brings some extra virtualization-friendly features to the table. "Resource Governor provides isolation and resource optimization amongst instances within a VM," says Allen. "Data compression ensures disk space optimization, allowing the database instance and the VM to better leverage disk space and make better use of I/O bandwidth, therefore extending the possibility of running more VMs on the same server."

Ozar agrees that SQL Server 2008 offers some virtualization-friendly features and singles out the Resource Governor as his favorite. "[Resource Governor] allows DBAs to set up performance ceilings for any given application or database on that server. After consolidating lots of applications onto less hardware, the Resource Governor ensures that one poorly behaved application doesn’t bring every other consolidated application to its knees," says Ozar. "It’s the SQL Server equivalent of VMware resource pools: We can define exactly how much resources a given application gets. Even better, the Resource Governor can allow an application to get all the horsepower available if nothing else is active at that particular time, too."

If you opt for running SQL Server with VMware’s ESX Server or Virtual Infrastructure 3 (VI3), many of the same planning and scheduling tips apply. According to Scott Drummonds, VMware's group manager of technical marketing, many of the misconceptions people have about virtualizing SQL Server have less to do with the technology and more to do with faulty planning and implementation. "In some cases, the VI3 admin may be a different person than the DBA and there may also be a storage admin," says Drummonds. "All of these people need to work closely together, mainly to ensure that you don't have a situation where there are 10-15 databases sharing the same LUN [Logical Unit Number]. You can get virtualization performance up to and higher than 90 percent if the environment is properly configured." (See “SQL Server Performance in a VMware Infrastructure 3 Environment” for more details.)

Obviously, the best virtualization tips and advice will come from someone who uses SQL Server in a live production environment, which is exactly what Tom Gibaud, manager of IT for Rochester General Health Systems (www.viahealth.org), does on a daily basis. "We've been using VMware for about three years … we started with the low-hanging fruit of server consolidation and virtualizing our file/print servers and Active Directory," says Gibaud. "We've been virtualizing SQL Server with VI3 for some time, and we've had great success with it."

Gibaud said that all but one of his organization’s SQL Server databases are virtualized, including those that handle electronic medical records, payroll, dictation systems, the blood bank system, radiology imaging, and all of its CRM applications. More than 400 guests inside of 60 SQL Server instances have been virtualized.

Gibaud stresses that reliability is a vital part of his virtualization strategy. "We also looked at using clustering with SQL Server, but—for me, at least—it caused more issues than it solved," says Gibaud. "We looked at VMware, and the reliability and high availability options were very important for us. Our top priority is patient care, so we need to make sure that nurses and doctors can get the information they need as soon as possible at any hour of the day."

When asked what advice he would give other DBAs and IT pros when it comes to planning their SQL Server virtualization strategy, Gibaud suggests you shouldn't mix workloads in your VMs, which makes it easier to manage them. He also stresses the importance of proper planning and running VMs on the best hardware available. "In talking to some colleagues that have been unsuccessful [virtualizing SQL Server], they've done things like mix their file and print servers with SQL Server in the same VM, or they didn't have enough iron underneath VMware [VI3] to handle the workload." Gibaud says that the time spent properly planning and configuring your storage options can also pay huge dividends. "You need to lay things out properly on disk. We make sure that our transaction logs are on raid 1, databases are on raid 5 … You need to follow best practices and set up your SQL Server instance with the same care you do when installing to bare metal."

In his presentation "Virtualizing SQL Server using VMware Infrastructure" at VMworld 2008, Hemant Gaidhani, technical product marketing manager at VMware, provided some additional SQL Server virtualization tips. Gaidhani recommends placing the OS application on separate spindles than the TempDB, presizing your data files, and managing your file growth manually—not by using the SQL Server Autogrow feature. Gaidhani also encourages DBAs to move their TempDB files to a dedicated LUN, make sure that all TempDB files are roughly the same size, and preallocate TempDB with enough space to handle expected workloads.

Contrary to what some DBAs believe, virtualization and SQL Server can work together in harmony. Armed with a bit of research and some best practices, you can leverage virtualization to maximize your investment in your SQL Server infrastructure. In these trying economic times, that might be the best practice to follow.