Which Raid Setup for SQL Database

D

Duster

Supreme [H]ardness
Joined
May 4, 2002
Messages
5,838
OK I have an SQL style database kind of thing that I want to put into a raid setup for speed but was unsure how to do this. This is in a windows 2k3 environment which will also be on this array. I know this is not the greatest but it is something i don't have control over. The data base is not that big currently (<10 gigs). About 10 stations access this array atm but will grow to maybe 20 by years end. I have 3 80 gig HD's for this and wondering how I should provide.

I know the importance of backups so please don't tell me how this can increase my drive failure and cause loss of data. I have a backup server for this that has a raid 5 array and then that is backed up to two different locations.

I was thinking Raid 0 with 2 drives then the 3rd as a backup... not Raid 0+1 that way i can page file on the spare drive or would i even notice a difference?
 
well the more drives you can spread your pagefile over the better... but i'm not to sure how well windows manages its pagefile over multiple drives (ie most of the information being called up from the pagefile may be all on one drive). I put my scratch disks on raid0 volumes where possible.

I'd buy another 80gb and raid10 for your SQLDB... but thats just me.
 
I don't know what vendor you're using, but Oracle usually recommends a distributed parity RAID version for their subsystems.
 
gaspah said:
I'd buy another 80gb and raid10 for your SQLDB... but thats just me.
Click to expand...

Well that is easy, Don't have the space for another drive so that is not really an option

fri2219 said:
I don't know what vendor you're using, but Oracle usually recommends a distributed parity RAID version for their subsystems.
Click to expand...

I am using Progress not really sure what version b/c it is part of another piece of software and it seems heavy modified
 
drizzt81 said:
However, other people don't think George Ou's comments are correct:
http://blogs.zdnet.com/storage/?p=131

in the end, I think there are quite a few factors that will determine the optimal disk layout.
Click to expand...

So what he suggested is raid 5 for sql style stuff, which to me i don't think will be optimal b/c this also has to share with windows. I always read about how having windows on a raid 5 is bad b/c it contently reads and writes
 
Do not do raid5 for sql, raid 5 is pretty bad at small writes and dbs do a lot of that, if you can't fit more drives for raid10 or multiple raid 1s then just go with raid1
 
I can tell you from my eperience in the banking industry that if you need performance and redundancy the best way to go is Raid 10 for sql databases. You get very near the performance of Raid 0 with the parity you need. The more pairs of disk you use the better.
 
General Rule is to go with a raid 10 and be done with it. A lot of setups for smaller systems like yours though put the database on its own raid 1 set. Personaly this is the route I would go unless you really need the extra speed then go 10.
 
swatbat said:
General Rule is to go with a raid 10 and be done with it. A lot of setups for smaller systems like yours though put the database on its own raid 1 set. Personaly this is the route I would go unless you really need the extra speed then go 10.
Click to expand...

Well like i said 10 is not really an option. So Raid 1 is better then raid 0 in this application?
 
Raid 0 is not an option unless you don't care about data loss. If one of the drives would fail you would loose your sql db and the system would be down untill you replaced the failed drive.
 
like i said i am not really concerned with data loss as i have multiple backups in different locations
 
well if you have spare drive on hand or willing to deal with downtime while you get replacement drive then raid 0 would be faster then raid 1.
 
Sniper|3d-R| said:
I usually setup a RAID5 array.. sometimes with or without a hot spare. But for SQL/Database stuff its always RAID5.
Click to expand...

why raid 5? dbs do a lot of small writes, raid 5 sux at it, even with good hardware controller it's still slower then raid 1 or 0
 
haha, yes you are in fact correct, and me really thinking one thing and typing out another. haha lack of sleep.

I do RAID5 on my larger storage systems. Got a RAID5 array with 6TB. :)
 
Duster said:
OK I have an SQL style database kind of thing that I want to put into a raid setup for speed but was unsure how to do this. This is in a windows 2k3 environment which will also be on this array. I know this is not the greatest but it is something i don't have control over. The data base is not that big currently (<10 gigs). About 10 stations access this array atm but will grow to maybe 20 by years end. I have 3 80 gig HD's for this and wondering how I should provide.
Click to expand...
Can you analyze how that data grows? Is there a `constant stream' of small insertions, a few large insertions from files (say a load command with MySQL) or is there some other function that describes the pattern better? I am no DB expert, but I would guess that read performance is also dependent on `how big' your queries are. If your DB needs to read large, sequential amounts of data, a STR maximizing RAID setup would be preferential. If you need to read multiple tables at once, a RAID-1 based array may improve performance more.

You say that `ten stations access this array', but do not tell us whether their access is paralell and concurrent, usually read or write etc.
Duster said:
I know the importance of backups so please don't tell me how this can increase my drive failure and cause loss of data. I have a backup server for this that has a raid 5 array and then that is backed up to two different locations.
Click to expand...
That is fine, but how expensive is downtime? How long does it take you to recover from a backup. How expensive is it to have lost the amount of data since the last sync/ backup time?
 
drizzt81 said:
Can you analyze how that data grows? Is there a `constant stream' of small insertions, a few large insertions from files (say a load command with MySQL) or is there some other function that describes the pattern better? I am no DB expert, but I would guess that read performance is also dependent on `how big' your queries are. If your DB needs to read large, sequential amounts of data, a STR maximizing RAID setup would be preferential. If you need to read multiple tables at once, a RAID-1 based array may improve performance more.

You say that `ten stations access this array', but do not tell us whether their access is paralell and concurrent, usually read or write etc.
Click to expand...

reading and writing all the time.

That is fine, but how expensive is downtime? How long does it take you to recover from a backup. How expensive is it to have lost the amount of data since the last sync/ backup time?
Click to expand...

well if the entire computer went down I could be back up in about 4 hours prob

down time is not that HUGE of a deal as we are only open during hte day and no weekends not some 24/7 thing going on
 
You must log in or register to reply here.
Back
Top