Guide to SQL Server virtualization best practices
A comprehensive collection of articles, videos and more, hand-picked by our editors
As virtualization has grown more sophisticated, there is more incentive than ever to move SQL Server instances
to a virtualized server. The number of physical machines goes down, along with power consumption and licensing costs, and you can better manage the systems you do have since they’re all virtualized. It all sounds like upside, but there are still a few major scenarios in which SQL Server virtualization won’t benefit you.
When VM I/O is a poor substitute
The single biggest resource a database needs to perform well, apart from CPU, is disk I/O. With a physical server largely dedicated to one SQL Server instance -- or even multiple instances -- it’s much easier to ameliorate I/O bottlenecks. You can change up disks for faster models, move from spinning platters to solid state if it’s cost-effective in terms of IOPS (input/output operations per second) or add more random-access memory (RAM) to increase buffering. The trade-off is cost, since such upgrades rarely come cheap.
More on SQL Server virtualization:
Is SQL Server virtualization more than a passing trend?
Virtualizing SQL Server: myth or reality?
Get the right hardware for your SQL Server virtualization
During SQL Server virtualization, though, the hardware must be as good as or better than the hardware you’re migrating from or database performance will suffer. This is doubly true for I/O, since the last thing you want is to share the I/O bandwidth needed for a heavily used database. There are ways to mitigate that -- for example, you can put your database’s storage on physical spindles and I/O channels that are deliberately segregated from other virtual machines (VMs) -- but this must be done before you migrate to a VM.
Here’s a common example: Many servers dedicated to databases use RAID 1+0 (also known as RAID 10) in their storage arrays, which is expensive but yields the best results. A VM setup, on the other hand, might use RAID 5 as the best trade-off between performance and redundancy. A workload optimized for the former hardware would suffer on the latter, unless you can dedicate physical disks directly on the VM to replicating the original RAID 1+0 setup.
None of this implies that the disks have to be locally attached storage. A storage area network on a VM of equal or better speed than local disks on the physical box will certainly work. What matters is IOPS -- not the exact technology needed to achieve it.
If you build the VM host yourself, you have some control. But if you use someone else’s VM hosting, you may be at the mercy of a one-size-fits-all setup.
When VM memory isn’t enough
Databases benefit from having plenty of physical memory: it’s a place to perform operations and a cache for I/O. SQL Server works best on its own standalone machine for this reason, since it can freely provision out memory as needed and not directly compete with other applications on the same machine. A rule of thumb is to allow enough RAM for the entire database -- or at least the most commonly used parts of it -- to be cached in memory, whenever possible.
A VM version of the same database server needs to be provisioned with the same amount of memory, or more, as its physical counterpart. Many VM systems have memory-sharing techniques that allow identical pages of memory to be shared across VMs. But such sharing tends to be for things like the operating system running in each VM. Most of what’s sharable between VMs is not going to be the contents of the database itself.
This is another area in which having memory-consumption statistics for your SQL Server instance is helpful. You’ll be able to see how much memory your production database needs, and you’ll be better informed on how to match that in a VM.
When there’s no pressing reason to virtualize
There should be a compelling case for virtualization, meaning the benefits go beyond good intentions. Virtualizing just because you can rarely produces good results.
For instance, if you have an old, legacy SQL Server setup that is simply sitting around on a box of its own -- and which has relatively modest demands by today’s hardware standards -- that’s a good candidate for virtualization. The older and slower the machine, the more benefits you’ll get from virtualization. Consolidating many boxes means less power consumption, cooling and floor space.
On the other hand, you might have a whole cluster of SQL Servers that would produce no particular cost savings or performance benefits by being virtualized. Consolidating that cluster into a VM might provide some savings in terms of electricity or cooling -- but it might come at the cost of overall performance. Best to leave things as is.
SQL Server virtualization should only be implemented when there’s a good business case for it and when you’re not losing overall IOPS or memory allocation in the process. Although the vaunted advantages of virtualization can make it seem miraculous, it’s important to remember that it’s a tool, not a magic bullet.
ABOUT THE AUTHOR
Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including InformationWeekand Windows Magazine.