July 14, 2008 at 10:27 am
Hello everyone...
I am starting to research options for a new software application that we are about to deploy. The software vendor has told us that with the number of sites and other items related to our business that their database will be right around 1 TB after one full year of usage. This is way out of the scope of anything we have done before and I am hoping for some guidance.
We have a SQL Server that is an HP Proliant x64 server with four processors and 16 GB of RAM.
I have researched today and chatted with HP and determined that a SCSI storage array with 24 x 300 GB SAS 15k RPM drives would give us right around 5 TB of data storage. This will be at a price tag of about $25k. These drives will be spread across 2 MSA60 HP enclosures.
Ok, lets say that is all there is to it on the data storage side.
What will come in to play with a DB that big on the admin side. How should I approach this in terms of backups, indexes, etc to make sure the DB stays online and is tuned for best performance.
I am just trying to find an entry point in preparing for what appears to be something that can get out of hand very quickly.
Any input or direction would be helpful.
Thanks
gb
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply