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.

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

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