SQL Server 2000 I/O Configuration in a SAN/NAS Environment
In order to size your disk drives properly, you must also keep in mind the overhead incurred by RAID striping. Most systems use some sort of RAID striping in order to avoid the loss of data in the event of the loss of a disk drive. RAID overhead usually does not come into play during read operations, but the write overhead for RAID 1, and RAID 0+1 is 2x and for RAID 5 is 4x. Thus, if you are calculating the required number of IOPS for RAID subsystems, you must take into account the RAID overhead.
|RAID 0||None||No fault tolerance, one fault and you’re out.|
|RAID 1 or RAID 0+1||2 x (1 logical I/O = 2 physical I/Os||Good fault tolerance.|
|RAID 5||4 x (1 logical write = 2 physical reads and 2 physical writes)||OK fault tolerance. Poor write performance. Most economical.|
Note: No RAID levels incur any read performance. The overhead that you incur by using RAID is only reflected in writes.
Tuning the I/O Subsystem
To tune the I/O subsystem you must make good choices. These choices are: how many disk drives do you need and how are they to be configured. Choosing the correct RAID level and the number of disk drives is the most important thing that you can do when designing your system. In addition, I/Os must be monitored in order to determine if there are problems, and if there are problems, the I/O subsystem should be modified.
Of course there are other factors, such as RAID stripe size, RAID controller CPU utilization, controller caches, data file placement, etc., but they are beyond the scope of this article.
SAN and NAS Systems
SAN and NAS systems provide different types of storage from traditional internal or direct attached storage. A SAN (Storage Area Network) is designed to provide access to storage over a private fibre channel network. A NAS (Network Attached Storage) is used to provide storage over a standard network.
A SAN system is an external storage system that allows multiple computer systems to access the same storage. The RAID controller inside the external storage system is able to take requests for different logical volumes within the storage system from different HBAs (Host Bus Adapters). This allows for several different features. One of the most common uses of a SAN is for storage consolidation. This is where multiple systems share the disks in the external storage subsystem. This allows for consolidation of storage resources and management. An example of this is shown in the figure below.
With storage consolidation, even though the storage in the external disk subsystem is shared among the different systems, it is not entirely accessible to all systems. Logical disks are carved out of the physical disk drives and allocated to each of the computer systems. Only one system can access a particular disk volume.
Another use for a SAN system is for clustering. Failover clusters use a shared disk subsystem that allows one of two systems to access the same storage. Even though the storage is accessible by both systems to access the storage, only one will be used at the same time. In a failover cluster, if one server were to fail, the second server could resume operation by taking control of the storage and the SQL Server database that resides on that shared storage.
SAN Performance Considerations When putting together a SAN system you must not only look at the I/O traffic that is being generated by one system, but the I/O traffic being generated by all systems in the SAN. So in addition to sizing the I/O subsystem disk drives and RAID levels, you must look at the traffic on the SAN itself, as well as keeping in mind that other systems might be accessing the same RAID controllers. It is also possible to run into bandwidth limitations on the SAN itself, since fibre channel has a limited bandwidth.