May 27, 2011 at 9:25 am
I need some opinions from sql DBAs. The managment is thinking of acquiring a new set of data. Based on the early estimates it would triple or even quadrupple the db in size. So storage wise I'd expect it to get to around 10-11TB. Currently there are some nightly processes that run for quite some time, the longest takes 3-4 days to run (db size is 2.5TB). What kind of hardware/infrastructure should we consider? Performance is very important to us and the processes would have to at least remain in the same time frame or be cut down in run time. Any thoughts or comments? Currently we're running SQL 2005 Enterprise, but once we're about to aggegrate the new dataset we'd like to move to the latest version of SQL engine.
Another very tricky aspect of all this is to replicate all of this data to our remote datacenter located in a different state.
Thanks
Luk
May 27, 2011 at 9:55 am
I have a similar project underway. This is what my environment looks like -
32-core Xeon blade server
192GB RAM
1TB System Partition (internal drive)
20-space 6gbps attached storage in the following configuration:
'Load Database' - partitioned across 2x4 1TB RAID1 drives
'Load Log' - 2 500GB RAID 0 drives (your needs for log redundancy may differ)
'Transform Database' - partitioned across 2x4 1TB RAID1 drives
'Transform Log' - 2 500GB RAID 0 drives
'TEMPDB' - 2 500GB RAID 0 drives
'TEMPLog' - 2 500GB RAID 0 drives
May 28, 2011 at 4:18 am
If you're moving up to 10TB systems and you haven't handled that level before, I'd strongly recommend getting some consulting help in on your project. Anything past 1-2TB requires specialized knowledge that you don't want to develop through trial & error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2011 at 4:59 am
some nightly processes that run for quite some time, the longest takes 3-4 days to run (db size is 2.5TB)
A nightly job taking several days? I truly hope this is just a typo...
If not you might get someone in to have a look at your current system and tune it before even looking at moving to the 10TB level.
This might even require to get two consultants in: one for tuning the mid-size system and after that's done one for the hardware, storage and file setup of the 10TB.
I'm sure there are a few consultants around being able to do both, but that's not always the case...
May 28, 2011 at 6:24 am
LutzM (5/28/2011)
some nightly processes that run for quite some time, the longest takes 3-4 days to run (db size is 2.5TB)
A nightly job taking several days? I truly hope this is just a typo...
If not you might get someone in to have a look at your current system and tune it before even looking at moving to the 10TB level.
This might even require to get two consultants in: one for tuning the mid-size system and after that's done one for the hardware, storage and file setup of the 10TB.
I'm sure there are a few consultants around being able to do both, but that's not always the case...
Yes a nightly job runs several days, but it's not all SQL, most of it is actually Perl. This is an outdated process that needs fixing. I never really liked the way things are being handled, but these improvements have been defferred from one year to the next, etc.
SQL jobs that run strictly against the database take a few hours at the most.
May 28, 2011 at 7:31 am
Luk (5/28/2011)
...Yes a nightly job runs several days, but it's not all SQL, most of it is actually Perl. This is an outdated process that needs fixing. I never really liked the way things are being handled, but these improvements have been defferred from one year to the next, etc.
SQL jobs that run strictly against the database take a few hours at the most.
Since you're requested to investigate the steps required to handle a much larger data volume it might be the right moment to ask for attention/time/budget/support to fix it (or get it fixed). 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply