Here’s some of the reasons companies usually virtualize their SQL Servers:
- Cost savings on hardware
- Cost savings on Windows OS licensing
- Cost savings on SQL Server licensing
- Protect against the failure of a single hardware element
- Leverage extended features for Disaster Recovery
- Automatic load balancing across multiple hosts
- Easier hardware replacement/migration
When we perform a SQL Critical Care® on a virtualized SQL Server, we often ask, “Are we actually getting those benefits?”
1. Cost savings on hardware – do you find yourself putting one SQL Server guest on each host, isolating them to make sure they get the performance they need? If so, you’re not actually saving money on hardware.
2. Cost savings on Windows OS licensing – as a standard, some companies license all their virtualization hosts with Windows Server Datacenter Edition in order to get unlimited virtualization rights. However, if you’re only running one guest per host (or just a few), then you’re not saving money here either.
3. Cost savings on SQL Server licensing – for this one, you’ve gotta do a little bit harder work. Add up the licensing you’re spending now, and look at what it would take to run similar instances on bare metal hardware. Keep in mind that you can still buy dual-socket, quad-core servers that are insanely powerful (768GB RAM, dozens of SSDs), thereby keeping your SQL licensing lower.
4. Protect against the failure of a single hardware element – on the free versions of most hypervisors, you don’t get automatic failover protection. You can manually start up a guest on another host with some human intervention. Is that enough for the business, or are they assuming it’ll all happen automatically with only a minute or two of downtime – even when you’re not around? Or even worse, do you not have enough hardware horsepower to start up your biggest SQL Server guest somewhere else if its host fails? Or, heaven forbid, are you using local SSDs with virtualization, thereby missing the entire ability to move guests around?
5. Leverage extended features for Disaster Recovery – VMware and Hyper-V have killer features (and third-party app extensions) that make it easy to replicate a guest from one site to another. Are you using those, or have you given up because SQL Server’s data change rates are too high, and your network can’t keep up?
6. Automatic load balancing across multiple hosts – VMware’s Distributed Resource Scheduler (DRS) will automatically shuffle VMs around between hosts based on resource utilization. It’s an amazing way to react to performance issues with less human intervention. You should be using it.
7. Easier hardware replacement/migration – because SQL Server licensing is priced by the CPU core, and it’s super expensive, many shops choose to improve their virtualization host hardware annually. Whenever they need more capacity in their VMware or Hyper-V clusters, they drop in a couple of new hosts, vMotion or LiveMigrate the most expensive per-core guests over to those hosts (thereby taking advantage of today’s faster processors), and then give everybody else the hand-me-downs. It’s easy to do even live during the daytime. However, some shops are still running their SQL Servers on CPUs that might get featured on Antiques Roadshow.
If you’re not leveraging at least some of these virtualization features, and you don’t plan to…then what was the point of virtualizing to begin with? Jump on in – the water’s fine!
11 Comments. Leave new
I can honestly say, that the organization I work for did their do diligence before implementing VMware, taking into account all aspects for cost savings and protection of each guest, utilizing DRS and Vmotion. I feel so blessed to work in such a talented and thorough IT department. Yes, the water is nice!
I virtualized 80% of our SQL infrastructure (me the sysadmin / vmware admin / san admin) and my DBA’s have reported better performance than running bare metal. They also moved from DAS to SAN as part of this migration (i know shared storage vs. DAS is another point you bring up occasionally).
On top of that, now they can actually have dedicated SQL instances for each application, and have a dedicated environment (Dev, Stage, UAT and Prod) for each application.
If a virtual environment is properly built (including its related networking, storage and compute) most average SQL instances will likely perform just fine. Unfortunately I think virtualization and shared storage get a bad rap from either poorly configured / spec’ed implementations or very stingy admins
We have 7 Dell r820’s, quad 8 core procs, and 768GB or RAM. 4 10g ports per esx host (all wire speed) 2 for general network and 2 for storage. We have 4 SAN’s capable of 60K random read, 36k random write and 2GBps read and 1GBps write. The sans aren’t dedicated to SQL, but we haven’t found that to be a problem yet.
I’m biased, but virtualazation is a good fit in 90% of all solution, even if performance is of concern. Its all about properly scoping the solution. In our case we are hitting most of your points, but even if we weren’t, I still see a good fit for virtualization. its the fringe cases where virtualizing doesn’t make sense.
Eric – that’s great to hear! It’s not unusual to hear that performance is better in a new environment regardless of the underlying hardware though – often we learn lessons and build better environments the second time around, heh.
Well that of course and making sure as a SysAdmin we collborate the DBA’s when it comes to resource requirements. I have a few systems with 16+ vCPU’s and 256GB of vRAM. No skimping on our part, if its needed, its needed.
Brent…great article !
Binut there s one point that I would like clarification on… let’s say we wanna patch a SQL that’s virtual…and the patching results in an unpleasant situation where stuff s not working post the install…how safe is it to revert to a VMware snapshot. (assuming that we asked the VMware guys to take one with SQL services off , before we started our patching).
This again..is not specific to patching.this may apply to cases where tiny applcations live on the sane machine as their SQL servers and the app upgrade goes badly…
p.s: I ve read somewhere that M.S doesn’t offer support to SQLs restored from VmWare Snapshots :/
San – generally speaking, I recommend that people back up data, not servers. If uptime is important to you, have a second server that you can fail over to.
I would concur with what you’re saying. However to more specifically answer the question from san, this is my thought.
Snapshots need to be used with care for any system, not just SQL. There are a few considerations when you take a snapshot.
1. is the SQL server in an AAG or is there a form of TL shipping occurring? Meaning is there any data replication going on? If so, do not use snapshots unless you take the entire replication set down and snapshot when its shutdown. In the case of reverting, you’d need to revert the entire replicated infrastructure. Restoring just one node out of x would likely break / screw up your data synchronization (THIS is why MS says not to snap certain systems). If you goal would be to restore the primary and re-replicate your secondaries that’s another option, but a painful one. (might as well just do a backup / restore like Brent mentions).
2. Are you willing to lose any changes between when the snapshot occurred and when you decide to rollback? If not, snapshots aren’t a good option.
2a. if you are ok with the data loss, you now need to make sure the snapshots properly quiesce (spelling?) SQL (powered on snap), otherwise your DB’s will not be in a clean state. Best course of action is shutdown, snap, power on. Vmware if the tools are loaded will quiesce SQL “if” the check box is selected. You can look in the app log to verify that the DB’s were froze during the snap. A similar thing should occur if its a “San” snapshot too.
3. There are options to exclude disks from snapshots, for example, you could snap the OS only, but then you need to make sure your systems DB’s or user DB’s arean’t on the OS drive.
In the end, you need to make sure your VMware/SAN admin has a very strong understanding of what your goals are and what the caveats are. If you don’t get the feeling that they can help you out of the situation, then rely on SQL dumps, not the snap.
thanks @ Eric & @ Brent.
@eric : our VMware guys insist that if SQL services are off , the ‘quiesce’ ing isnt an issue.
ofcourse, we don’t trust them and do db backups each time…but we haven’t seen an issue with going back to a VMware snapshot until now (touchwood).
We ve used this about 20-25 times until now.
One more thing our VMware guys want to start doing is use templates of a production build to build all new servers. For ex : have a SQL server machine with all maintenance jobs ( and no user data). every time someone requests a new SQL server, we use the servers template to create a new one and just modify the servername and adjust storage according to the new db’s requirements.
we tried this and it works absolutely fine.Saved us time as we dint have to do the SQL install or setup our maintenance jobs.(as it was already setup on the server whose template we used)
but this sounds just too good to be true. is there something we’re missing? it feels wrong 🙂
San – there’s a lot of great questions in here, but this is a little beyond the scope of things I can answer quickly in a blog comment. Instead, here’s what I’d recommend: isolate your questions individually, and post them on http://dba.stackexchange.com with as much supporting info as possible. Enjoy the journey!
Great article Brent. Reading your points about VMWARE HA and DRS, it would seem to me your are primarily talking about stand-alone SQL Server instances. What’s your take on running Failover clustered SQL instances with Shared Storage RDM’s or AlwaysOn implementations on Virtualization? Would you favour replacing Native SQL HA solutions with the Virtualization software HA solution? Or would you typically look to avail of both to maximize protection?
Rich – here’s my thoughts on that:
https://www.brentozar.com/archive/2013/01/vmware-ha-is-not-database-server-high-availability/
https://www.brentozar.com/archive/2012/09/why-your-sql-server-cluster-shouldnt-be-virtualized/