Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Virtualization Advantages and Disadvantages Part 2 of 5


By:   |   Read Comments (9)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Virtualization


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, July 27, 2011 - 2:19:08 AM - Maddy Back To Top

What an awesome article! It is very informative and full of knowledge. All the advantages and disadvantages of SQL server are very easy to understand because of the way they are presented by the author. It is clear that the main designer of SQL server is Microsoft. To find out a detailed overview on the history and development of SQL Server, check out the following article: http://www.techyv.com/article/sql-server-past-future.

I think we should all share knowledge on these websites. If any of you have more information on SQL, please share it here as these articles really help those who are just a beginner in the computer world.


Friday, July 22, 2011 - 4:16:20 PM - Thomas LeBlanc Back To Top

Good job, look foward to next 3 articles.

 

Thomas


Thursday, July 21, 2011 - 5:24:04 AM - Alan Cranfield Back To Top

Great post! Thanks. Love your analogy to eating everything on the plate to maxing out your servers. Personally, I prefer my servers to be like highly trained athletes with low pulse rates but have the capacity when needed to handle those spikes.

Ah, provisioning, yes.  I've lost count of the number of times our SANs have saved our butts by dynamically expanding a drive or provisioning a new drive... Running out of capacity happens more often than you think especially in high volume OLTP financial environments.

We have a couple 100 VMs in our INT/PRE-PROD environments for SQL – not in production (yet).  Our physical machines in our PROD environment are matched 1:1 with VMs in our INT environment. Which is kinda cool


Wednesday, July 20, 2011 - 3:41:05 PM - Scott Shaw Back To Top

Before our virtualization project we were concerned that the 3 month capacity plan performed on the system was not indicative of many of the IO cycles of our systems.  The 3 month monitor may capture quarterly spikes in financial applications but wouldn't catch year-end budget spikes on even the dreaded yearly employee performance review applications.  So far nothing major has happened though we have seen some issues with memory.  Strangely, we are seeing the OS being starved of memory and not SQL. We do not use ballooning. I'm not sure why the VM team decided not to turn on the balloon driver. 

Yes, the new version of VMWare will allow for 32 vCPUs. VMWare is definitely trying to entice the big players to come on board. Still, I don't think we're going to see a virtualized Parallel Data Warehouse anytime soon.

Wednesday, July 20, 2011 - 3:40:10 PM - Scott Shaw Back To Top

John,

You're right, provisioning is definitely a double-edged sword.  I say it is less a matter of being lazy as maybe not getting good requirements or inheriting a system that wasn't properly configured from the outset.  You still want to size appropriately.  The dynamic storage allocation at the very least helps to eliminate the dreaded practice of shrink and forget. 

 
The odd thing about VM is from a storage perspective VM takes up more storage than physical implementations. This is mostly due to the storage of VM templates and clones.  Some of these get forgotten. Also, as you mentioned, over-provisioning can be dangerous if the VM folks don't provide a way to manage it when the space runs out.  

Wednesday, July 20, 2011 - 3:38:30 PM - Scott Shaw Back To Top

Sreekanth,

Never say never! :)  I understand what you are saying. Highly available systems need to be carefully looked at but there are many HA solutions offered in VM that provide more sense to virtualize HA systems.  If I may play phychologist for a moment, it appears to me the fear stems from a lost of control.  Your fear is definitely justifiable. This is why DBAs from all over must unite to understand what is happening behind the scenes and be able to intelligently discuss our requirements.  At the end of the day, we're still responsible for the integrity and recoverability of our databases no matter what platform is choosen.


Wednesday, July 20, 2011 - 3:08:53 PM - Sreekanth Bandarla Back To Top

If I really need close to three 9's or two 9's availability i would prefer physical Sevrers with Microsoft Failover Clustering. I'm not sure what a VM can offer in terms of HA( I've to rely on my Virtualization admins, most of the times i(as a SQL DBA) got a hard time from them for optimizing things!). My previous expereince was, we've implemented failover Clustering on VM's and i observed Failover takes lot more time compared to time it takes on a Physical Cluster. I beleive there is some Mumbo Jumbo Going on Behind the scenes between VMWARE and MSFT Failover Services which causes the delay.

machine Critical Systems and Very High Activity(throttled CPU very Often, no matter how optimized the Queries are, few things require multiple CPU Cores) - I would  Say No to Virtualizing SQL Servers! I'm not against Virtualization. I simply love it and how it makes things easy. All i want to express is, If you are talking about SQL Server supporting a very Critical Application, think Twice before voting for a VM especially if you are not VM Admin in your Company!

 

 


Wednesday, July 20, 2011 - 1:19:22 PM - John Fox Back To Top
I slightly disagree with the Provisioning as being an advantage, I think of it more as a dual edged sword. Any DBA who needs to suddenly take a server off line to add space or memory is doing a very poor job of managing his system. While it's 'nice' to just add disk space on the fly, these are still tasks that need to be planned and coordinated. And with the limitations of memory currently on Sql Server (i.e. the query/compile space is limited), adding additional memory only improves cache performance at a certain point. Hardly a reason for the additional complexity of a virtual system. Being able to do it on the fly leads to lazy and reactive DBAs, since the price to pay for not planning isn't as great. However, it also has an interesting problem. On more than one occasion, we have 'run out' of disk space, even though it seems we had plenty left. Seems the VM guys were assigning virtual disk space also, and had allocated more virtual space than they had physical space. We've also had servers 'mysteriously' switch to their backup server. The database didn't go down, but all the connections timed out in the process and caused remote tasks to fail. Systems guys upgraded the software, and the problem went away. I don't like those kinds of solutions or problems. I am not against virtual servers. I agree with the statement above about better managing resources using virtual servers. My experience though is that they have increased my daily workload and monitoring requirements because now I also have to monitor the virtual servers. The trade off is that my workload decreased when resource limitations are reached. So all those hours I used to spend planning are now spent monitoring. And I have a whole host of new problems to learn to deal with. DBAs need to take control of their complete environments, or they will find themselves diagnosing problems they don't have the tools for. It doesn't mean they have to do all the work, but they need to be the ones responsible for understanding ALL of the aspects of visualizations. The system guys only know how to do it, they won't appreciate the impacts of issues as well as the DBAs will. Note: We are not in a high-load real-time environment. Most of the database access here is on the back-end systems, with a limited online audience. 99% up-time is nice, but being down for an hour or so is not a big deal. Obviously, for places with high up-time requirements, virtual systems can provide significantly more advantages than they do for our company.

Wednesday, July 20, 2011 - 10:34:07 AM - Caveats Back To Top

Some caveats:

First, don't expect virtualizing 20 with 5 local spindles each onto one physical box with only 12 physical spindles on a SAN to do well from an I/O perspective.  I/O requirements don't change much.

Second, if you're planning on using 2008+ Data Compression on indexes, be aware that rebuilding compressed indexes can easily bottleneck at 100% of processing power even on a quad socket non-virtualized box.  The 8 vCPU limit is a serious limit in this case, though I understand that VMWare 5.x will allow 32 vCPU's, which should help somewhat.

Third, as with all virtualization, don't forget about peaks when you're focussing on averages, particularly concurrent time peaks with tight deadlines, such as year end reporting.

 


Learn more about SQL Server tools