Suggestions on how to carve this up?

  • CodeMuddler

    SSC Eights!

    Points: 845

    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.

  • aderuwe

    Grasshopper

    Points: 21

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply