Solved

Install SQL cluster on VMware ESX

Posted on 2008-06-18
17
3,900 Views
Last Modified: 2010-05-18
Hello, I am investigating virtualising our company infrastructure. The project is moving quite quickly and I need to decide on a strategy for our SQL 2000 cluster. I want to virtualise this but people have heard of nightmarish situations arising. From the documentation I have been reading it seems that I can do it as long as there is not too much processor intensive or disk IO intensive activity.

My question is, can this be done and, if yes, what would be classified as 'high cpu or disk IO utilization'? I need to find some thresholds to compare with that offer solid acceptable throughput ranges.

I hope this question is clear.
0
Comment
Question by:michaelcrack
  • 7
  • 4
  • 4
  • +2
17 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21813236
Are you talking about having all of the cluster resource nodes on the same VM machine?
0
 
LVL 18

Expert Comment

by:chuckyh
ID: 21813407
What's your current configuration for your SQL servers? That would give an idea of how intensive your cpu and i/o requirements are. Are they on a SAN?
0
 
LVL 5

Expert Comment

by:Stappmeyer
ID: 21813794
VMware has a specific document that outlines the support of MSCS on ESX.  This document discusses Clustering Virtual Machines on One Physical Host, Clustering Virtual Machines across Physical Hosts and Clustering Physical and Virtual Machines.  

http://www.vmware.com/pdf/vi3_35/esx_3/vi3_35_25_u1_mscs.pdf

There are specific requirements and caveats outlined.  I recommend that you review this in detail.
0
 
LVL 5

Expert Comment

by:HeinoSkov
ID: 21816527
In every case that you need to virtualize a server, you should definately know the requirements of the server that you want to virtualize. By requirements I mean CPU, Memory, DISK IO, Network IO.

The most common issue is DISK IO and in most cases it is not VMware setting the limitation but misconfiguration of the hardware due to that the DISK IO requirement numbers is unknown. Which then results in bad performance.

So my strong advice would be to look into measuring the requirements for your SQL cluster - before you decision whether to virtualize it or not.

Some of the counters that you need to benchmark is:

Ave Disk Bytes/Transfer
Avg Disk Queue Length
Disk Transfers/sec

And the longer time that you benchmark the more precise values would you get. These numbers could you use to calculate the number of disks required for your SQL server / cluster. You need to take RAID penalty into account.

VMware partners have access to tools to help you determine these numbers. Otherwise perfmon could be used.

Regards
Heino
VMware Authorized Consultant
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21816568
Out of curiosity, what do you gain by putting a windows cluster on a VMWare machine?  The whole idea of a cluster is to have separate machines to fail over to in the event that a machine fails.  So, if you have your cluster on a VMWare machine, and the machine goes down...what do you gain?
0
 
LVL 5

Expert Comment

by:HeinoSkov
ID: 21816793
Chapmandew, that is a really good question.

VMware's cluster solution only protects you on the hardware. MSCS is also making it easier to patch your server by failing over the resources. The question is if you want the last feature?

So why virtualize. Well by virtualizing you would gain DR benefits as it is much easier to do backup and restores of those machines if you extend your backup solution with an image level backup of your operating system. You're are by doing this not dependent on which hardware to buy.

Also VMware is soon releasing Site Recovery Manager to fully protect a main site by creating a DR site on a remote location. A clustered SQL running on VMware would be protected by this product if needed.

I agree with you that MS clustering on VMware is provided very small benefits.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21816920
So, if I were to install a windows cluster right now on 2 virtual instances and put SQL SErver on them...I really do get any benefit from doing it, as they'd both go down if the machine failed, right?
0
 
LVL 5

Expert Comment

by:HeinoSkov
ID: 21816936
I would not recommend putting a cluster on the same box - but across boxes....
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 60

Expert Comment

by:chapmandew
ID: 21816947
I gotcha.  Thank you for explaining.  I just wanted to make sure I understood it correctly.
0
 

Author Comment

by:michaelcrack
ID: 21819884
Hello all, thanks for the many replies. We are implementing ESX server to virtualise approximately 30 servers at first, the other servers will follow. I am connected to a san, the current SQL set up uses the san as quorum. The idea is to use 3 nodes in the ESX cluster to allow for redundancy.

I am aware that the 2 individual VM's in the SQL cluster would have to reside on different nodes in the ESX cluster. I have also been benchmarking the SQL server cluster for a few days. What I need to know is - are there some figures that I can compare these benchmarks to? IE. Something that states that if your disk IO or CPU utilisation is above 'X', then you should avoid virtualising.

Stappmeyer, thanks for the link, I am reading the doc now.
0
 

Author Comment

by:michaelcrack
ID: 21819912
Sorry to double-post but FYI, we have about 15 databases, 1 is 20GB (monitoring software - sentinel) and the next biggest one is 4GB. From my benchmarks, IO is not very high but I need concrete thresholds in order to justify the move to management. The choice is being queried by a colleague who has 'heard somewhere' that it can be a bad choice. My thoughts are that this would apply to a massive database with thousands of client connections but I need to find out for sure in order to justify it in the project plan.

Thanks again to all.
0
 
LVL 5

Expert Comment

by:HeinoSkov
ID: 21825975
What are your IO numbers from your measuring?

Typically a 10K RPM disk will do 130 IOPS, and a 15K RPM will achieve 180 IOPS.

Since were using RAID, theres a penalty that depends on the RAID level. Assuming there are 3 reads for every write, the penalty factor for RAID 5 is 0.57 and 0.8 for RAID 1 (or 0+1).

Finally, heres the formula well use:

Total IOPS = #Disks x IOPS/Disk x RAID Penalty factor

So lets put 5 15K RPM disks in a raid 5

5x180x0.57=513

So those 5 disks would provide you with Total IO of 513... If your measuring is higher than this - then you need to add more disks.

The above is an example.

Remember on Database servers you would split the DB and LOGs on seperate storage LUNs, so you need to take this into account as well.
0
 

Author Comment

by:michaelcrack
ID: 21829238
Hi HeinoSkov,

Thank you for the information, judging from your information above (I am working on getting you results) I just want to clarify, the databases are on the SAN and will remain on the SAN, my query pertains specifically to whether I can virtualise the OS. Do you see a problem with virtualising the operating system?

I would have thought that the SQL instance on the os would have a relatively low overhead and the overhead would be on the SAN where it has always remained.

P.S. The LUN is set to RAID 5
0
 

Author Comment

by:michaelcrack
ID: 21829815
Average disk queue length on E: is 0.001 and on C: is 0.035
% Processor Time is 2.904
0
 

Author Comment

by:michaelcrack
ID: 21854010
Any suggestions on this query?
0
 

Author Comment

by:michaelcrack
ID: 21981504
I have done some serious investigation into this and it appears as though opinions are split between those who think MSCS on esx with HA creates too many issues and others that believe that HA does not support application-level support so does not cover service failure within the OS.

Has anyone done an implementation like this or decided against it? If so can I ask for any input that may be helpful in making my decision. I am happy with the resource usage and we are well within the capabilities although this would be our only MSCS on the VI. Perhaps its better to keep this cluster in a stand-alone environment if the business are not willing to accept the change in service level?
0
 

Accepted Solution

by:
michaelcrack earned 0 total points
ID: 22087065
Hi Guys,

I think you can close this question, it has not really been answered but the company are going to get a professional vendor in to do testing on the systems once the project is underway.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

HOW TO: Install and Configure VMware vSphere Hypervisor 6.5 (ESXi 6.5), Step by Step Tutorial with screenshots. From Download, Checking Media, to Completed Installation.
This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This Micro Tutorial walks you through using a remote console to access a server and install ESXi 5.1. This example is showing remote access and installation using a Dell server. The hypervisor is the very first component of your virtual infrastructu…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now