SQL Server Virtualization Advantages and Disadvantages (Part 2 of 5)

Related Tips: More

Problem

Your previous tip helped define the VM environment and discussed some of the advantages and disadvantages of virtualization. What about virtualizing SQL Server? Is this always a good idea? What are some things I need to think about? Check out this tip to learn about the advantages and disadvantages of virtualizing SQL Server.

Solution

You will need to consider at least two paths before deciding to virtualize any of your SQL Servers. The first path is to think about why you are virtualizing your systems; consider the advantages and disadvantages a virtual environment provides. Some of these advantages and disadvantages are systemic to a specific vendor and others are systemic to virtualization. The second path is whether or not your SQL Server is capable of being virtualized; is it feasible to virtualize your SQL Server? You’ll base the decision to virtualize both on how the SQL Server is being used and the size of your SQL Server. For either one of these paths you’ll most likely need to argue against virtualization as opposed to arguing for virtualization. This is a general observation, but I’ve found that virtualization provides so many benefits outside the SQL Server environment that your company has already decided to implement the technology. I have witnessed one large company refusing to virtualize but, since their competitors are virtualizing, it won’t be long before they begin implementation.

I’m going to purposely ignore the advantages of virtualization as they pertain to the datacenter and instead focus on the advantages for the SQL Server DBA. There is some overlap, but I want to address specifically how virtualization helps you and your SQL Server environment. I won’t be able to cover everything, your mileage may vary depending on what features you implement and what vendor you use. Still, I want to provide a decent overview so let’s take a look.

Advantages of Virtualizing SQL Server

Advantage #1: Provisioning (Hot-Swapping)

By provisioning I’m referring to the capability of dynamically assigning additional resources to a VM instance. I cannot overstate the beauty of being able to add 100GB to a dying LUN without downtime and without the user even knowing its happened. You want another 3 GB of memory – no problem. The process really is a thing of beauty. It helps us DBAs focus on other things and it gives me a sense of confidence that I can provide additional resources to the instance on a moment’s notice.

Advantage #2: Resource Management

This is what virtualization was created to do and there are advantages. I’m a big recycler and I hate waste. I guess that’s why I gain weight because I always have to finish what’s on my plate. I just can’t stand throwing away food. I apply this same OCD to my servers. I can’t stand a server consistently showing 95% CPU idle time – it’s a waste of CPU. On the flip-side a physical server showing 95% CPU utilization drives me crazy when I don’t have another slot available. Virtualization frees me by allowing me to think about this less often (notice I didn’t say ever). Ballooning is the method by which VM moves around memory. Ballooning will add memory to your VM if you need it and take it away if it doesn’t (assuming you are not using lock pages in memory). vCPU’s can be ramped up or ramped down as necessary. VM is especially helpful for those once-a-year-I-demand-everything budget applications.

Advantage #3: Cloning

We recently had a request for 14 new SQL Servers (seriously!). They all ran the same application, used the same databases, and had the same configuration. The only difference was they were in different geographic locations. In the past this might have been a nightmare manual process subject to countless possible misconfigurations. Instead, in the virtual world, the VM team cloned one system 13 times. All I had to do was go to each system and update the @@servername to reflect the new name. Voila! 14 new SQL Servers to manage and, especially, license. More about that next.

Disadvantages of Virtualizing SQL Server

Disadvantage #1: Provisioning (New Servers)

Provisioning is the double-edged sword of virtualization. Since we’ve virtualized our environment, our server count has increased over 20%. We build anywhere from 4 to 10 new servers every month. We decommission some servers, but most are brand new and many still including test and development environments. Server sprawl and how to handle, manage and license the sprawl will be a serious issue going into 2011 and beyond. We do try to consolidate on a shared server when possible, but most systems don’t fit into that model. The business has little incentive to consolidate SQL Server because virtualization has already consolidated most of the environment. Virtualization consolidates all the physical server resources and drastically shrinks the footprint in the datacenter. After virtualization a company tends to lose the driving momentum to consolidate databases. Make sure you have clear SQL Server install and configuration processes in place prior to virtualization, because you’ll be doing a lot more installs. I recommend trying to use an automated install application such FineBuild.

Disadvantage #2: Division of Duties

