Monday, October 27, 2008

Using local disks in a cluster

With the advent of (relatively) cheap, fast Solid State Disks, DBAs everywhere are realizing that they could potentially see huge performance gains by incorporating these new disks into their database servers. This is straightforward if you've got a monolithic box: buy 2 SSDs, create a new RAID 1, move tempdb and/or logs onto it, restart SQL, shake well, and watch your IO waits go to 0! Hopefully, anyway.

However, if you've got a cluster, things are a bit trickier. The SAN vendors are still ramping up their SSD support, so it can be difficult to get an SSD into a shared storage enclosure unless you roll your own. Especially if you pick up one of the most promising new disks, the FusionIO ioDrive. This disk gets attached to a PCIe bus directly rather than a SAS or SATA backplane, so it can't be put into most commercially available shared storage devices (SANs, NASs, etc.).

Even so, we were sufficiently impressed by the IO stats on this disk to pick up a couple of them, but we still wanted to put them in our production cluster. We can't use them for shared data, obviously, as the data wouldn't persist between machines. But in theory they could be perfect for tempdb, which gets wiped out on each restart and so only needs to see the same path on each machine. I figured there was probably a way to get Microsoft Cluster Services to see the local SSDs on each machine as the same disk.

To test this theory, we (I and coworker Doug H.) took a pair of virtual machines, added drives to simulate a standard local disk, shared SAN storage, and the new SSD, built them into a cluster, and installed SQL Server 2005 on the R: drive.

Disk 0 (C:) - local - boot disk
Disk 1 (Q:) - shared - quorum
Disk 2 (R:) - shared - SQL data
Disk 3 (D:) - local - simulated SSD

When the VMs were initially configured, the fake SSDs were created on the same SCSI bus as the boot disk. The cluster administrator quickly informed us that this would not fly - disks on the same bus as the boot disk cannot be used as shared resources. So we moved the D: drives to a new bus, separate from both the C: (simulated backplane) and Q:/R: (simulated SAN) drives.

The next step was to add the D: drive to the cluster. I assumed this wouldn't work without some hacking, but I decided to try a "baseline" attempt without any modifications. The Cluster Admin New Resource wizard allowed me to add the D: drive on one of the nodes, but it wouldn't come online. So much for the baseline attempt.

I knew that Windows 2003 (though maybe not 2008) clustering relies on disk signatures to identify disks for cluster operations, so I decided to synchronize the signatures of the two fake SSDs. To this end, I found a utility - MBRWizard - that would let me manually set the signature of an MBR disk.

Using this utility, I found the signature of the D: drive on machine #1:

mbrwiz /Disk=3 /Signature

and then overwrote the signature of the D: drive on machine #2:

mbrwiz /Disk=3 /Signature=xxxxxxxx

Then I added the disk as a cluster resource again, and brought it online. Success! Failover worked, too, so I failed the SQL Server group (Group 0 in the image below) back and forth between nodes and created a Data directory on each D: drive.


The next step was to make sure it worked with SQL Server. I took SQL offline, added Disk D: as a dependency, and brought it back up. Then I altered tempdb's location to use the D: drive after a restart.

alter database tempdb
modify file (NAME=tempdev, FILENAME = 'D:\Data\tempdb.mdf')
GO
alter database tempdb
modify file (NAME=templog, FILENAME = 'D:\Data\templog.ldf')
GO

Finally I took SQL offline and brought it back up, with fingers crossed. And it worked! Failover worked too.

So my proof of concept for using a local SSD in a cluster (for tempdb only!) was successful. Stay tuned to see if it works in the real world.

8 comments:

creon said...

So, did it "work in the real world"?

Ira Pfeifer said...

Unfortunately we haven't tested it yet - I've gotten pulled off of Adventures in Hardware Land and back onto more traditional DBA and development work. Hopefully I'll have more news soon.

J said...

I've tried to do just this on a non-virtual cluster. I was able to add the drives (using one RAID controller) by using the ManageDisksOnSystemBuses reg entry, but they new physical disk resource won't ever fail over properly. Any luck with your implementation?

Michel said...

J,

How did you do it. I have also a physical cluster with a local disk on a HP smart array P400 controller. Added the key and the logical disk but i can't see the disk when creating the new disk resource.

michel

Michel said...

fixed it:

see http://support.microsoft.com/kb/555312

scot.post said...

Anyone tried this on Windows 2008 MSCS? I cannot bring the SSD disk online. I set the same disk signature on each disk. Each disk is connected to the internal P410i controller on an HP proliant server. Ths disks are on the same bus as the boot disk. I followed KB555312 to configure the disks, but online fails.

daveberm said...

I'd be curious to know if anyone has all the steps required to make this work with Windows 2008 R2. I can make it work using DataKeeper Cluster Edition from SIOS with a trick to create a volume resource without a mirror but I'm not sure how to do it without a 3rd party storage class cluster resource.

Philip Gray said...

I found a different way to go about this that doesn't require signature changes and doesn't break cluster validation.

I've been running this on a production SQL cluster without any problems for just over a month.

I posted some detials on it here:http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/7858a037-e445-4385-bfaa-283de838240e/

I'd be interested to hear some community feedback on this.