A Guide to Navigating Around the Deadly Icebergs that Threaten Virtualized Databases
When it comes to virtualized workloads, databases can be in a size class all to themselves. This and other factors can lead to several unique challenges that on the surface might not seem all that significant, but in reality can quickly sink a project or implementation if not given proper consideration. If you're put in charge of navigating such a virtual ocean liner-sized workload through these iceberg-infested waters, you'll want to make sure you're captain of the Queen Mary and not the Titanic. How do you do that? The first step is understanding where the icebergs are.I recently had a conversation with Thomas LaRock, president of the Professional Association for SQL Server (PASS) who also happens to be one of our Head Geeks here at SolarWinds, to get his input on this very topic. Here's what I learned:
CPU & Memory Allocation
First, don't treat a database like a file server when it comes to configuration and virtual resource allocation. Typical configurations allow over allocation of both memory and CPU. However, configuration of CPU shouldn't be more than 1.5-2 times the number of logical cores you have. When it comes to memory, don't over allocate at all if possible, instead going to at most 80 percent. As memory utilization gets near 100 percent, you may not have enough resources to even reboot the host. If you do push your systems, make sure that you not only have a virtualization monitoring tool, but that you actively use it.
High Availability Strategy
Most virtualization admins use snapshots and vMotion-Thomas' preferred option-as a primary approach to address high availability concerns. On the Windows side specifically, clustering and availability groups are also common. While either technology can be effective, they probably shouldn't be used together. An example of why not is a database VM being vMotioned to another VM as a result of a performance problem, but the availability group just seeing that as a server instance no longer responding. If you do use both, make sure that you don't allow automatic vMotion so there is always an operator in the mix to (hopefully) prevent problems, otherwise bad things can happen.
To Monster VM or Not?
You might wonder if an easy way to overcome the challenges of virtualized databases is simply to allocate one of VMware or Hyper-V's "monster VMs" to the database instance and just solve problems by throwing hardware at them. However, a better approach is to put database VMs on a mixed use host that includes a range of production, development, test and other workload types. The rationale being that if you have a host with nothing but one or more database servers running on it, you have no options if you accidently run out of resources. With a typical mixed use host, you're less likely to be simultaneously hammering one resource type, and if you do start to hit a resource bottleneck, the impact of turning off a development or test VM to provide short term resources will typically be less than shutting down a production database.
Taking these considerations and tips into account can help make sure your virtualized databases stay afloat a long time rather than being lost due to a potentially avoidable iceberg on the maiden voyage.
If you're looking for additional information on virtualizing databases, SolarWinds has a number of great white papers available online, including "5 Risks for Databases on VMware" and "Monitoring Database Performance on VMware."
Note: This post originally appears in VMBlog at http://vmblog.com/archive/2014/10/23/would-you-rather-captain-the-queen-mary-or-the-titanic.aspx#.VGUUxfnF8uK