RDM versus VMFS for SQL Server 2008 R2

[sibdba]

We are in the process of migrating our SQL Server’s (stand-alone & Clusters) to a new virtual environment using VMWare. Could you let me know what are the pros and cons of RDMs versus VMFS storage configuration? What type of storage options is recommended for physical and SQL Clusters and appropriate reason for the same? I am using Windows Server R2 and SQL Server 2008 R2.

[BenConrad]

Here is my $.02:

In vSphere 4.x you are limited to 2TB VMFS datastores and RDMs.  I find that for larger SQL servers we end up with a 2TB VMFS datastore that has say 500GB free (1.5TB used by the SQL server) and when I need to add another disk(s) say 600GB I can't!  I'm not a big fan of extents so I don't go that route.

With VMFS 5 it's going to be much better in this respect as my VMFS can be 2TB+, I will be converting many of my RDMs to VMDK in the future.

The advantage of using RDMs is that you just create some LUNs and then format, no worries about having to fit them inside of the VMFS volume.  If the RDM is 'virtual' you can snapshot them, just make sure you have enouth space in the datastore where your .VMX lives.  One disadvantage is that for each RDM you can  end up using more host sessions on your SAN, in larger enviroments this can become a problem as you max out your SAN host connections.  You also can't sVmotion RDMs like you can with VMDK's.

There is really no discernible performance difference between the two.

For multi-host cluster you must use RDM (physical or virtual).

Ben

[sibdba]

Many thanks Ben..!

I have a business requirement to setup SQL Cluster on two different ESX Hosts to ensure high availability. Meaning if for any reason one of my ESX Host goes down, SQL will still up and running on the other Host. In this case, I have to go for RDM, am I correct on my assumption? We may go with VMFS 5 in near future, so in this case VMFS may be the right direction.

[vcpguy]

You will have to use MSCS solution. I am not big fan of MSCS just because there are lot of administrative headace involved.

[BenConrad]

Yes, rdm is required for running a mscs cluster across multiple hosts. vMotion is not supported...

[sibdba]

Thank you very much. Now I am in a better position to decide whether to go for RDM/MSCS or not.

[rickardnobel]

Here is a good document about how to set up MSCS/MS Failover Cluster in vSphere and the requirements for RDM and other special configuration:

http://www.vmware.com/pdf/vsphere4/r41/vsp_41_mscs.pdf

[Argyle]

What’s driving the MSCS requirement? VMware HA does the same thing but often at cheaper Windows licenses and easier administration if you keep it as a single SQL server installation instead of a cluster. If an ESXi host goes down any other ESXi host will start the SQL Server VM up again. With VMs the OS start real fast these days so it's in principle the same downtime as with MSCS (where the SQL Services restart).

In both cases you will have interruption to the SQL Service, MSCS doesn't help there. So for hardware problems they are basically equal.

There is only one reason for MSCS that I can see it and it is if you want to fail over SQL Services to another server while you patch the operating system on the first server. If the business is ok with the additional downtime in this scenario, it will save you some headache to go for standalone SQL VMs instead of running MSCS inside vSphere.

Some pros for standalone SQL VMs:

You get the benefit of vmotion (this is even better than MSCS since MSCS stops services, vmotion doesn't) when patching hardware, you don't need people that are knowledge with MSCS, more junior admins can handle stand alone SQL servers than clusters, you can standardize, you don't have to think about "Argh, forgot about the MSCS, now we need to plan more for the service window or storage setup" etc.

It depends on your business requirements of course what to choose but it can be worth it to question if you really need MSCS.

[sibdba]

Thanks for your update. There are two main reasons why I am thinking of implementing MSCS on vSphere:

1. My current physical system is configured with MSCS

2. If for any reason one of my ESX host goes down, SQL Server should not be impacted

Per my understanding vMotion happens within the ESX host (but not across hosts). So, do you feel without MSCS I can still maintain the high availability of ESX host failures?

[rickardnobel]

sibdba wrote:

Per my understanding vMotion happens within the ESX host (but not across hosts).

vMotion is across ESX/ESXi hosts, but requires both hosts to be alive and well.

[Argyle · Accepted solution]

1. This can be a reason, though not a strong one in my view

2. MSCS does not solve this. If for any reason one of your physical MSCS servers goes down today, SQL server will be impacted.

So nr 2 is not a valid argument. With MSCS when a physical server goes down, the SQL services go offline (impacting users) and then restart on the other physical server. SQL Server services are only active on one physical server at a time.

When a ESX host goes down, you have the same impact, that SQL Servers are stopped and restarted but instead of services on OS level being stopped/started, you have a restart of the virtual machine while another ESX host brings it up. The impact for the end user is the same as for MSCS which is an abruption in service.

VMware HA gives similar benefits as MSCS. Except for the OS patching scenario mentioned earlier (where you still need a short downtime to transfer SQL services to the other server).

When it comes to VMotion you can utilize this when you are doing hardware maintenance on a ESX host (or any maintenance). You transfer the standalone SQL Server VM to another ESX host with no down time. If you would do this with MSCS you will get downtime for end users even if it’s short. MSCS in practice stop the SQL Services on one server and starts them on the other.

It depends where you see the most benefits:

ESX pro: No downtime for hardware maintenance due to vmotion option

ESX con: If you patch the OS, Stand alone SQL Server might be unavailable longer if patches impact SQL services.

MSCS pro: You can failover SQL Services (with interruption though) to another host during OS patching

MSCS con: Always abruption of service for hardware maintenance due to SQL services stop/restart when you failover

[sibdba]

"MSCS does not solve this. If for any reason one of your physical MSCS servers goes down today, SQL server will be impacted."

I have a question on the above comments. In a real-life physical SQL Cluster scenario, if one of my node goes down SQL will be still up and running on the other node (as if a single node cluster). We normally take the faulty node out of the Cluster and troubleshoot it. Once node-2 is ready then 'add the node' to the existing Cluster. Is this something not expected in a virtual env?

[Argyle]

"In a real-life physical SQL Cluster scenario, if one of my node goes down SQL will be still up and running on the other node"

This is not correct. The way MSCS works is that the SQL service is just active on one node at a time. The SQL services (visible in Administrative Tools -> Services) exist on both nodes but they are only running on one of them. This is how MSCS is designed.

When a node goes down, or you do a manual failover, the SQL Services are stopped (interruption for end users) and then started on the other node. It's the same thing as if you would stop/start SQL in Administrative Tools -> Services on a single server installation.

So you can say the SQL Server is "up" on the other node, but at a cost of a stop/start of services. This you have to do any time you do hardware maintenance in MSCS. With ESX and single SQL servers, you could instead vmotion to another ESX host with no downtime on SQL servicies.

The benefit of MSCS is on OS level that you can failover (with a short interuption) when you need to patch or work on OS on a node where it would impact SQL Services. But in the hardware maintenance scenario ESX actually improve availiblity of single SQL Server since you vmotion to another host with no down time.

[JohnADCO]

There are two types of clustering for SQL.  If you cluster for performance, you should be able to down a cluster member and stay up.

[sibdba]

Excellent point, many thanks! Now I got the real difference between MSCS verse HA.

[meistermn]

Why not used SQL Mirroring instead of MSCS?

http://www.dell.com/downloads/global/solutions/public/white_papers/Business-Continuity-for-SQL-and-V...