Setting up SQL Server clusters on a SAN
Serdar Yegulalp, Contributor
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in January 2008
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 Yegulalphas been writing about computers and information technology for
more than 15 years for a variety of publications, including InformationWeek and Windows
Magazine.
More information from SearchSQLServer.com
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation