x

Why virtualize SQL Servers

I need some help in changing my thought process about moving SQL servers into a virtual environment for production. My boss has scheduled someone to come in today to talk to us about SQL in a virtual environment and I would like you all's help in understanding the benefit before the meeting. What I can't get over is if you have a 24 core 128 GB ram virtual host, what is the benefit over having a 24 core 128 GB multiple instance server. You still have the same amount of processing power and ram to work with and multiple instances can carve up the resources between instances just as a virtual server can. What is the license requirement for SQL on a virtual host? What is the cost difference between a virtual environment with 6 VM's verses 6 instances on the same hardware? Someone set me straight.

--- Thanks everyone for the very quick responses. I asked this question, left for work, 20 minutes later I have five answers and links to Brent Ozars blog. I have read over his blog picked up a few more tips and got confirmation on a lot of my thought processes. What I need now is to understand how SQL server is licensed on the Virtual host. If all I need is standard edition and I have a 4 slot VM Host, could I just purchase SQL 2008 standard for 4 cpu's and have as many SQL VM servers on the hardware or would I be required to purchase Ent or Datacenter.

-- Thanks again for all the additonal information. It is now 9:30 AM Eastern and I have to go dark for a few hours for a couple of meetings. One of which is meeting with the VMWare folks. I can't thank you all enough for your input.

--Great meeting with the VMWare folks. Nearly all the comments you all mentioned were brought up and confirmed. One interesting thing that the VMWare specialist stated was that it is recommended on production vm host to not issue any more vcpu's than you have physical cpu's. Have others found this to be true? I can understand the logic, but if all my boxes are not using the bulk of their alloted CPU, wouldn't it make sense to put a few more vm's on the host to stack the load? I thought that was one of the concepts of VM clusters.
more ▼

asked Oct 14, 2010 at 04:46 AM in Default

Tim gravatar image

Tim
36.4k 35 41 139

On licensing - check out blog post about Hyper V and licensing

http://blogs.technet.com/b/uspartner_ts2team/archive/2010/02/21/sql-server-licensing-in-hyper-v-environment.aspx

and the word-doc from MS (serach for Witualization and multi-instancing): http://download.microsoft.com/download/1/e/6/1e68f92c-f334-4517-b610-e4dee946ef91/2008%20SQL%20Licensing%20overview%20final.docx

You have to be careful depending on licensing per core or per CAL.

As I understand you would have to have a server license per windows host. On this, you can have unlimited instances.
Oct 14, 2010 at 05:47 AM WilliamD
@TRAD - have edited my answer. @WilliamD - I am pretty sure that document is a bit misleading in places... Not sure... but still!
Oct 14, 2010 at 05:53 AM Matt Whitfield ♦♦
@WilliamD - Just to say why I think it might be - for example on page 30 - there is an example of one VM on a machine with two quad-core processors. It says 'each virtual processor is the equivalent of one core' then uses 4 for the number of virtual processors. Last time I looked, two quad cores was 8 cores...
Oct 14, 2010 at 06:10 AM Matt Whitfield ♦♦
Thanks everyone, you have all given me a wealth of information. This site never lets me down on good solid information. You all are the best. I hope to meet each of you in person if you are attending the PASS Summit.
Oct 14, 2010 at 06:14 AM Tim
@TRAD - would love to attend PASS one day, but it would be mega expensive for the UK contingent. Any chance you'll be @ SQLBits one day?
Oct 14, 2010 at 06:21 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

6 answers: sort newest

In your example you would have less as the virtual environment would draw resources from the hardware. Virtualisation gives benefits where you are bringing lots of systems into one piece of hardware, therefore getting closer to the full use of the hardware resources - 3 systems using 25% of their CPUs capacity moving to a single hardware environment to then use 75% of it is a saving. The same goes for HDD and RAM. Brent Ozar ([www.brentozar.com][1]) has some good content on this, it would be worth reviewing what he has to say about it

[1]: http://www.brentozar.com
more ▼

answered Oct 14, 2010 at 04:52 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

(comments are locked)
10|1200 characters needed characters left

Numerous good points have been made, but perhaps I can add a few more.

Vms give you the option of running multiple different operating system environments on one machine even at the same time. This is generally much more significant in testing/development then it is in production, but for testing/development it can be a huge convenience. Even in production, it is a nice option to have for groups that have a need for multiple different configurations but only want to maintain a limited amount of hardware.

