Share via

Deploying SQL Server 2005 with SAN #1

  • Article

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: https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

 

  1. 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.

 

  1. 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.

Comments

  • Anonymous
    October 12, 2005
    What is a SAN? Or a LUN or HBA for that matter?

    At least spell out the acronym once somewhere in the article. Or was this a coded message not intended for public consumption?

    Sorry, I couldn't help it :-)

  • Anonymous
    October 13, 2005
    Glad to see you're going to be writing about SANs and SQL. I've got a question for you in this area:

    I've heard that multiple logical drives are better than one when holding everything else constant because Windows will maintain separate queues for each. Example: 3x 10-disk LUNs, striped metaLUN or metaLUNs across all 30. Is it better to have a single large stripe that produces one logical drive, or 3 smaller ones?

    I can see the advantage of separate queues, but will it produce a significant difference in IOs? Alternately, can it be more efficient to have a single queue that can be better optimized by write-reordering?

    My testing so far has been inconclusive, so I'd like to get your take on the relative performance of single vs. multiple drives. Thanks - hope to read more SAN columns in this blog!

  • Anonymous
    October 17, 2005
    Hello Scott: Fair comments. We wrongly assumed that readers would know the abbreviations. Here they are: SAN - Storage Area Network, LUN - Logical Unit Number, HBA - Host Bus Adapter.

  • Anonymous
    October 17, 2005
    Ira: You raise an interesting question. We plan on addressing this topic in a future Blog. Meanwhile, there are some operations in SQL Server where having multiple logical devices (either through mount volumes or LUNs) can result is better performance under some conditions. Generally, the impact is small.

  • Anonymous
    March 09, 2006
    A good recommendation about using RAID 10 in this case, but you haven't mentioned about placement of TEMPDB in this case and on which RAID. And also it is useful if you can mention/explain to have a seperate files/filegroups are place on different disk groups.
    Thanks.

  • Anonymous
    May 14, 2006
    Thank you for addressing the topic of SQL on a SAN.  Can you expand your commentary to include redundant SANs?  For example, if a single SAN can be viewed as a single point of failure and thus my client requires two SANs for redundancy (in geographically dispersed facilities), what factors should be considered for SQL Server 2005?

  • Anonymous
    May 15, 2006
    The comment has been removed

  • Anonymous
    February 07, 2007
    Best practices say to have a separate drive for OS, log files, data files, and full text indexes.  So, if you have a SAN does it make this practice mute? In theory you can have multiple LUNs sharing the same physical drive, right, which would violate best practices?

  • Anonymous
    March 22, 2007
    We dont have space in one SAN. We bought another SAN toget more space. Is it a bad or unsupported practice to use that new SAN? Should we move the data from one database to the other SAN so that we have all its data ina single SAN? Whatis this is not possible?

  • Anonymous
    March 23, 2007
    Jadeflon: For SQL Server data integrity, it is essential that all basic IO tenets are honoured - including the one about log write ahead. Please discuss with your SAN vendor if splitting the data on two SANs still meets all the tentes. You can read more about these on the website http://www.microsoft.com/sql/alwayson/default.mspx Till you get a confirmation from your SAN vendor, it may be safer to keep all the data on the same SAN.  

  • Anonymous
    April 29, 2008
    There are quite a few places to find information about improving the performance of your SQL Server machines

  • Anonymous
    May 20, 2008
    Scott Cadillac said: What is a SAN? Or a LUN or HBA for that matter? Duh, what does SQL mean? Try Googling if you don't know widely used terminology. ;)

  • Anonymous
    January 17, 2009
    PingBack from http://www.hilpers-esp.com/648317-sql-server-2005-sql-server

  • Anonymous
    June 01, 2009
    PingBack from http://paidsurveyshub.info/story.php?id=76010

  • Anonymous
    June 13, 2009
    PingBack from http://thestoragebench.info/story.php?id=9349

  • Anonymous
    June 16, 2009
    PingBack from http://topalternativedating.info/story.php?id=12214