What are the best practices when it comes to arranging your mdf's and ldf's on disk? Are there particular HDD configurations (RAID, SAN etc.) that are particularly advantageous?
Edit: I'm interested in both the simpler solutions (e.g. for configuring the local instance on my dev box) and the more complex solutions (for production servers under heavy load)
I think Matt Whitfield has some good advice, and I'll add in what I consider to be the order in which I set things up.
I've used R5 in many places, but usually because of cost considerations. There are good reasons at http://www.baarf.com/ not to use it, and I won't repeat them here. If you can afford it, go with R1 or R10. DO NOT use R0.
The first thing you need to do is physically make sure backups are separate from data. If you have two physical drives (or arrays/LUNs), then put the OS, pagefile, and backups on one, data on a second.
If you can move to 3 separate drives (physical), separate logs from data.
If you can go to 4, I would either move tempdb (data), the pagefile, or separate out heavily used databases/filegroups from each other. You have to measure the writes and load for each of these items and decide what to move.
One of the simplest things you can do is to use a different physical disk for the SQL server data files (.MDF) and for the SQL Server log files (.LDF). The reason behind this is that .MDF files are accessed randomly, while .LDF files are accessed sequentially.*
First and foremost - understand your data profile before you make a decision. RAID 5 disks are more suited to situations which involve a lot of random writes, whereas RAID 10 can be a lot faster for infrequently written but heavily read data, or for sequential writes.
Secondly - give yourself time to test and make an appropriate judgement. It can be a pain setting up SANs multiple times, especially seeing as in a cluster environment the SQL Server cluster resource must depend on the disk resources it uses - but if you have the time to test it and make some appropriate measurements, it can save a lot of time further down the line.
Thirdly - a large number of smaller disks can often out-perform a smaller number of large disks for random access, whereas larger disks can be beneficial for sequential access.
Fourth - always include a hot-swap spare in your array. Lightning does strike twice!
This is just based on my experience, hope it helps.
Microsoft has some recommendations here: technet.microsoft.com/en-gb/library/cc966414.aspx technet.microsoft.com/en-gb/library/cc966534.aspx
Make sure you consult your SAN vendor. They may have specific and proven recommendations for using their hardware with SQL Server.