After virtualization you are most likely going to have 5 groups managing (and learning) the virtual environment: VM Team, Storage Team, Server Team, Backup and Recovery Team and the DBA Team. This will depend on the size of your organization. A large organization will separate the normal operational server team from the VM team. The VM team is the group of folks who implemented the virtualization and they may slowly hand over duties to the operational server team, but still retain authoritative control over the environment. You will need to interface with all of them at a more intense level than in the past. In our shop the SQL DBAs have full access to all the SQL Servers and read access to vSphere and we still do not have insight into the full details of our SAN environments or what is shared on our hosts. Backup and recovery are constant struggles because many backup errors are due to problems with vMotion or our NetApp infrastructure. The point here is you’ll need to ramp up communication at the same time you ramp up virtualization.

Disadvantage #3: Performance Tuning

I’ll address this more later in the series, but how you monitor performance will change after virtualization. It becomes a bit more complicated and you’ll need to learn some new terminology. You can’t simply rely on performance monitor. In a VMWare environment you’ll need to start using vSphere or add VMWare performance counters to your performance monitor logs. This is where the deep dive comes in because in order to understand performance in a virtual environment you’ll need to understand how virtual environments manage resources.

Disadvantage #4: Licensing

Along with server sprawl comes the issue of licensing. Add on top of that the virtual environment and its time to call a shrink, and the bank. You’ll need to work out licensing with your company and Microsoft to make sure you’re compliant in a virtual environment. Basically when you run SQL Server 2008 R2 in a virtual environment you license for each virtual processor used by the virtual operating system. You do not license based on the physical processors. Keep in mind though the vCPU is considered to have the same number of cores as the pCPU. This means you cannot license a vCPU by core (for example, 2 core pCPU but only 1 licensed vCPU). This scenario changes based on the version of SQL Server you use. Let’s say you purchase the Datacenter edition of SQL Server 2008 R2. Once you license it for all of the physical processors on the host, you can run an unlimited number of SQL Server instances.

When Not to Virtualize SQL Server

Let’s start with a basic limitation in VMWare. ESX 4.1 limits the total vCPU to 8 (I believe Hyper-V limit is 4). Granted, you get a lot more bang for the buck per vCPU in a virtual environment, but I would suggest running a tool like VMWare’s Capacity Planner prior to virtualizing any heavy-hitters. I wouldn’t necessarily consider virtualizing large data warehouses, but not because of size. We successfully virtualized our 800 GB SharePoint server so size is not a problem. The primary point for virtualization is consolidation, so I tend to think if you virtualize a high octane system and it’s the only system that can run on a host than you aren’t gaining much. There is DR and HA to consider, but that’s a call unique to your business. I guarantee things will change soon. A new version of ESX is due out soon and I’m sure they’ll up the vCPU limit.

It’s also possible there may be some systems, mostly older systems, which may just not virtualize. This isn’t a SQL Server problem though. We’ve virtualized everything from SQL 7 to SQL 2008 R2. There may just be some odd applications that don’t work well in a virtual environment. We have one critical Windows cluster that would not survive the P2V. We decided to just leave it physical instead of pushing the issue. As a DBA I suggest closely monitoring the migration effort and be ready to test the system post-virtualization.

The conclusion is for most organizations there will be very few systems that you can’t virtualize. If you have your doubts, then be sure to test prior to migration or prepare an effective rollback plan.

In Conclusion

If you looked closely you’ll notice the disadvantages outweigh the advantages. Still, I’m a big fan of virtualization. We can overcome all the disadvantages. The advantages are too good to pass up. As the virtual software gets better and better we’ll see larger and larger systems virtualized. As more and more companies experience successful virtualizations others will continue to make the leap. One purpose of this series is to both provide my own experiences and consolidate my research. DBA’s need to work together to make the virtualization process transparent to themselves and to others.

Next Steps

Microsoft Licensing in a Virtual Environment.
I’ve always liked this post by Gavin Payne. It’s very appropriate in a virtual environment Infrastructure DBA.
Brent Ozar post on deploying SQL Server on VMWare Top 10 Keys to Deploying SQL Server on VMware.
If this tip completely lost you then refer back to Part 1 – SQL Server Virtualization Overview (Part 1 of 5).
My next installment will discuss the virtualization planning phase. I’ll discuss IO resources and licensing. I’ll talk about whether to create a new VM or P2V and whether Windows clusters are still a viable HA solution.

http://www.mssqltips.com/tip.asp?tip=2441

SQL Server Virtualization (Adv vs Disadv).