Hard Drive Configurations for SQL Server

By: Jeremy Kadlec   |   Comments (21)   |   Related: > Hardware

Problem

At my company we are looking at purchasing a new SQL Server and I am not sure how many disk drives we should purchase.  We do not have an unlimited budget because we are a small company, but I am interested in how we should configured disk drives for SQL Server if we bought 1, 2, 4, etc disk drives.  I also am interested in the rationale behind the decisions.  Can you shed any light on the subject?

Solution

Let's address the second part of your question question first.  The rationale behind spending time building an appropriate hard drive configuration is to ensure the disk drives and the remainder of the resources (i.e. CPU, memory, etc) will meet the application needs for a finite period of time such as 2 or 3 years. You do not want to under estimate the needs and have disk IO problems or have the need to replace hardware every 6 months as an example.  In addition, it is valuable to ensure your disk drives will have proper redundancy to provide basic fault tolerance to ensure the SQL Server component of the architecture will meet the business needs.

SQL Server Components

From a relational engine perspective, the following items can be considered as having different types of processing on a SQL Server machine or can be considered a different component requiring special needs from a disk drive perspective:

• Windows
• System Databases
  • Master, Model, MSDB
  • TempDB
• User Defined Databases
  • Database File groups
    • Primary
    • Indexes
    • Data - Read\Write
    • Data - Read-only
    • Data - Specific tables
• Database Transaction Logs
• Backups
• Full Text Catalogs
• Batch Processing

Capacity Planning

Before you select a disk drive configuration, it is wise to determine your general needs rather than purchasing too few disks and having to upgrade in the near term or purchasing too many disks that are not needed in the long term.  To get a feel for your disk drive needs the PhysicalDisk counters in Performance Monitor should shed some light on the situation.  Specifically, the transactions per second, read, write and transfer operations as well as disk queuing counters should give you a good sense of disk drive metrics.  Then hopefully you can translate these metrics into disk drive needs with the hardware vendor.

Table Legend

Below outlines the table legend for the next few sections:

• Disk - Disk number from a physical disk perspective
• Quantity - Number of disk drives
• RAID - Redundant array of inexpensive disks configuration
• Volume - Windows drive letter
• Purpose - The functional SQL Server component that the disk drives are supporting

Configuration = 1 Disk Drive

Although this is not the idea configuration for redundancy, I am sure many development and test SQL Servers have a single disk drive at some organizations.  It is just the reality in some circumstances, but is without any disk redundancy be sure to understand the potential for a failure.

Configuration = 2 Disk Drives

With 2 disk drives, a single RAID 1 set can be leveraged to ensure any 1 disk drive does not yield a failed server.  This is the most basic redundant disk configuration.

Configuration = 4 Disk Drives

With 4 disks opens up the option to have either one RAID 5 set or two RAID 1 sets.  With the one RAID 5 set you have the opportunity to support additional storage needs as compared to the two RAID 1 sets.  With the one RAID 5 set only one quarter of the raw disk space is lost to support the parity as compared to the two RAID 1 sets where half of the raw disk space is used for parity, so only half of the disk space is usable.  On a similar note, a RAID 10 set can be used which also yields half of the raw disk space as usable.  Here are some possibilities to support those needs.

Configuration = 8 Disk Drives

Once you have a server with 8 or more disks, how the disk drives are configured is really dependent on the needs.  For example, if your SQL Server has a small user defined database that needs to be split up as much as possible to balance the IO needs this is a very different configuration then having a large user defined database that is not IO bound or having to split the IO load between user defined databases and tempdb.   Depending on the disk storage needs and the performance needed, the RAID configurations could be RAID 5 or 10 to support some of the SQL Server functions.  Although, an even number of disk drives are needed for the RAID 10 set.  In addition, typically 4 or more disks are used with the RAID 10 set and 2 disks are used for a RAID 1 set, but some terminology is different based on the hardware vendor's disk configuration interface.  As you can see with more disks you have more options, so below are a few different options that benefit different scenarios. 

Configuration = 12 Disk Drives

This configuration just expands on the last option in order to support each of the functional areas in SQL Server.  Once again, based on your exact needs dictates exactly how the disk drives should be configured, but below are examples of a balanced approach and a multiple file group approach.  In addition, with this approach the backups are written to a RAID 0 set which does not have any redundancy so a maximum amount of storage is available for the backups on the local machine for the fastest possible local recovery.  As indicated above, depending on the disk storage needs and the performance needed, the RAID configurations could be RAID 5 or 10 to support some of the SQL Server functions.  Although, an even number of disk drives are needed for the RAID 10 set.  In addition, typically 4 or more disks are used with the RAID 10 set and 2 disks are used for a RAID 1 set, but some terminology is different based on the hardware vendor's disk configuration interface.

Storage Alternatives

As you select the brand of server you want to use in your environment, also consider the storage options that available from the vendor.  Some vendors have external arrays, have their own NAS\SAN products or have partnerships with NAS or SAN vendors.  If your hard drive needs exceed the options listed above then consider these options.  In addition, if you are looking to consolidate SQL Servers or migrate to blade servers, be sure to fully understand the options available with the storage so that the storage sub system can meet the current needs and scale to meet future needs in a cost effective manner.

Next Steps

• As you consider the hard drive configuration for your next SQL Server, consider this tip as a point of reference to configure the disk drives.
• Although many of these disk drive configurations provide a great deal of redundancy, do not fool yourself by not taking backups.  Be sure to have a thoroughly tested backup and recovery strategy to ensure a 1 or 2 disk failure will not yield a down application and major fire fighting by you.
• If you standardized on a hardware platform and disk drives, consider purchasing additional disk drives to keep on site for quick replacement in addition to 2, 4 or 8 hour support agreements from the hardware vendor.
• Rebuilding a disk drive configuration once the server is in production is not a pleasant process, so be sure to understand the needs and the options available with the disk drives so you are not faced with extensive downtime to rebuild the disk drives and potentially re-install all of the software.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips