How Many Hard Drives Do I Need to Support SQL Server?

Your RAID configuration and the placement of your database files depend on the number of hard drives in your server. Or in other words, you can only use what you have—a real world physical limitation. This is the third post in a series about the file subsystem for SQL Server.

Like almost everyone else that has written about this very complex and important issue, we started talking about RAID and the optimal RAID for this or that. (We had to start somewhere.) However, before you decide which RAID level to deploy, you need to count your hard drives.

Buying New

Are you purchasing a new server to run SQL Server and want to know the number of hard drives to use? The flip answer is more than one and less than 100—about the size of a breadbasket.

OK, here's the serious answer. The number of hard drives you'll want to deploy is a matter of economics and not a consideration of RAID deployment. In fact, the size of the data that you want to hold is the determining factor for the number of drives. We are going to cover this in another post. For perspective, let's run through each scenario.

One Drive

You'll have major issues. If you have only one drive, you don't have any redundancy to your file system, and you can't use RAID. The best thing you can do is deploy another server and implement SQL Server replication to protect your data. Even then, you may lose the transactions in progress if the physical disk fails. Not a good idea. Try again.

Two Drives

Now things are better. With a two-drive server for SQL Server, you deploy RAID level 1 (mirroring), and everything—Windows, SQL Server, database data files, transaction logs, and tempdb—goes onto the one logical drive. For a reminder of what RAID level 1 is, read our previous post. This deployment is fast and redundant; however, it creates a bottleneck around the disk I/O.

The disk subsystem has the potential to be the greatest bottleneck to SQL Server when it comes to hardware.

The speed of the disk drives and their ability to read and write are what truly slow down a server that is running SQL Server—in other words, the more disk heads you have, the faster the server will perform. So even though two drives create a safe environment (using RAID level 1), you want to try to get as many disk heads (i.e., physical disks) in your deployment as possible.

Three Drives

Read more: PTC Windchill on SQL Server

Posted via email from jasper22's posterous