Setting up SQL Server clusters on a SAN

Storage area networks, (SANs), make it easy to connect massive amounts of expandable storage to a server. SANs are particularly useful for SQL Server installations: Enterprise databases don't just require a great deal of storage; they also have continually-expanding storage needs. That said, you need to take some care when using SANs in clustered SQL Server environments. In this tip, I'll give you some suggestions to keep in mind when setting up a SQL Server cluster on a SAN.

1. Get manufacturer-specific guidelines for tuning

SANs are not all built the same. Know your SAN before you hook it up and start populating it with data. For instance, you must understand how to prepare disks and what recommendations the manufacturer offers so they will work well in a clustered Windows Server environment. Check to see if the SAN you're using has actually been tested in a clustered environment or not.

For instance, you will likely have to use the DISKPART.EXE utility (included in Windows 2003 Service Pack 1) to fine-tune disk-track alignment. Hewlett-Packard Co. is one company that provides with its storage devices detailed documentation about how to perform this kind of tuning for Windows Server 2003. (This is usually referred to as the "LUN offset" on SANs.)

2. Use RAID-10 whenever possible

This isn't a cluster-specific piece of advice but it's important nonetheless. If cost is less important than data integrity, use RAID-10 for your SAN, which is widely considered one of the best storage arrangements for databases although it comes at a higher cost.

For those not familiar with it, RAID-10 is "nested RAID," or a RAID-0 array made from a set of RAID-1 arrays. It's also been described as a stripe of mirrors. This is an extremely robust and efficient setup; RAID-10 is not just highly fault-tolerant, but it supports fast writing, too, which is critical in a database.

When you set up a RAID-10 system, put data and log files on different sets of mirrored spindles to enhance both your speed and your recovery options. The more physical spindles you can spread your data out over and the more redundancy and parallelism you can get the better.

RAID-5 is also commonly recommended for databases, but RAID-5 is best on read-only volumes. RAID-10 is best in any scenario where disk activity has more than 10% writes, which is probably the vast majority of databases out there. For very large databases that grow into the terabytes, you could even consider RAID-100, which adds yet another level of nesting and striping (also called "plaid RAID").

3. Active/active and active/passive considerations

An active/active (a/a) cluster should get a different disk arrangement than an active/passive (a/p) cluster. An a/a cluster has two nodes or servers, which are both active at the same time, balancing the load between them and mirroring each other's updates. If one server goes offline, the other can pick up the slack as needed. An a/p arrangement has one server running continuously with the other server sitting idle. If the main server fails, only then does the backup server kick in.

With a/a clusters, each database server should get its own set of mirrored disk spindles; the two should not share the same logical drive for their databases. This is obviously more expensive, but if you want the best possible uptime, then the cost involved in adding the needed disks will be well worth it. Some database administrators go so far as to provide a dedicated SAN to each node of the cluster. However, if the amount of data replicating between nodes outweighs the amount of data going to and from clients, it might make more sense to keep the data for an a/a setup on the same SAN (albeit on different physical disks).

With a/p, you can easily have the database(s) sharing disks or SAN units. Since only one database server is active at any given time, there's no contention going on.

4. Keep drive lettering consistent across clusters

This is one of the most cluster-specific pieces of advice to keep in mind. All host nodes in a cluster must see the same drives with the same drive letters, so plan your drive lettering cluster-wide. The clustering software controls who has access to a specific device, so you don't need to worry about that; but each node must have a consistent view of the storage to be used.

5. Don't try to move temporary databases around

The temporary databases used by SQL Server are part of the failover process and need to be available in a shared context. Don't try to move them around. You may think you're getting SAN bandwidth back by hosting temporary databases locally, but it's not worth doing at the expense of basic functionality.

6. Do backups through mapped drives only

If you're using a SAN to store SQL Server backups, those backups should be run through a mapped drive letter and not through a UNC name. Failover SQL Server clusters can only work through storage devices registered with the Cluster Service Cluster Administrator. This becomes doubly important if you have a failure and need access to SQL Server backups through a device shared on the cluster. Also remember to keep the advice in tip #4 in mind when mapping out drives for your backups.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

More information from SearchSQLServer.com