First - while I'd totally be down for advice; I'm also interested if there are any good ways of measuring/collecting data in advance that I could use to determine which 'rules of thumb' to follow and which to skip.
I can't change the schema significantly for the application, and while I've got some suspicions that the developers went index-happy; I don't want to take out their indexes and replace them just yet.
I've got a decent-sized database for a telephony application, about 60GiB or so. The data breaks up into, mainly:
- TRAFFIC table (constant sequential writes, occasional long scans along any number of indices for reporting, grows rapidly. Associated Index is huge as well.)
- BILLING table (constant sequential writes, often scans along account_info for user information)
- Everything else (constantly random access reads and writes, more frequent than the other two tables.)
I'm on a single-core machine right now with 3Gb devoted to SQL Server and disk cache in SQL Server 2005. The msdb database and OS are on a 15k hard drive, RAID 1. The log files and backup drive are on a 15k hard drive, RAID 1. The telephony DB is all on 2 x 15k hard drives, RAID 10. (That's eight drives total.)
I'm upgrading hardware in a big way. Going to have SQL Server 2008 R2, 12 cores, 32Gb of RAM on Server 2008 R2 x64. It'll have four internal HDs, probably for OS and backup, but the biggest change is the attached storage - 12 x 15k drives on an external SAS interface.
We're a bit CPU/I/O bound on the current server, but not enough to bother much - this new servers going to heal a lot of sins - but I'd still rather do it 'right' this time.
I could do a massive RAID 10 of 6 disks and throw the entire DB onto it, but I've been considering breaking up TRAFFIC and it's index files onto separate partitions - and possible BILLING as well. In addition, the Everything Else might do well in it's own File.
Then, of course, there's always RAID 10 vs RAID 5/6, vs RAID 50/60 to consider.
I'd love some guidance on how to approach this decision and how to determine whether it's worth breaking up the drives to put TRAFFIC/BILLING seperate - and whether I'd be fine with two filegroups (PRIMARY and USERDEFINED) or if I'd need more.
By the way, sizeof(TRAFFIC) = sizeof(BILLING) * 2 >> sizeof(EVERYTHING ELSE)