SAN considerations for your SQL Server environment

SANs surpass the performance of locally attached storage for its high availability and high performance access to data. In this tip, Contributor Hilary Cotter highlights what you need to consider when using SANs and how to test 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 (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

This was first published in January 2008

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close