Guide to SQL Server virtualization best practices
A comprehensive collection of articles, videos and more, hand-picked by our editors
It is hard to argue with virtualization. Few technologies have had such a sudden and profound impact on the way...
businesses run their IT operations, saving them money and manpower, all with scant glitches or snafus. But when it comes to databases such as SQL Server, analysts warn there may be a few “gotchas” -- namely, SQL Server virtualization risks -- lurking out there.
One note of caution came from Peter O’Kelly, principal analyst at O’Kelly Associates. O’Kelly said there are “waves,” or trends, in the IT industry, and the current wave holds that virtualization is supposed to be good for everything. “Now, industry is discovering that there are some places where you may want to dial that back a bit,” he said. “It is probably something that needs to be assessed on a case-by-case basis.”
Virtualization might not always be a good thing for databases in general because it may interfere with the heuristics of the database management system for data access optimization, which is designed to work directly with data storage devices.
“Adding virtual storage may result in more disk access operations, and since disk access is measured in milliseconds while memory access [e.g., for cached data] is measured in nanoseconds, the consequences can be significant,” O’Kelly said. “The heuristics will break, the optimizer won’t do everything it is expected to do, and that will create a problem.”
Chris Wolf, an analyst at Gartner Inc., agreed that memory can be an Achilles’ heel for databases in virtualized environments. “Historically, people have run into issues involving memory management,” he said.
For instance, a few years ago hypervisors were using software to emulate physical memory. And, as noted by O’Kelly, when you try to emulate memory in software you run into bottlenecks and end up with slower response times.
However, starting in the second half of 2009, AMD began to introduce AMD-V Rapid Virtualization Indexing on hardware, and Intel early last year released Extended Page Tables. According to Wolf, these developments allow virtual machines to manage their own physical page tables in memory. That removes the software bottleneck. “So a few years ago people virtualizing SQL Server might have said it doesn’t run well, but with the right architecture today, it isn’t a problem,” Wolf said.
Another potential rough spot for SQL Server virtualization involves memory appetite. “SQL will take as much memory as you will give it, and that will cause problems with resources sharing," Wolf said. "That’s why on a physical server, people must tune it to use as much memory as it needs, not as much as it wants.”
Fortunately, Wolf said, the tuning is straightforward. So, he advises that infrastructure people and SQL Server administration teams make a point of talking about the issue and resolving it.
The same can be said for making sure I/O is optimized. As an example, Wolf cited vSphere 2.1, in which VMware introduced Paravirtual SCSI. “It is a new storage driver to provide accelerated I/O to access storage, and they introduced a new feature -- storage I/O control -- which lets you prioritize storage access for certain applications, so one app won’t take over all of the I/O,” he explained.
Similar tuning issues concerned Greg Shields, an IT analyst at consulting firm Concentrated Technology. Now, although almost anything can be virtualized, Shields said implementation can still present challenges.
It used to be that you could look at a server and say, that’s a network problem -- but with virtualized servers, it might really be a lack of processing resources.
-- Greg Shields, IT analyst at Concentrated Technology
“In my experience, most IT pros do not have a good handle on capacity management,” he said. When the world was all physical servers, Shields said experience taught people to develop gut feelings for things like the supply of memory. “It used to be that you could look at a server and say, that’s a network problem -- but with virtualized servers, it might really be a lack of processing resources.”
According to Shields, what is needed now, especially with SQL Server, which has the potential for very high utilization, are tools to help administrators convert their data and metrics to actual intelligence. “By themselves, no human being can really do a good job of converting those metrics into useful information,” he explained.
“When you have those insights, maybe it means you don’t consolidate as many machines or maybe in some cases it might mean you virtualize one server on one physical machine, as counterintuitive as that might seem,” Shields added.
However, he stressed, the power of virtualization is such that the “overhead” of virtualizing is now so minimal that performance is “almost native” anyway. And that’s bound to be good news for those running SQL Server.
“Today, with the right architecture, there is no reason you can’t run a SQL Server workload in a virtual machine environment,” Wolf said. “We have had many of our customers doing this with large-scale databases. Our position is that virtualization should be the default platform for all your apps in an x86 environment. The onus should be on the owner to show why it isn’t good rather than on IT to show why it is needed.”
SQL Server virtualization: Checklist for success
With the right hardware, some planning and a few key steps, SQL Server will generally perform well in a virtualized environment. Gartner analyst Chris Wolf makes the following basic suggestions:
- Use the hardware-assisted memory virtualization feature because without it, high-memory page latency will result in very poor performance for any database workload at enterprise scale.
- Make sure there is relative equality among virtual machines. In other words, you need to have appropriate storage I/O, network I/O control and a resource pool so that in-memory activities are appropriately prioritized for more critical virtual machines.
- Implement tuning so that applications consume appropriate amounts of memory.
And to avoid problems, Robin Layland, principal analyst at Layland Consulting, offers these pointers:
- Make sure that virtualized applications like SQL Server are provided with the correct virtual LAN number so that they can communicate with the host server.
- Give your server load balancer information about the applications it will be handling. “If you bring up another instance and don’t tell the server load balancer, there is a good chance no one will be able to use it. If it is running five servers and you add another, it may still think you have five,” Layland said.
- When you bring up another instance of an application, coordinate with personnel responsible for server operation and network operation so that they can make appropriate changes.
Alan R. Earls is a Boston-area freelance writer focused on business and technology.