I talked about why sysadmins want to virtualize servers, and today it’s time to talk about why you as a DBA or developer might want to virtualize some of your SQL Servers.
Virtualization Means Cheap High Availability
In a perfect world, every SQL Server would be a cluster on a SAN with instant failover. This isn’t a perfect world, and we can’t afford to implement that level of protection for every server in the shop.
Virtual SQL Servers provide a level of high availability without the expense and hassle of clustering. In my last post, I explained that sysadmins love virtualization because they can easily move guest servers around from one piece of hardware to another. As a DBA, I love it for that same reason: suddenly, if the underlying hardware fails, I’m not sweating bullets. If the RAID card fails, if the motherboard fails, if the memory fails, if the network card blows chunks, you name it, it’s not my problem. The sysadmin simply boots up my SQL Server on another virtual host, and I don’t have to deal with screaming users.
I especially like this option for development and QA servers. Management is rarely willing to implement expensive high-availability setups for development environments, but when the dev server goes down, the developers and project managers are kicking my door down. The PM’s scream about how there’s so many expensive developers sitting around idle waiting for the server to come back up. Virtualization avoids this problem altogether.
Virtual SQL Servers can React Quickly to Changing Needs
Capacity planning is hard work. How much speed do we really need for a new application? What happens if our estimates are wrong, and we suddenly need a whole lot more oomph? What happens if we overbought, and we’ve got all this expensive hardware sitting around idle because the application never caught on with end users?
Virtual servers can be easily scaled up or scaled down. Simply shut the virtual machine down, change the number of CPUs and memory it has with a few mouse clicks, and boot it back up again. Presto change-o.
We can even move storage around. Say we initially decided to put the server’s data on a cheap RAID 5 SATA array, and now our users have fallen in love with the application and they’re adopting it like crazy. We can shut it down, move the virtual hard drives over to a faster RAID 10 SAS array, and boot it back up again. Presto change-o, rapid storage performance improvements without long downtime windows to backup/restore.
I loved this approach because it let me keep old SQL Servers around as long as necessary. For example, we decommissioned an old help desk database server, but I told the help desk folks they could keep it around as long as they wanted. It hardly used any resources at all, because nobody queried it unless they had a question about an old help desk ticket that didn’t get transitioned correctly to the new system. I set the server up with one virtual CPU, 1gb of memory, and put it on cheap RAID 5 SATA. The end users loved me because I could provide the service for no cost.
Virtual Server Performance Isn’t That Bad
Virtualization has a really bad reputation because in the beginning, the software really did suck and admins didn’t understand how to use it. Raise your hand if you installed VMware Workstation on your desktop years ago, and you tried to run multiple operating systems on a machine that barely had the horsepower to run Internet Explorer. I was there, and I know how it was.
Things have changed: the software’s gotten much better, memory became absurdly cheap, and there’s resources to help use it better.
The 2008 book Oracle on VMware by Bert Scalzo is a good example. Bert’s one of the Oracle experts at Quest, and he wrote this to help DBAs understand how to implement database servers in a virtual environment. It does a good job of explaining enough about virtualization to make a DBA comfortable with the technology and avoid some common pitfalls. Years ago, books like this weren’t available because the technology was changing so fast that best practices were wrong as fast as they were written.
You can still get really bad performance with virtualization, just as you can get really bad performance with SQL Server on native hardware. But follow a few basic guidelines like avoiding oversubscription, properly configuring networking & storage, and using the right OS configurations, and you can get perfectly acceptable performance. Yes, you’re still going to lose a few percent of overhead – but with today’s redonkulously fast CPUs and memory, I’m more concerned about optimizing T-SQL code than I’m concerned with losing 5% of my CPU speed due to virtualization. It’s all about priorities, and that small performance loss is just one piece of the picture.
Virtualization is All About Give and Take
The economy has gone down the toilet. Virtualizing non-mission-critical SQL Servers gives us the chance to cut costs without cutting service levels, and furthermore, gives us the chance to show management that we’ve got skin in the game. If we insist that every single SQL Server is too important to be virtualized, and that there’s no way we can save money, then management just might look at us as part of the problem. Be part of the solution: be proactive, find SQL Servers with low utilization levels, and offer them up as the first phase.
My approach was to start with the SQL Servers that I frankly didn’t really want to survive on their own anyway. I had one particular application that had always demanded its own standalone SQL Server with no other applications on it. The server was barely used, and it was a completely out-of-date and unsupported version of SQL Server. The application manager wouldn’t allow me to patch it, wouldn’t upgrade to a newer version, and didn’t have the budget to move to newer hardware.
Virtualization administrators have slick tools like VMware Converter and Vizioncore vConverter to automate the physical-to-virtual (P2V) process. These solutions take a backup of the physical server and clone it into a brand-new virtual server. When the virtual server starts up, the new virtualization drivers are installed automatically, and the server is up and running smoothly without time-intensive application reinstalls. I’ve blogged about virtualizing SQL Servers with P2V and I’ve performed it many a time – it’s a great way to get rid of hardware dinosaurs.
Even better, this whole process doesn’t require work from the DBA. The time and effort is all put in by the virtualization administrator. If your sysadmin is dying to try virtualizing a SQL Server, you can point them at your newly chosen dinosaur and tell ’em to have at it – you don’t have to be involved. That’s the best of both worlds: management will see you as a part of the solution, but you won’t actually have to do any work.
16 Comments. Leave new
Switching from the virtualization administrator to a SQL Server DBA, this has been my take on it (heavily influenced by a wise DBA manager who also has sat on the systems side):
– If you don’t impact my performance negatively, we’re good.
– If you don’t impact my reliability negatively, we’re good.
– If you do happen to impact either or both of those, but the business has agreed to live with it because of the other advantages virtualization gives, we’re good.
At the end of the day I tell you what I need and you give me the platform. If the platform performs to expectations, why should I care if it’s physical or virtual?
Good Description.
My concern is that once we go VM for SQL Server, it’ll involve more people than just us (the VM people)
Needs more approval for restart, needs email list to investigate performance issues, etc…
Plus, not all SQL Server DBAs are fit for VMWare ESX admin. I have had plenty experiences with MS Virtual Server and liked it enough
but now I want to get into Hyper-V (not interested in ESX)
Sometimes DBAs don’t even get a box to play with on their own, or given crappy old servers.
When I get a beefy physical servers, I want to setup Hyper-V on it and test out Windows 2008/SQL 2008 without worrying a thing
KBK – you summed it up really well! That’s my approach to pretty much anything SQL-related, not just virtualization.
Great post! Read my article on what this means for SharePoint at http://www.sharepointmadscientist.com/Lists/Posts/Post.aspx?ID=28
Any thoughts on virtualizing SQL2008 on VMware? The current database server(s) have 32GB of RAM and 1.5TB of storage and 4 dual-core processors. Any words of wisdom before we undertake this project?
Tom – keep reading, and there’s suggestions in the next parts of the article about what makes a bad candidate for virtualization.
We are considering virtualizing SQL Server 2000 on the free version of XenServer running on DELL PowerEdge 2950’s
We’d plug in a JBOD for our RAID Array and off we go. We are thinking we’ll get some baselines by virtualizing some simpler apps first and working out the kinks with configs and resource allocation over most of 2010 and then do the virtual SQL implementation.
Any thoughts?
Generally speaking, virtualization is a bad idea on JBODs. You lose a lot of the advantages you get from abstracting away the hosts. One of the biggest advantages of virtualization is the ability to boot up any guest on any host, thereby removing problems due to host hardware outages. Exactly what are you trying to gain?
If you are asking about why we are looking at using JBOD’s….because that is a solution recommended to us. We want an economical solution that is resillient and offers some redundancy (RAID) where we could keep a spare drive or two on hand. We were thinking that not having the drives in the PE2950’s was the way to go for scalability. We don’t have a large SQL install. We want to get our feet wet with virtualization by migrating our Exchange install and then upgrading it on the virualized environment. If all goes well we’d move one or two IIS-based smaller apps as their own environments… What would be a some good storage options for this sort of install?
If you are asking about why we are looking at using JBOD’s….because that is a solution recommended to us. We want an economical solution that is resillient and offers some redundancy (RAID) where we could keep a spare drive or two on hand. We were thinking that not having the drives in the PE2950’s was the way to go for scalability. We don’t have a large SQL install. We want to get our feet wet with virtualization by migrating our Exchange install and then upgrading it on the virualized environment. If all goes well we’d move one or two IIS-based smaller apps as their own environments… What would be a some good storage options for this sort of install?
Doug – wow, ouch, no, I would not call that resilient. I’d avoid virtualization with JBODs at all costs. Consider picking up a shared storage environment. If you want to stick with the Dell gear, they acquired Equallogic and they’ve been updating that iSCSI SAN gear. You can get into that fairly inexpensively, and then you’ll be able to survive if one of your hosts dies. You can just boot up the guests on another host. Otherwise, you’re not adding resiliency – you’re actually getting even worse by putting all of your eggs in one basket. If that one JBOD fails, if the cable fails, if the RAID card fails, or if the PowerEdge fails, suddenly you’ve lost all of your guests at once. That’s dangerous.
If you’re using Exchange 2003, I would also highly recommend against virtualizing that first. Start with the web apps with lower storage throughput requirements. Exchange is notoriously tough on storage throughput, and you’ll be very dissatisfied if you virtualize that first. Hope that helps!
Egads! Thy links at the end of the post don’t work, Sir Ozarington (of the New Hampshire Orzaringtons).
Please could you have a look? I’d love to dig into that 3 hour vid-fest!
PS: love your work mate…
G-man – thanks, glad you like the work! Unfortunately the video’s no longer for sale.