We have a HP MSA70 with 20x300gb 2,5" 10k disks allocated for our SQL2008 server.
Our usage of the SQL server is:
50 concurrent users of our ERP system(Visma business) - 70/30 read/write.
2500 users pr. day on our website (CMS system + eshop) - (projected 5000 user by the end of the year)
A BI system that extracts data 8 times a day with extremely heavy queries (including production ours) **the BI system doesn't do any writes to these disks - only reads.
Atm. the array is setup as an RAID10, and thats it - all data+log+tempdb is on this array. We periodically experience that our ERP system becomes very slow, and we found out that it's mainly when our BI system is getting data (which last's approx. 30 mins everytime.)
I've been researching for quite some time now, but im still in doubt of how we should provision our spindles. My idea is:
8 disks raid10 - OLTP db's, mainly our ERP system (which consists of 10 db's) + tempdb
8 disks raid10 - Log files for all databases
4 disks raid5 - OLAP db's, transactional replicated copies of our ERP db's where our BI system get's it's data from. ++ our website.
My biggest concern about the above is, if i should take 2 disks from the log array, and add them to the "OLTP", so it get's 10.
I know that's not exactly enough to go on to come up with an exact answer, but i am looking for some guidelines and good ideas :-)