August 6, 2013 at 11:07 am
I have a 2 node failover cluster running SQL 2012 enterprise. The purpose of the server is to host a reporting database (no OLTP workloads).
The servers are 12 core Sandy Bridge Xeons, 128GB RAM, with 4x 145gb 15k rpm spindles internally.
For storage I have an IBM DAS with 24 identical spindles. 300GB, 10k rpm. The DAS is connected via 6gb SAS.
The DAS supports RAID levels 0/1/5/6/10
Just looking for some advice on how to carve this DAS up. I need to host the reporting database, the staging database, the Business Objects Enterprise database (20 Gig at present), the logs for said databases, and of course the system databases / TempDB. All of this data can be re-acquired from the production system, so the reporting and staging databases are running in simple recovery mode. Backups are done to some slower disks on a Netapp SAN.
Current Sizes & Trends
Reporting DB: 1.5TB, growth trend ~650GB/year
Reporting Log: 80GB, no growth past 6mos
Staging DB: 50 GB, no growth past 6mos
Staging Log: 50 GB, no growth past 6mos
BOE DB: 20 GB, minuscule growth
BOE Log: 450 MB, minuscule growth
TempDB DB: 65 GB
TempDB Log: 2 GB
The write workload happens between 1 and 5:30 AM every morning as part of an ETL process; then the scheduled BOE reports kick off at 6:00 AM, followed by ad-hoc reporting throughout the rest of the day.
I know a lot more about query optimization and such than I do about storage architecture, so I'd really like to get some advice on the best way to utilize what I have to get the best performance, while getting 2-3 more years before having to purchase additional storage capacity (based on current growth trends).
**Edit added memory to server specs.
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
August 8, 2013 at 11:10 am
This is the age old question and I've never gotten a good answer...ever. I've asked SQL MCMs, MVPs, trainers and never been able to get a good answer. Its always "well, it depends...." with no explanation. I've done my fair share of reading on storage architecture and can't come up with a good answer myself, either. Am I just at Google?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply