July 14, 2008 at 12:02 pm
First, on the storage arrays, I get 7.2 Gig from those numbers, so I'm assuming there's some room lost to RAID set up or some such. Is this an OLTP database (lots of updates/inserts/deletes), or an OLAP database (lots of reads, but inserts/updates/deletes are large and infrequent)? If OLTP, you'll want to make sure to have it on RAID-10 (or RAID-01, of course), NOT on RAID-5 or RAID-6.
One thing you can do to make backups a bit easier to deal with is split the database into multiple files, and back those up individually instead of trying to back up the whole database all at once. This has some complexities to it, but it can be done and might be more efficient.
Another thing you can do for a high-transaction database is split some of the common indexes off onto another drive array from the array the table (clustered index) files are on.
Of course, have the data files and log files and backup files on separate arrays, at the very least. And have tempdb separate from those, if possible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 12:24 pm
Hello and thanks for the response.
This database will be based on your explanation an OLTP database. There will be hundreds of thousands of records created each day.
I had originally planned for RAID 10, but that will take twice as many drives and twice as many enclosures putting the cost for this setup around $50k, which I cannot justify spending when I can do RAID 5 for half the price.
I had planned on making a Data Drive, Log Drive and Backup Drive out of the storage array and raiding each one accordingly.
I am not sure if we have the knowledge to split the DB into multiple files, or house the indexes separatly.
July 14, 2008 at 1:46 pm
Just keep in mind that RAID-5 will have lower performance than RAID-10 on this kind of database. Also, because of the constant drive thrashing, there is some evidence that RAID-5 fails more often than RAID-10 on this kind of thing. (Even with that in mind, RAID-5 failure rate is pretty slim, it's just a risk to keep in mind when planning for disaster recovery.)
Since it's going to be RAID-5, make sure when you separate the data, log and backup drives, that you're not just using partitions, but actually using different disks for these things. Otherwise, there's not much point to separating them.
On the expense, work out with management what the cost would be to the business of losing all data back to the most recent tested backup. The cost of doing the drives the right way can be explained as an insurance policy with that loss value as it's maximum payout. They may not (probably won't, in most companies) buy that, but it's a true statement, and you'll be on-record for doing the right thing by bringing it up. And if they do understand the value of their data, and back you up on disaster recovery, business continuity, et al, then it'll be very worthwhile.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply