Views from the Field: Expert Advice on Virtualizing SQL Server

Virtualization has taken the computing world by storm over the past few years and can help DBAs and IT pros get the most out of their existing hardware resources. It’s well known that virtualization has tangible cost and performance benefits, including server consolidation, power consumption reduction, and the painless creation of virtual development and test environments. Just about everyone in IT is using virtualization to some degree, but many DBAs have concerns about running SQL Server in virtual environments.

Virtualization has emerged as a valuable tool to help DBAs manage their database environments more effectively. That said, there's a fair amount of half-truths and misconceptions about using SQL Server with virtualization, such the admonition to never run a high-transaction SQL Server database in a virtual machine (VM) or that running a SQL Server database in a VM always results in sluggish performance. (See “Virtualization Myths and Misconceptions" for more information about this topic.) "We DBAs are conditioned to demand the fastest of everything—raid 10 drives, all the memory we can cram in the box, enterprise edition features, etc," says Brent Ozar, a SQL Server domain expert at Quest Software. "In an ideal world, we’d never put databases on virtual servers. But in an ideal world, all of our companies would be massively profitable and we’d be awash with help from skilled junior DBAs at our beck and call. This is not that ideal world, and there are cases where high-transaction SQL Server databases will function fine in virtual environments because that’s what the business needs dictate." Although virtualization does have some drawbacks, it can be used successfully in many SQL Server environments if you plan your virtualization strategy carefully.

Creating a SQL Server Virtualization Strategy

Because every SQL Server installation is different, Lindsey Allen, principal program manager lead on the SQL Server team at Microsoft, suggests that you first assess whether virtualization would be ideal in your environment. "[You] need to identify the goals the organization wants to achieve via virtualization. Is it cost reduction through consolidation, higher availability, or improved manageability? Then you should identify the workloads running on the SQL Server instance to be virtualized, and test to ensure that they meet operational SLAs in a virtualized environment," says Allen. Based on that information, you should be able to plan, design, and implement a new virtualization environment.

"Think of virtualization like facial tissue—soft and disposable. You don’t want to put your production systems onto virtualization without a lot of need driving it and without a lot testing to ensure it will work," says Kevin Kline, director of technology for Quest Software’s SQL Server Solutions Group. "Instead, start with the low hanging fruit of database applications—development environments, QA environments that don’t do performance testing, training environments, that sort of thing. Only consider production environments when you know exactly what the workload profile of the production application is, and you know exactly what the performance profile of the hardware plus the virtual machine(s) will be."

Ozar agrees, and reinforces the argument that up-front planning when it comes to your SQL Server virtualization strategy will pay significant dividends in the long run. "Before you start, define what 'success' means on your virtualization project. Establish service level agreements with the application’s business users before you start, and monitor your performance and availability in order to determine the virtualization project’s success," says Ozar. "Database administrators struggle with the SLA concept; we don’t always do a good job of defining exactly how fast an application needs to be in order to be considered 'up.' If we don’t agree on service level agreements before we start virtualizing, we can end up in a scenario where our end users complain that the apps aren’t fast enough, but IT believes everything’s just fine because we’re saving so much money. [You also need to] establish transparency between the DBAs and the virtualization admins. The DBAs need to be able to trust that their virtual servers aren’t being unnecessarily throttled to low resource ceilings, and the virtualization admins need to trust that the DBAs aren’t rebuilding indexes in the middle of a workday. This kind of cooperation facilitates successful virtualization projects."

Ozar also encourages DBAs to work closely with their colleagues in the IT department, mainly to encourage clear lines of communication and help spot potential problems before they occur. "Make absolutely, positively certain that your antivirus software is not doing regularly scheduled scans on virtual servers," says Ozar. "If all of the virtual guests kick off a virus scan on Friday at 9pm, as some antivirus software does by default, it will bring all of the virtual servers to their knees—even if they don’t have antivirus software installed. That’s a prime database backup time window, and that can wreak havoc on SQL Servers when they can’t get I/O responses back in under 15 seconds. Transparency between the DBA and system admin teams helps make sure this kind of global scheduling problem is averted."

Using SQL Server with Hyper-V

If you’re looking at teaming SQL Server with Microsoft Hyper-V, Allen has some pointers to optimize VM performance when running SQL Server. "For the best possible VM performance we recommend provisioning fewer total VM logical processors than the total number of physical CPU cores available on the server," says Allen. "You can over-provision and run many VMs on a single box, but the VM overhead ([the] ratio of an operation done in a VM to the same operation done natively) will go up. This may not be acceptable in some scenarios."

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."

Using SQL Server with ESX Server and Virtual Infrastructure 3

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.

Virtualizing SQL Server

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.