Deploying SQL Server 2005 with SAN #1

Prem Mehra and Mike Ruthruff

An often asked question is how to design and deploy SAN with SQL Server 2005. The question is frequently raised by installations that are either deploying SQL Server for the first time or are upgrading to SAN from direct attach storage.

This topic covers a wide area that includes several important considerations, such as:

virtualization of physical disks, RAID groups, creation and sizing of LUNs, alignment of sectors, NTFS format size, base lining of SAN throughput, dynamic and basic disks, sizing and allocation of Windows files, isolation of data, index and log objects, performance monitoring, etc.   

We want to address most of these considerations starting with this blog and hopefully add to these recommendations in future. So far, we have not found any reason to alter our recommendations for SQL Server 2005 SAN configuration from those we have made for SQL Server 2000.

Three of the key things to think about before deploying SQL Server on SAN are 1) the core SQL Server requirements with respect to IO subsystem, 2) the complexity introduced by virtualization of the IO subsystem and 3) the type of RAID level to choose. 

1. SQL Server 2005 is capable of working with SANs from various vendors including the well known leaders in this field. When choosing a SAN or any disk array vendor, please make sure that the selected hardware honors the tenets of SQL Server I/O basics, namely: Stable Media, Write-Ordering and Torn I/O prevention. These are required to ensure data integrity; for details please refer to: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

2. Get engaged with your SAN administrator and / or engineer early in the SAN configuration design cycle to provide input from SQL Server usage perspective. Remember, in general, SAN presents LUNs to Windows which in turn presents drive letters or mount volumes to SQL Server. Due to this abstraction, SQL server is not aware of the actual physical disks; however, as you well imagine, SQL Server performance is dependent on the choices made while deploying SAN.

This abstraction can introduce challenges when troubleshooting a poorly performing IO environment, even more difficult when multiple servers share access to the same set of underlying physical disks.  For this reason it is critical to understanding the underlying physical characteristics of your SAN before and during deployment of SQL Server.  Some of the key ones include the following:

• Relationship between LUNs (logical) and disks (physical).  Specifically understand the number of physical disks backing each LUN and which LUNs share the same physical spindles.   
• Any sharing of physical disks between multiple servers and what applications are running on each server.  Problems can arise when different servers share the same physical spindles and have very different IO characteristics (i.e. Exchange and SQL Server)
• Physical characteristics of components in the path from the host to storage array (i.e., speed and number of HBA’s in the host, speed of switch ports, number of paths from switch to array, speed of front side fiber channel ports on array,  amount of cache on array, speed of physical disks etc..)
• HBA placement in relation to PCI buses on host server.  Most modern servers have multiple PCI buses and slots.  PCI (or PCI-X) slots can have differing bus speeds.  It is important not to overload a particular bus with HBA’s.

3. Our recommendation is to use RAID 10 both for logs and data/index files. When cost is an issue you could allocate data and index files on RAID 5 or equivalent; however, keep logs on RAID 10.
   
   RAID 10 offers better availability than RAID 5 and better performance especially for write-intensive applications.  Some of our customers have reported up to 50% adverse impact on write intensive work load when moving to RAID 5. The impact on performance of RAID 5 is dependent on the hardware vendor’s implementation of RAID 5 and the characteristics of your particular work load. You should not assume that you will get a 50% adverse impact – it could be higher or lower.
   
   

In the next blog we will address a few additional topics on SAN and SQL server deployment and focus on how best to assign SQL Server objects to LUNs to achieve good performance while keeping design simple.