Storage system for SQL Server 2005

The most common performance problems found in SQL Server 2005 databases involve the disk storage system and in particular where the data and log files are stored. Using an appropriate storage system for your SQL Server database files and configuring it properly can have a massive affect on the performance of your database. SQL Server performance is extremely dependant on I/O performance, if you get this right you are like to get good performance out of you database. This article will look at some of the storage systems available to you.

Storage Systems

There are three different categories of storage systems:

Direct Attached Storage (DAS)
Network Attached Storage (NAS)
Storage Area Network (SAN)

This section will provide an overview of the three different type of storage system

Direct Attached Storage

DAS is the most commonly used storage subsystem for database storage. DAS is computer storage that is directly attached to one computer or server and is not, without special support, directly accessible to other ones. The connection runs directly from server to the storage device. These devices can either be internal or external devices, an example of DAS storage are the local hard disk drives that can be found in many servers. Many smaller organisation or smaller systems utilise DAS as their storage system. The main alternatives to direct-attached storage are network-attached storage (NAS) and the storage area network (SAN).

Network Attached Storage

NAS provides storage that that is connected using and IP based network. A NAS storage system consists of disk storage and software to manage the storage and is dedicated to serving files over the network. A NAS device attaches to the LAN like a normal file server. It doesn�t contain a full blown operating system but utilises a slimmed down kernel.

NAS is not recommended for SQL Server data storage because of the overhead incurred by the protocols used for file level data access. For this reason using a NAS storage system for your SQL Server database is likely to incur a performance hit.

Storage Area Network

SAN is the most common storage system in medium to large organisations. The benefits of SAN include flexibility, scalability, storage consolidation, centralised storage management. SAN provides the largest amount of consolidated storage and the largest number of server hosts that can be connected. The host server can be on different operating too for example UNIX and Windows and Linux are usually supported.

A SAN usually consists of disk controllers, disk cabinets and disk drives. Each SAN model will usually support a limited number of host machines and limited disk drives.

Storage Considerations for SQL Server 2005

Having reviewed the most common storage systems I will now try and collate this information and analyse how it will impact on SQL Server 2005. Read/Write performance greatly affects the performance of SQL Server. As a result the best I/O performance with SQL Server is block based data transport so you�re likely to choose between DAS or SAN. NAS transports data in file form.

I would not recommend using NAS as a choice for storing SQL Server files.

SAN or DAS? DAS is usually less costly when compared to a SAN solution. SAN offers greater flexibility. When determining which storage system best suits your needs then consider the following:

How big is the database(s)
How many users will need database access?
What activity will be performed OLTP. OLAP etc?
How many disk drives will be needed for storage space (include backups) how many spindles will be needed for best performance � will you be separating log and data files � this can have a massive affect on performance?
Data growth predications?
User Growth predications?
Will other servers be added to the environment?
The high-availability need of the data?
Scalability and disaster recovery requirements of the organisation need to be considered.
If you have a relatively �small� environment, for example with one SQL Server, one production database etc then a DAS storage solution may be more appropriate. For larger systems, in a multi server environment, where I/O activity is high then a SAN storage solution may be a better option. A SAN solution can offer �high-availability� and disaster recovery options compared to DAS

This article has looked at the storage systems available to house your SQL Server 2005 databases. I will re-iterate here that I/O activity can have a huge impact on the performance of your SQL Server 2005 database. Other factor like the amount of servers in your infrastructure, the need for high availability and disaster recovery will all be factors to consider when deciding on a storage solution for your SQL Server environment.
Contact us if you wish to dicuss your SQL Server storage needs