Solved

Best Hard Drive setup for SQL Server

Posted on 2004-03-24
13
833 Views
Last Modified: 2010-04-03
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 asp.net web server and this sql server db will obviously be the source.

Thanks,
Nick

0
Comment
Question by:NickUA
  • 5
  • 3
  • 2
  • +1
13 Comments
 
LVL 55

Expert Comment

by:andyalder
ID: 10675275
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.
0
 
LVL 11

Accepted Solution

by:
infotrader earned 250 total points
ID: 10675763
Here's a site that explains the difference between different RAID levels:
http://www.acnc.com/04_01_10.html

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
0
 
LVL 55

Expert Comment

by:andyalder
ID: 10675943
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 http://databasejournal.com/features/mssql/article.php/3114651 has an interesting view of what to put where but it all boils down to how many disks you can afford.
0
 
LVL 11

Expert Comment

by:infotrader
ID: 10676010
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
0
 
LVL 55

Expert Comment

by:andyalder
ID: 10676154
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:droswell
ID: 10676924
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 http://www.microsoft.com/windowsserver2003/evaluation/sysreqs/default.mspx

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

0
 
LVL 1

Author Comment

by:NickUA
ID: 10679273
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.

Thanks,
Nick
0
 
LVL 1

Author Comment

by:NickUA
ID: 10679315
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
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 250 total points
ID: 10679878
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, http://support.microsoft.com/default.aspx?scid=kb;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?
0
 
LVL 1

Author Comment

by:NickUA
ID: 10679975
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?
0
 
LVL 55

Expert Comment

by:andyalder
ID: 10680153
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.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article, I provide some information on storage disks which go into calculations that will help you figure out how much Input/output Operations Per Second (IOPS) your disk subsystem can deliver. To effectively size & tune up applications l…
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now