Vms can also give you a good way of sandboxing applicaitons that are running on the hardware. For instance, some companies do not want to support separate hardware for the website and database. If they are hosted on the same physical machine but different vms then you can still sandbox them and compromising one may not automatically compromise another. In short, when configured properly they can add one more layer to your security plan.

Also, others have mentioned it, but I want to reemphasize that there is a price in performance and complexity to be paid. This price is very often worth it, but vms should only be used when this price is fully understood and accepted.

more ▼

answered Oct 14, 2010 at 10:39 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

It's probably worth noting that the resource governor in SQL Server doesn't govern I/O - so an advantage of VMs would be the ability to throttle I/O in case you need to meet SLAs on a system with guaranteed performance levels. There is some word on the street that I/O throttling is coming in Denali, but there's only one of us here who knows that, and they can't tell us!

Edit -> Regarding virtualized environment licensing - from the horse's mouth:

For Enterprise Edition there is an added option: if all processors in a machine have been licensed, then the customer may run unlimited instances of SQL server 2005 on an unlimited number of virtual operating environments on that same machine.

Edit 2 -> More [from the horse's mouth][2]:

With Workgroup, Web, and Standard editions, for each server to which you have assigned the required number of per processor licenses, you may run, at any one time, any number of instances of the server software in physical and virtual operating system environments on the licensed server. However, the total number of physical and virtual processors used by those operating system environments cannot exceed the number of software licenses assigned to that server.

However, with SQL Server 2008 R2 they have changed it - the 'unlimited number of virtual operating environments' bit now only applies to Data Center Edition - i.e. double dollar.

[2]: http://download.microsoft.com/download/1/e/6/1e68f92c-f334-4517-b610-e4dee946ef91/2008%20SQL%20Licensing%20Overview%20final.docx
more ▼

answered Oct 14, 2010 at 05:09 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

wouldnt be surprised to hear a whole lot about what coming with Denali during PASS ...
Oct 14, 2010 at 05:13 AM Fatherjack ♦♦
Ok, so with that nugget of info, if we bought per processor license for a quad cpu box, I could run as many SQL server VM's as possible with a 4 cpu license for SQL 2008 and below regardless of edition. If I only needed standard then I could get a 4 cpu standard 2008 license and be good. For 2008 R2, the unlimiteed support would have to be DC ed.
Oct 14, 2010 at 06:01 AM Tim

In R2 if you license the socket (say with 4 cores) with the enterpise license (only one CPU lic needed) then you can have upto 4 virtual services with that license..

see : http://download.microsoft.com/download/2/7/0/270B6380-8B38-4268-8AD0-F480A139AB19/SQL2008R2_LicensingQuickReference-updated.pdf
Oct 14, 2010 at 06:07 AM sp_lock
@TRAD - No - for enterprise edition, you could license the 4 physical CPUs. For Standard edition you would need a processor licence per virtual CPU (i.e. one per core or two per core with hyper threading). At least, that's the way I read it.
Oct 14, 2010 at 06:12 AM Matt Whitfield ♦♦
If it were down to me, I would just licence physical cores. You'll get less work done with a whole load of VMs, and you could just run multiple instances instead. Keep it simple...
Oct 14, 2010 at 06:51 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

To add to Grant's point about DR, I understand that one advantage of VMs is the ability to have what I consider to be a "hardware cluster". Not so in your case, but imagine having two monstor machines with the VM-Suite spanning them, this would give you the ability of running a server on MonstorA with the ability of failing over the hardware layer to MonstorB. The other advantage in this situation is beefing up your hardware transparantly - as long as you have the right software - you could do the hot-add for memory and CPU if your instance needed beefing up by adding MonstorB into the mix.

Imagine the ability of upgrading/replacing the machine you are talking about in the future. You would buy your new hardware, add it to the VM-Suite, move the SQL Server VM to the new hardware and you're done. This can be completely online (if your hardware/vm-suite supports it) and can be brilliant for a high-uptime instance.
more ▼

answered Oct 14, 2010 at 05:02 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
VMs offer a whole bunch of functionality above & beyond what you can get with simply instances of SQL Server. First off, you can control behavior at the OS level, rather than simply at the instance level. You can also take advantage of some of the disaster recovery technologies offered by VMs. There are places where VMs shouldn't be used, if you've got systems that are already stressing big iron, putting them on VMs will NOT help you. But overall, the flexibility and fine grained control that VMs put into your hands make the investment worth it in most circumstances.
more ▼

answered Oct 14, 2010 at 04:49 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x14

asked: Oct 14, 2010 at 04:46 AM

Seen: 8014 times

Last Updated: Oct 14, 2010 at 12:41 PM