SQL Server 2005 on VMware

[eddiefdz]

Hello Folks,

I know there has been plenty of talk about SQL 05 on VMware but i can't seem to find a solid answer. Some say yes, other say now but i am not sure yet. I am in the middle of an implementation and I want to know what the deal is. Can I or Can I NOT run SQL 05 on vmware? If anyone says yes, I would like to know how and what they are using it for and in what capacity. Also, are there any consultants out there that have tried this that are willing to put their money where their mouth is? Please let me know. This is getting extremely frustrating. Thanks!

Eddie

[dpomeroy]

We have SQL 2005 running in several different VMs, what are the problems/issues you are running into?

We don't run any high load or mission critical databases in VMs, those typically go on physical clusters.

[eddiefdz]

Well I am not running into any problems yet, I am just wondering wether it would be good practice to run a production SQL Server in VM? If anyone out there is doing it, then I would like to know what their performance is like. Thanks!

[esiebert7625]

In general SQL Server is a good candidate to use vSMP, it has built in multi-threaded structures and a high scalability ratio. Microsoft recommends deploying SQL Server on SMP systems with at least two CPUs.

Also checkout these links...

http://www.microsoft.com/sql/howtobuy/virtualization.mspx

http://www.microsoft.com/technet/community/events/vs/add-62.mspx

[johnretlaw]

Eddie:

Did you get any help anywhere? I'm in the same situation.

I have a new client that is trying to force VMWare down my throat and our sql server 2005 db is a little more than intensive...

On bare metal, it usually is starving for resources (at times) on a 4 way (Hyper-threaded) 8GB Ram with an external cage running 15k drives.

I need proof that this will or will not work with this type of vldb resource hog.

Does anyone have a white paper? Benchmarks with DB/Usage stats?

Anything other than "sure...it'll work...duh..."?

Besides the fact that MS doesn't actually support SQL Server on VMware....this should be enough, but it's not...I need some performance proof either way before I can let this happen.

Thanks...John

[TomHowarth]

Why are you being so negative about this. you seem to have forgot that it is your client requesting this. remember YOUR CLIENT!. if you want to keep them as a client, be flexible, I have installed SQL 2K5 on ESX and found better preformce in most cases, the additional advantages of DRS and HA that VI3 provide in my opinion add not detract from deployment on virtual hardware.

instead of shutting the door to the inovation that your client is brining to the table embrace it, and you will find that you will increase your client base Virtualisation is here to stay and will only become more prevalent you and your company risk being left behind.

if your DB is that intensive, then yes keep it physical. but let the customer decide. if you do not dip your toe in the water your client will go to a competitor.

from a personal view point. I am actively guiding my client base away from vendor that are anti-virtualisation.

[TMPalmer]

I do not think anyone is opposing using VMware. Quite the opposite. I am a DBA and I will not subject any production physical machine to a VM if it can't gaurantee at least the same level of service that the physical is already delivering.

I also want to see the proof before agreeing to go virtual.

If I have a physical that is running SQL Server 2000 Enterprise on a server with 4 physical 2.0 GHz processors with hyperthreading and 4 GB of gauranteed Ram, how many of these can I place on a virtual before there are issues with performane (knowing that SQL Server is a resource Hog). How big of an ESX server would be needed? Since they are Databases with large DBs, should there be designated gigabit NIC cards to avoid bottlenecks. Should processor(s) de designated to one image to ensure we do not lose performance?

SQL Servers are a whole different animal and require resources that may be needed to ensure t is running properly. Our vendor wants the same thing we do, to save them cost but at what cost to performance and reliability?

Lets go bck to the question posted in the post before:

On bare metal, it (SQL SERVER)usually is starving for resources (at times) on a 4 way (Hyper-threaded) 8GB Ram with an external cage running 15k drives. Hom much can this span?

I need proof that this will or will not work with this type of vldb resource hog.

Does anyone have a white paper? Benchmarks with DB/Usage stats?[/i]

Message was edited by:

TMPalmer

[Ken_Cline]

Not everything is suitable for virtualization - and not everyone virtualizes for the same reasons.

\- If you have an application that is resource constrained on a "comparable" physical machine, it will be more resource constrained in a VM.

