SQL Server Virtualization Overview (Part 1 of 5)

By:   |   Updated: 2011-06-29   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | > Virtualization


Problem

I hear the rumblings of DBA's facing the prospective of virtualizing their databases. Some have managed to stave off the inevitable while others have embraced it slowly one test or development database server at a time. I'm writing a series of tips as a guide from a DBA who has already been drowned in the virtual sea to DBAs who want to take the plunge. My team manages more than 365 virtual database servers in the healthcare field and my company has virtualized more than 3,000 servers. Still, I'm not writing from the perspective of someone who is an evangelist for virtualization, instead; I'm writing this because as a DBA you eventually won't have a choice. If your company has more than 4 people, you will virtualize your servers. It is better you understand and read the new map now than continue to wander in the wilderness.

As a disclaimer, these tips do not teach you how to virtualize your servers. These tips are meant to teach you how to manage and understand your virtual environment from a DBA point-of-view. At the end of this series of tips, you should be able to talk the lingo with your server and storage group so you can better argue for an architecture that fits your needs. You will also learn some of the pitfalls I've experienced as well as some of the successes. I hope it proves useful.

Solution

So, you might be asking, what is a virtual machine? Simply put a virtual machine is a computer within a computer. One machine is physical and the other one is software (virtual). The physical machine is called the host and the software-based machine is called the virtual machine, or VM. The devious part of this relationship is the VM machine doesn't know it's a VM. The VM is like a goldfish in a fish tank thinking it's in the ocean. The VM machine sees it has RAM, IO, network and CPU but what it doesn't fully realize is these are only given to it by the good intentions of the host. All of those things can be quickly and seamlessly removed, or added, at the host's whim. Furthermore, the virtual machine may be sharing these precious resources with neighbors it knows nothing about. A database server could have a file server right next to it and they would never meet yet, that file server could steal CPU cycles from the database server. I know, it sounds cruel, but if the VM is happy than we shouldn't see the harm in it.

VM Architecture
Image Source: http://zone.ni.com/devzone/cda/tut/p/id/8709

As of this writing, there are 2 major Enterprise-level VM providers: VMware and Hyper-V. Of course there are more vendors who offer VM solutions, but you'll most likely use one of the above unless you are installing VM on your desktop. Research sites estimate that VMware owns between 50% to 80% of the market with Microsoft's Hyper-V and Citrix's XenSource gaining ground. You will find that despite the various vendor offerings, the VM architecture remains closely similar. One or more virtual machines are running on a host managed by a VMM (Virtual Machine Manager). Everything else is a confusing jumble of alphabet soup. I'll define a list of common VM terms later in the tip.

What are the benefits to virtual servers?

Now that we have a rough idea of what a virtual environment looks like, let's discuss some of the benefits. Firstly, borrowing a resource such as CPU's (in a virtual environment they are called vCPUs since a CPU is not physically attached to the VM - the physical CPUs are labeled pCPUs) can be a good thing when you have plenty to spare. What works in life can work in VM. If you have plenty and someone else has little, you might as well share. Sharing allows companies to optimize resources. The technical term for memory sharing is called ballooning and refers to the delegation of memory resources where it is most needed on the host. Ballooning takes away from the classic provisioning which is typical when configuring new systems. Some DBA's like this and some don't. We'll talk about that more later.

VM offers fast provisioning of new systems. Remember a VM is simply software or, in simpler terms, a file. An administrator can create a template of a VM. The template contains all the settings and software necessary to create a new system. Companies no longer have to wait for ordering physical hardware, or make room in the datacenter rack for new installations. The other benefits to the VM provisioning model include reduced energy costs, reduced capital costs, more disaster recovery options and, arguably, reduced management and operational costs.

What are the downsides to virtual servers?

Wow! So VM's are the perfect solution for everything, right? No. The risks are both architectural and operational. The inherent consolidation of resources onto a single host is a risk. Because you can have a large number of virtual machines on a single host, a failed host will potentially cause an outage for many systems. The equivalency is power going out for a whole rack of servers.

How the VM team configures the environment is another risk. There are some settings in a VM environment (power management comes to mind) that, if enabled, can be disastrous for certain types of applications (SQL Servers come to mind). The teams managing the virtual environment may not understand the resource needs of your system and this can compromise both performance and stability. Finally, remember you are sharing resources in a virtual environment but, as we all know, different applications have different needs. An OLTP database has different resource needs than a file server particularly when it comes to IO. Disks are already the bottleneck and VM won't change the equation.

