I know this has been asked before, but it also seems to produce differing thoughts, but all the threads I've read lack something specific for me to glean and make a decision, so I offer this up. I want to know which route to take to config an SQL server RAID array.
My question is about what type of Drive/RAID setup will give me the best performance for SQL database. (I have options listed below.)
Details (in case they help):
SQL Server build on a Dell PowerEdge R620, Windows 2012 Server R2, SQL Server 2012 Standard SP2.
8- 10k SAS drives. Drive SPACE is not an issue.
HW RAID (Perc H710p) to take heat off the processor.
SQL DB is 15GB. Potential growth to double it's size. Software that uses the DB is probably NOT very WRITE INTENSIVE.
I'm aware that this server is loaded probably to the idea of overkill. But other reasons have made this the best server to use on premises for SQL. Plus, the server is critical to us and we also want to config it to last.
Info: I will add, I am having a hard time giving up on the idea of using 1 or 2 of my 8 drives as global hotspares. I have seen them on the chopping block for some tech advisors. I am a one man IT shop and have been given a directive to make sure the server stays up while I'm out of office 5 or 6 weeks a year. Beyond surviving one drive down, I need to know that i can survive the "lost 2 drives before you got back". I need to know that the server has something that will kick in even on the darkest day.
Also because the current database is operating 'adequately' on Windows 2003, SQL2005 with C:\RAID1 and D:\RAID5 (DB and Data files), I wonder if any option changes in RAID configs will be felt, but I was about to build the server as such:
C:\OS (2drives in a RAID1 for OS and SQL executables)
D:\DATA (4drives in a RAID10) (for the SQL database and DATA directories)
2 drives as global hot spares.
But I have found threads suggesting OPTION 2:
C:\ OS and SQL (6 drive RAID10)
plus 2 global hotspares?
So before someone tells me to lose the hotspares, I would be curious which route would be suggested between the strengths/weaknesses of OPTION1 vs. OPTION2?
Is RAID10 with 6 drives potentially going to give me better SQL performance than splitting out the OS\RAID1 and a DATA\RAID10.
To confuse my 6 drive issue, I have seen comments such as: "RAID10 in this scenario should stay with 4 drives, or 8 drives (NOT 6 drives). A 6-drive RAID10 introduces an inefficiency and a bottle neck."
I would love to know if there is merit to that point.
Finally if the hotspares are really a detriment to the above thinking, I am open minded. That would bring into play a potential
OPTION3: 8-drive RAID10
OPTION4: C:\OS (4-drive RAID10) with D:\DATA (4-drive RAID10)
OPTION5: A 2-drive RAID1 each for OS, DATA, LOGS, Hotspares?
Some suggestions politely hint that the strengths of that mentality aren't as strong nowadays.
So at this point, I'm pretty confused about small changes and lack the experience to know if they make any perceptible differences.
The thing that won't change is that I need to config this server and get it into production. I hope to protect against my worst case scenario, and also give SQL the best response time we've seen yet.
Reality has me suspecting I won't feel the difference, so it's a waste of ADMIN $$ to chase this much further. If I had testing time I would do it. I have done what I can and checking my facts/logic with all of you is the last/best attempt before I make a decision. Thank you in advance for your wisdom.