\- If you truly have a VLDB, you are often better off consolidating by implementing a DB farm/cluster rather than by virtualizing

\- If you are looking for encapsulation, HW independence, new DR capabilities, etc. then perhaps you will virtualize even though performance isn't "the best"

So - yes, there are people running large DBs in VMware. Frequently, the DBMS is the only VM on the host - and it's tehre for reasons other than simply consolidation.

You need to evaluate the business requirements and decide what your priorities are and how you can satisfy them.

[fontyyy]

Besides the fact that MS doesn't actually support SQL

Server on VMware....this should be enough, but it's

not...I need some performance proof either way before

I can let this happen.

Microsoft doesn't support anything on VMware unless you have premier support, "Support policy for Microsoft software running in non-Microsoft hardware virtualization software"[/url], the important bit;

"Microsoft does not test or support Microsoft software running in conjunction with non-Microsoft hardware virtualization software. For Microsoft customers who do not have a Premier-level support agreement, Microsoft will require the issue to be reproduced independently from the non-Microsoft hardware virtualization software. Where the issue is confirmed to be unrelated to the non-Microsoft hardware virtualization software, Microsoft will support its software in a manner that is consistent with support provided when that software is not running in conjunction with non-Microsoft hardware virtualization software."

Whatever, I run SQL2005 on ESX, only a few very low use DB's for some student retention and achievement software data reports and manipulation and a front end that can show some nice graphs. Basically a project with near zero money attached to it that the sole guy in charge of producing these figures found. It works perfectly and I don't look at it from one month to the next.

[crazex]

I have been running production SQL 2005 boxes in VI3, for about 6 months now, with absolutely no problems. Our SQL DBs are not overly transactional, however, we do very large data warehouse builds, and we've actually been able to under-spec our servers a little bit.

-Jon-

[Dav35]

I'm in the middle of two sets of vendors - on pushing VM and one saying Cluster for a SQL and Exchange cluster.

When you say you are not overly transactional, how many would you do in a day for example

[tlyczko]

How about whether one would virtualize a timesheet/payroll SQL Server (think getting ready for payday!!) vs. a SharePoint Services backend database server (50-60 users, relatively low usage)??

Thanks, Tom

[amit40]

We Are running SQL 2005 & SQL 2000 in our Production VMware ENV from last 6 months .

We have also installed SQL 2008 with 64 bit in VMware ..works perfect

[ReverendDeuce]

For me, it all comes down to disk IO.

We have huge SQL databases that are very IO-intensive. There is no way in hell we'd make them into VMs because of this very reason. We can't take the 10% disk performance hit using VMDKs, and we certainly don't want to map raw LUNs. While NPIV shows real promise, it's still a bad idea to put something that is bound by IO into a virtual environment.

A bit of math can help:

15K RPM = 180 iops

10K RPM = 150 iops

These are the IO operations per second. So a 15,000 rpm disk can do about 180 operations per second. Your RAID type will determine cumulative gain or loss of iops. RAID5 results in a 4x increase in iops needed per write action because it has to write parity.

Now, open your SQL server and start up performance monitor. Start watching the Physical Disk counter "Disk operations per second" or somesuch (not the byte counter, just the operations counter). Also, add in the "Current queue" counter. Use individual counters for each physical disk that your SQL data is on.

Now just watch. Look at see where IO queues are forming. Do you have any? A little bit of queue is fine, but if you have consistently high queues then you have some bat bottlenecking going on and should re-evaluate how your volumes are configured and/or how your data is laid out across the disks.

So the answer is YES, of course you can run SQL 2005 on a VM. But you really need to make sure that your disk subsystem is up to par for keeping up with your SQL workload given the overhead that virtualization adds. Obviously, you also need to worry about memory and CPU allocation -- so I am leaving that part out as to me, disk is the biggest factor in making this decision for you.

[northwest]

Another consideration is SQL licensing. We license by CPU, so when you have a VM, each vCPU only gets you one core to process on. On physical hardware, one CPU licence can get you four cores.

We use VMs for test servers that qualify for MSDN licensing only, or light load production where the database is not supported with others.

Where we can, we consolidate databases on physical servers.

SQL licensing considerations far outweigh hardware costs and ESX server licensing.

Chris