Best Hard Drive setup for SQL Server

I have an SQL Server machine that i'm building and i'm going to have 1.5gb of data uploaded each week (divide that by 7, uploaded once a day) so write isn't *that critical... however, there will be a ton of reads - and some pretty massive queries spanning over a billion rows...  I have a pretty serious budget for building this machine so i need to know the best solution for housing this database... right now i'm kind of torn between a couple solutions...

Dual Opteron 248 processor setup w/ 4-8gb of ram

4 76gb 15k rpm u320 scsi Fujitsu hard drives in Raid 5 or 10 for the data files
6 76gb 15k rpm u320 etc etc  in raid 5 or 10
8 etc etc

any ideas?  i expect nearly 100gb/yaer - and i need to be able to expand in the future... fault tolerance is a pretty serious issue as well - i can have *some downtime in order to rebuild arrays and what not - but if i lost that much data i'd be done for...

Another solution i was told about would be SAN?  I know NOTHING about this...  i just need fast Read I/O - in all of my benchmarks i've run on my app/server it's bottled at disk read I/Os.

Please advise - i need to make a decision w/in the next 48 hours...

i'm building a complementary web server and this sql server db will obviously be the source.


Who is Participating?
infotraderConnect With a Mentor Commented:
Here's a site that explains the difference between different RAID levels:

I would probably do:
RAID1 for the OS partition (so that it is redundant)
Another RAID 1 or 0 (or even no RAID), depending on the amount of the RAM in the system, for the SWAP file/Virtual Memory
RAID5 for the Data

Why RAID5 you asked?  Well, 1st of all, I am cheap.  #2, I am thinking about physical drive bays.  #3, Expandability.  #4, Fault Tolerant.

The only reason why you would not use RAID5 is because of the write speed (RAID0 or 10 would be faster).  But since you said write speed isn't as important, I'd recommend investing those money on memory instead.  You'll have a much faster machine if you don't use the Hard drive for Virtual Memory/Caching.

- Info
Normal setup is mirrored pair for the OS, SQL executables, and page file. Mirrored pair for the log files. RAID 10 for the data. As you haven't got much write data you can get away with RAID5 and even omit the pair of drives for the logfiles.

SAN won't make any difference, it's the quality of the RAID controller whether in a SAN array subsystem or a PCI card in the server that matters. You want as many hard disks as you can afford, lots of small capacity 15K disks will be much faster than a few high capacity ones.
Although MS recommend the swap file to be on a seperate spindle it's not really necessary since once the OS and database has loaded C: isn't busy doing anything. The SQL servers we configure for them don't have seperate swap drives.

The article at has an interesting view of what to put where but it all boils down to how many disks you can afford.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Whilst what you are saying might be true, but there are still times when you're still going to be using the C: drive.  Such times could be:

1.  During first bootup.
2.  When your Windows is running other windows-related process
3.  Database backup
4.  Normal backup

We are talking about "Ideal situations".  As I said, it could be RAID1 or 0 or noe, for swap file :-P
If you want ideal solution then yes, although SQL doesn't use the swap file by all means get another couple of drives for swap but performance would be greater if those two extra drives were added to the main array. With 8GB of RAM there isn't going to be much swapping going on and tempdb might as well be in RAM.

My main concern about RAID5 is its performance with a drive faulty. It has to read every disk in the array to generate the data for the missing disk, RAID0+1 just has to read the mirror so its performance is only slightly reduced. Assuming a decent RAID controller that can read off either disk in a mirror rather than software RAID that only uses the mirror in event of a primary drive failure then 0+1 is going to be faster than RAID5 during read.

Like I said we config Proliants for MS and they never ask for seperate disks for swap space. It's all down to how many spindles NickUA can afford to buy.
I would tend to agree with pretty much everything these experts have said about RAID levels. There are a few things I'd like to add.

A SAN consists of a drive array and controllers. A typical setup would be fiber from the server to the SAN controllers- then SCSI from the controllers to the drive shelves.  This allows a fairly large amount of drive space (the SAN I administrate is roughly 6T).
However, this adds a very significant amount of complexity and cost (upwards of $100K).  For a datacenter require large amount of space, this is a good solution. But if you're increasing by a rate of 72GB a year, you probably won't need a SAN.

A dual Opteron solution sounds good.  Most of the expert advice you're getting here is right on. If you can afford to, a 1+0 array would be ideal. Go with 15K rpm, 72GB Ultra320 drives for your os and data array. Keep in mind, 6 73 GB drives will last you less than three years.

My recommendation is this:  Get your dual opteron server. Put 2 72GB 15RPM drives in a mirror for the OS.  Put 2 18 or 36GB 15K in the server for logs/swap.

8 GB of ram is good, but remember - you wil need Windows Server 2003 Enterprise Edition (or 2000 Datacenter) to support this much RAM. See here for details 

Next, I would buy an external SCSI shelf for your data drives. I would say you should look for 8-10 drives. Also, if you can, use multiple controllers. The more controllers, channels, and platters you can spread the data across the better performance you will have. Limit yourself to 1-2 drives per channel, and 2-4 drives per controller.

Finally, I've said this before in previous posts- but you need to do your homework on things like array block size.  

Hope this helps

NickUAAuthor Commented:
droswell:  Any recommendations for an external scsi shelf?  I can use pretty much whatever i need, i just don't know what i need.  I was looking at the adaptec 2200s?  do i need 2 of these?

Looks like

Raid 1 + 2 36gb Drives for OS / Page

Raid 5 or 10 (jury is still out on this one) for DB Files 6-8 74gb Drives?

Raid 1 + 2 36gb Drives for T-Logs

Possibly another Raid + 1 for TempDB?

Am i thinking right here?

I can probably fit all this into a 4U Rack, however w/ an external scsi holder i can get a 2U web rack and possibly a 2U (I'm assuming) SCSI holder?  Do you recommend any, any links to resellers?  I know of newegg/cdw/malabs ....

And is the only way to buy MS Enterprise thruogh MS themselves?  I currently have 3 Win2k3 STDs and 1 SQL Server STD, do i need SQL Server ENT too?  please tell me no.

NickUAAuthor Commented:
err TempDB would go into ram i suppose?  i know this is wrong forum for this - but real quick, what is TempDB?  i can guess by the name.   i'm a programmer, not a dba :)  i build custom gaming machines, not bigass servers :)

Thanks Guys
andyalderConnect With a Mentor Commented:
I would strongly disagree with limiting to 2 drives per SCSI channel. That might have been relevant for fast-wide scsi but with Ultra320 you aren't going to swamp the bus until you get to 7 or eight drives or for small I/Os you can use a 14 drive shelf on a single channel.

SQL standard only supports 4GB RAM and that ought to be plenty for you, not worth going up to Enterprise. I'd consider a single CPU if money is tight, as SQL is back end for web server it has to be licnesed per CPU and it isn't cheap. Adaptec do a 12 drive 2 U enclosure that will give you room for expansion. The adaptec card you mention 2200s would do fine, if you want to enable write cache you need the external battery pack for it but you say you're not concerned with write performance so you might as well skip this.

I don't know what tempdb is either,;EN-US;115050 note that if you use this option you will need the external battery.

What are you going to back it up onto?
NickUAAuthor Commented:
yeah - saw that 20k per processor license for enterprise - that sucks...  I have std right now w/ 1 proc license and will upgrade to 2 when i build this machine...  unless you guys recommend a different solution?  Oracle?  I'm a big fan of Yukon.

this adaptec 12 drive 2U enclosure is just that - an enclosure right?  It's not SAN - two different things?  do you have a model number on the adaptec you're talking about so i know to get the right thing?

I was going to ask another expert question about a backup solution - possibly just a tape backup?
I just spec the kit but our salesmen tell me Oracle can end up cheaper than SQL.

It was the SC4100 JBOD, it's just a shelf, ignore the term JBOD. There are many similar things from other vendors. Is there a preferred manufacturer? It's not SAN, for a single server SAN would just slow it down if the PCI RAID controller is any good.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.