There are several options available for configuring your storage media in Windows Server 2003 and Windows Server 2008. By understanding your storage options will help you to make informed decisions about SQL Server system configuration and data storage. In this blog post we will first take look at what Software and Hardware options are available for configuring your storage media in Windows 2003 Server and Windows Server 2008 and then I will be detailing the performance guidelines on how to physically distribute your database files on storage media to achieve best performance.

Software Options:

Microsoft Windows 2003 Server and Windows Server 2008 products provide you following options for configuring your storage media:

• Disk striping — Disk striping is a software implementation of Redundant Array of Independent Disks (RAID) 0, spreading data across multiple drives. This provides high performance levels but no fault tolerance.
• Disk mirroring — In disk mirroring (RAID 1), two drives are configured as a mirrored pair. Both drives have identical copies of data. Disk space requirements increase significantly, due to the duplicated data, but the system is protected against a single drive failure.
• Disk duplexing — Disk duplexing works the same as disk mirroring except that two disk controllers are used. This permits split reads, which will result in performance improvement on reads. Data is protected against a single drive failure.
• Disk striping with parity (striped volume) — This is a software implementation of RAID 5 with data and parity information spread across multiple (three or more) physical hard disks. The equivalent of one disk’s storage capacity is lost to parity information. Data is protected against a single drive failure.

Hardware options:

You also have the option of using hardware-based disk subsystems for disk storage. Though more expensive, this solution typically provides better performance. One reason for this is that software-implemented RAID configurations place additional overhead on the system processor. You can choose from:

• RAID 0 — The hardware implementation of disk striping. It does not provide fault tolerance.
• RAID 1 — The hardware implementation of disk mirroring and disk duplexing. It provides good read/write performance and data protection.
• RAID 5 — The hardware implementation of disk striping with parity. It provides good read performance and data protection. Write performance is not as good as either RAID 1 or RAID 10.
• RAID 10 (also called RAID 1+0) — A form of disk striping, but each striped disk also has a mirror image disk. It is like mirroring two RAID 0 disk subsystems. This option provides excellent read/write performance and data protection but can be expensive to implement.

Remember the following about Hardware Storage options:

• Disk fault tolerance systems provide added protection in addition to your regularly scheduled backups. However, you should not consider them as a replacement for data backups.
• There is a potential problem with write-caching disk controllers. If the disk controller is not specifically designed for use with a database, the controller can cause problems with data integrity. If a critical hardware error occurs, data in the cache might not get written to the disk, which can lead to partial updates and data inconsistencies. Caching systems that are not database-aware can also cause data on the disk and data cached in server memory to become unsynchronized because of the delay between write commands and physical writes.

Performance guidelines to improve SQL Server database engine performance:

To tune database performance, you must have an understanding of your tables, indexes, and application requirements because where you place files can directly impact your server’s performance. Some recommendations for improving performance are listed as follow:

• Always create database and log files on separate disks as this will ensure that they are not competing for disk space and disk access. You can further minimize the possibility of a bottleneck by placing files on disk subsystems connected to different disk controllers.
• Place the transaction log on a hard disk by itself because the transaction log is written serially and this will allow read/write heads to remain in position
• To spread disk activity across multiple disks, place objects that generate large amounts of disk activity on separate hard disks. For example, you might find it useful to separate tables and nonclustered indexes.
• Always monitor the default filegroup or allow automatic growth to ensure you do not run out of disk space. You will not be able to add any data to the system tables if the default filegroup runs out of space.
• To provide fault tolerance and performance use RAID appropriately. For example Put the transaction log on a RAID 1 volume and the database on a RAID 5 or RAID 1+0 volume. If fast recovery is not essential, put the database on a RAID 0 volume for the best performance.
• Create TempDB database on separate disk drive than user database. This will improve performance for database as different disk controller will improve the performance for disk input/output.