Guide to SQL Server virtualization best practices
A comprehensive collection of articles, videos and more, hand-picked by our editors
Editor's note: This is the first part of a two-part story by Robert Sheldon. Part two will appear next week.
Virtualization technologies have seen remarkable advancement in recent years. Virtual machines are more powerful than ever. They can handle greater capacities, are easier to manage and provide greater control over resource allocation. So, it's not surprising that once die-hard, anti-virtualization DBAs have reconsidered their stance regarding SQL Server virtualization. Not only can they quickly provision a database environment, but they also can replicate that environment many times over, while appeasing the IT gods who look to virtualization for cost-savings and convenience.
But despite all the virtual machine (VM) hoopla, not all SQL Server environments should be virtualized. Issues around licensing, performance, availability and support must be considered before SQL Server can be committed to a virtual environment. Failure to do so could result in a substandard production environment that wears the VM label proudly but fails to deliver on the VM promise.
SQL Server virtualization licensing
Let's start with SQL Server licensing, which can be a daunting process in itself, even more so since the release of SQL Server 2012. How you license SQL Server depends on which version and edition you're running, as well as the number of processors on your machine, whether physical or virtual.
For example, SQL Server 2012 offers three editions, all of which must be licensed: Enterprise, Business Intelligence and Standard. You can license each edition per processor or per server. If the latter, you must also purchase a client-access license for each device and user connecting to SQL Server. If you virtualize SQL Server Enterprise, you can license the product at the VM level or host level. You can license the Standard and Business Intelligence editions of SQL Server only at the VM level. What you can do from there with any of the editions depends on which edition you license and whether you participate in Microsoft's Software Assurance (SA) program. For instance, you can license the Standard and Enterprise editions on a per-core basis within a VM, but not the Business Intelligence edition. In addition, there's a four-core minimum for per-core licensing, even if you're running only two virtual CPUs. If you want to move your VM, you'll have to wait 90 days between moves unless you participate in the SA program.
What this means is that your organization could end up doling out a lot of extra cash for VM licensing, and the result could be that the cost savings realized through virtualization could be mitigated, at least to some extent, by the SQL Server licensing structure. Although the key is to carefully plan and provision your VMs for SQL Server, such provisioning isn't always within your control. For example, if you spread your SQL Server Standard instances across numerous VMs running only two virtual CPUs, you could end up paying a lot more for licensing than if you were to consolidate those instances on a single physical machine. SQL Server licenses apply to the operating system environment as a whole, regardless of the number of instances within that OSE. A physical server is one OSE, as is each VM, and they can add up.
The bottom line? Don't virtualize SQL Server until you completely understand the licensing implications.
SQL Server virtualization performance
Because virtualization technologies have made great strides, they've been embraced across a wide spectrum of organizations. Even so, a VM's ability to handle your SQL Server load still must be carefully assessed before you jump into the virtual waters. Of critical concern, for example, is the way the VMs and SQL Server data are stored. Even under the best conditions, the possibility of resource contention must be taken into account. Are other VMs sharing the same storage area network? Is the data being stored in the same place? If so, your SQL Server environment could be at the mercy of those other machines.
For more on SQL Server virtualization
Denny Cherry on the pros and cons of virtualizing
What are the hardware requirements for SQL Server virtualization?
For small database implementations, resource contention probably won't be a problem, especially given the advances in resource management. Yet large systems with heavy processing loads can quickly strain the host's I/O capabilities. Having to rebuild large compressed indexes, for instance, can quickly max out the CPUs, as can defrag and other maintenance operations. Many DBAs recommend that you avoid 1-GB iSCSI storage altogether and aim for storage that can consistently pump data at more than 100 megabytes per second -- or faster if circumstances call for more oomph.
If the storage capabilities cannot fully meet your needs, you must at least ensure that you have enough memory to compensate for shortfalls, as well as to meet anticipated peak demands. Your CPUs must also be able to handle those peak capacity loads. Although virtualization products support more demanding apps than ever, they still have their limits in terms of the number of virtual CPUs and the amount of memory they can support.
If you're running SQL Server systems that eat up CPU and memory, don't virtualize them unless you first can perform adequate testing to ensure you're getting the performance you require. If your system is so demanding that you can't run any other VMs or SQL Server instances on the host, you have little to gain by virtualizing. A VM requires additional storage and memory, as well as the resources necessary to run a second operating system. If multiple VMs are running on the host (which is the primary reason for virtualizing), the possibility of resource contention must be factored into your planning. What happens when several applications reach peak usage at the same time? In a virtual environment, concurrency issues spread well beyond the configuration of a single database.