There are also issues pertaining to control. As a DBA managing servers in a virtual environment I now heavily rely on the storage and server teams for information concerning performance and recovery. They control the centralized infrastructure and have made the decision to implement the physical hardware so now, as the DBA, I need to make sense of the architecture and how it affects SQL Server.

What's next?

Whether you like it or not if you're reading this then either your SQL Servers have already been virtualized or someone has told you they will be. It's ok. The following tips will either help you redefine your existing environment or prepare you for your new one.

Terminology

  • Ballooning - The act of moving over provisioned memory to another virtual machine.
  • Host - The physical machine running one or more virtual machines.
  • vCPU - A CPU assigned to a virtual machine. This could be only a single core.
  • VM - Virtual Machine. The software emulation of a physical machine.
  • VHD - A virtual machine file in Hyper-V.
  • VMDK - A virtual machine file in VMware.
  • VMM - Virtual Machine Manager. The software running on the host, which coordinates resources across one or more virtual machines. You may also hear this referenced as the hypervisor or ESX.
  • vMotion - The process of moving a virtual machine from one host to another with no application downtime. This term is specific to VMware.
  • Virtual SMP - Virtual Symmetric Multi-Processing. The ability of a virtual machine to use multiple physical CPU cores.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Scott Shaw Scott Shaw is a Lead SQL Server DBA with extensive experience running SQL Server in a virtualized environment.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-06-29

Comments For This Article




Tuesday, July 12, 2011 - 9:09:21 PM - Scott Shaw Back To Top (14158)

Jac,

Sorry for the late reply. The maximum vCPU you can allocate to virtual server in ESX 4.0 + is 8 vCPU.  This is a limitation in ESX and not SQL Server. If you are not running an ESX version greater than 4 (which appears you might be since you are limited to 4) then I strongly suggest you upgrade. There were big changes in the CPU scheduler for the higher version and you should see much better performance and be able to assign additional vCPU.  The next version is rumored to support up to 32 vCPUs.

Even VMWare will admit older versions of their software was not meant to handle large systems. I don't think this is the case anymore. We run very large systems in VM without performance problems. If we do see problems its usually related to memory and not CPU which is expected from a SQL Server. We run Intel x5680 at 3.3 GHz.

If you want to deep-dive into the advances in the co-scheduler in ESX 4, here is the document: http://www.vmware.com/files/pdf/perf-vsphere-cpu_scheduler.pdf

Hope this helps.

Scott 

Wednesday, July 6, 2011 - 10:25:42 AM - Jac Back To Top (14138)

  I understand the virtualization process and there can be some safeguards put in place to help allow for potential hardware failures.  But what about the DB that really needs more than 4 cpu cores?  Unless you have some new information, the biggest problem I have run into is that a VM can only allocate 4 cores for SQL Standard and 8 cores for SQL Enterprise.  The vcpu's (physical cores allocated) are treated in the virtual environment as a full cpu.  Granted I can allocate more virtual boxes to use the remaining resources, but these are fixed limits of what can be handed to a MSSQL server.  From a licensing perspective, this is great, but not performance for a highly used DB.

  Here is an example:  1 physical server with 4 quad core processors in it equates out to 16 cores available.  MSSQL licensing says I need 4 processor licenses to run on this physical server, and you can install MSSQL Standard edition and utilize every core.  Now you decide to virtualize on this box.  Still using MSSQL Std, you can only allocate 4 vcpu (cores), which equates out to 1 physical cpu, therefore it requires 1 MSSQL processor license.  Granted I have 3 cpu's (12 cores) left to allocate to other servers, but 4 vcpu's is the max I can give this virtual DB server.

  How can I get around this?  I want to give the virtualized DB more cpu power for more processing power.  I know that I can allocate the remaining resources to other servers doing different things, but I need more power during the day for the DB.  How can I give it say 3 cpu (12 cores) and use the cycles?  Right now, if I don't allocate any other servers to this physical box, the other 3 cpu's (12 cores) sit idle doing nothing.  Am I wrong, or is virtualization of DB's really meant only for the small DB apps, and not large, real DB intensive applications?  Thank you for your input.  Jac














get free sql tips
agree to terms