Best way to deploy SQL Server 2005 with SAN

If it is a first time you are deploying the SQL Server 2005 on a SAN, then there are few things you need to be considered that will be raised by installations that are either deploying SQL Server for the first time or are upgrading to SAN from direct attach storage.

The important factors 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.  By experience I can say nothing much difference between SQL Server 2000 and 2005 versions. In any case the hardware is designed to cater the core requirements of SQL Servers in addition to the operating system with regard to I/O subsystem, talk about virtualization of IO subsystem that purely depends upon the RAID level you choose to deploy. So SQL Server I/O hungry such as media stability during high-write & read periods, additoinal write-Ordering and Torn I/O prevention with a detection, most of these are clearly explained within this SQL2000IOBasics whitepaper.

It may not be possible to have a SAN administrator on board, but you can get a hand from vendor SAN expert during the early days of your SAN configuration design cycle that will help you to input all of your SQL Server usgae patterns, this will have additional usage of LUNs of mount volumes to SQL Server in order to have multiple instances installation. Such a layer to this abstraction, SQL server is not aware of the actual physical disks;  not only this the SQL Server performance is purely dependant upon the query design and optimization of frequently used queries/processes.  Such an abstraction with have all in one-physical server 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. 

The following characteristics are very important to have a design plan in place for SAN, these I have extracted Microsoft documentation source that was pointed by one of the Microsoft's analyst during a project review at our end. In this regard you must identify and be aware about relationship between LUNs (logical) and physical disks.  Such as number of physical disks backing each LUN and which LUNs share the same physical spindles.   So 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 catlogs when your SQL server is not shared such as having Exchange and SQL Server on same server. The configuration of storage array and its speed with 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 needs to be configured/planned.

This brings up usage of proper RAID disks within this SAN, so turning the path towards RAID it is best to go with RAID 10 both for logs and data/index files on the database. But you need to consider about costing so then go with RAID5 or equivalent by keeping log files on RAID 10 at least. As RAID 10 offers better availability than RAID 5 and better performance especially for write-intensive applications.  By experience I have seen a bit of impact on performance of RAID 5 that is dependent on the hardware vendor’s implementation of RAID 5 and the characteristics of particular work load.

To close up the disucssion I will wrap benefits you can get by keeping up a base-line for performance and availability to your database that will be ahead of time the maximum throughput and response time to expect. Also do not forget in identifying any performance issues quickly and resolve them. In this series I strongly recommend to review the whitepaper on disk subsystem performance and also using the free tools as SQLIO.exe or IOMeter. To download these tools refer to http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO.msi & http://sourceforge.net/projects/iometer/.