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 (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 c...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
an 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 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. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. 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
Learn how to set up SQL Clusters on a SAN
SQL Server performance considerations when configuring a SAN
Choosing the right SAN makes life easier down the road
