SAN considerations for your SQL Server environment
SANs (Storage Area Networks) are playing an increasingly important role in SQL Server
environments for several reasons. They include:
- The increasing size of the average database
- The increasing prevalence of clustered environments
- Performance advantages of SANs
- Storage efficiencies of SANs
- The increasing importance of recoverability or disaster recovery
The SANs sole function is to store data and offer high reliability and high performance access
to this data. It is a network which provides high-speed, highly reliable transportation of data for
multiple servers which generally connect through a high speed optical network called fibre. A SAN
consists of many high performance hard drives
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
(typically several hundred) aggregated together with
high performance controllers and caching; these hard drives are virtualized so that the consumer
does not know which hard drives a SQL Server or other device connected to the SAN will access. A
SAN administrator will present blocks of storage to servers using the SAN and these blocks of
storage can consist of a single hard drive, multiple hard drives or portions of hard drives in a
logical unit called a LUN (Logical Unit Number).
SANS have several advantages over locally attached storage:
- There is a distance limitation for SCSI connections which the SAN fibre network overcomes.
- Most SANs provide features which allow you to clone, snapshot, or rapidly move data (replicate)
from one location to another. File copies or bcp over your network simply are not scalable. This
increases their usefulness for disaster recovery.
- SANs play well with clusters. Clusters share resources between the nodes that form the
cluster.
- SANs will allow a cluster or a server to boot off a SAN.
- SANs offer increased utilization of storage. With locally attached storage large amounts of
disk space can be wasted. With a SAN you can expand or contract the amount of disk space a server
or cluster can access.
- SANs will also offload some of the processing from the host system to the SAN itself.
However SANs, due to their cost and complexity, only make sense in large enterprises. They are
not a good choice in:
- small environments with relatively small databases
- companies with limited budgets - SANs are very expensive
- companies which only require disaster recovery on one or a few SQL Servers
Before you rush out and hit your procurement manager for a SAN or two, there are some
considerations with using SANs with SQL Server.
Caching
One of the reasons SANs offer superior performance to locally attached storage is they typically
offer significant caching. This is normally a good thing. But as a SAN provides storage services to
multiple servers the available cache is also shared between multiple servers. So the large cache
may not always be available to you. This is especially exacerbated if other IO intensive
applications like Exchange are sharing the same cache as your SQL Server.
It is also possible that some file or database operations (like a checkpoint) can saturate the
cache resulting in degraded read and write performance. Do benchmarking with your SAN vendor to
ensure that your SAN cache will be adequate to provide optimal database performance.
LUNS
SAN administrators will carve up the SANs storage into LUNs and the servers will "see" one or
more of these units as a partition or drive. Consider three 200 GB drives in the SAN. This could be
theoretically divided up into two LUNS 300 GB each, and different SQL Servers could access each
LUN. You may end up in situations where the drives will experience twice the IO from both two
servers than if the drives were dedicated to a single server. Most SANs support Zoning, which
allows the SAN administrator to dedicate entire disks in the SAN to your LUN in order to isolate
the IO on this drive to your SQL Server.
Select the largest appropriate LUN possible as this will distribute the IO better over the
multiple disks which comprise your LUN. Ultimately this will offer better IO performance.
Also try to ensure that your log files are on a separate LUN consisting of a dedicated disk. Log
files typically are written sequential patterns, unlike data files. Having a LUN with drives shared
with another application will not provide optimal IO performance. Your SAN administrator may not
permit you to dedicate a separate disk or set of disks to your log files.,p> There are special
considerations if you have a large number of LUNs dedicated to your Windows Server. Please consult
this kb article for more
information.
RAID
Some SAN vendors may attempt to convince you to use RAID 5 for all data files and log files.
Before following their advice, test using a representative load to ensure that RAID 5 will offer
best performance for tempdb, your log files and any write intensive filegroups you may have.
Host Components
Check to ensure that the hardware your SQL Server (your host) uses to connect to the SAN
delivers optimal performance and that you have the correct, most up to date drives for them.
- Consider using multiple high speed host bus adapters (HBA) on the servers accessing your SAN.
If you do use multiple HBAs ensure they are on different bus's to prevent bus saturation. As PCI
slots have different bus speeds, see that the HBAs are plugged into the slots offering the highest
speed.
- Use multi-pathing software to balance the IO across all HBAs.
- Consider aligning your NTFS volumes with the SAN track sectors. This can be done via the LUN
offset on the SAN or through the Resource Kit DISKPAR tool to "sector" align the partition before
you format it. What you want is 64 sectors equaling 32,768 bytes. By default you will have 63
sectors per track which will result in additional IO when reading blocks 4k and larger. Studies
have shown improvements of 10 to 20 percent simply by properly configuring your partitions.
- Work with your SAN vendor to ensure your SAN solution delivers optimal performance for your SQL
Server.
Summary
As you can see SANs are complex and delivering optimal performance of a SQL Server solution
using a SAN is challenging. Benchmark your SQL Server to determine if bottlenecks exist with your
SAN. Work with your SAN vendor to fine tune your solution and carefully consider and test any
recommendations they may make.
ABOUT THE AUTHOR
Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a
Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in
2001. He is the author of a book on SQL Server transactional replication and is currently working
on books on merge replication and Microsoft search technologies.
More information on 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