On the Fifth Day of Christmas…2011
On the fifth day of Christmas my Server Administrator gave to me… Five Volumes Minimum
Four Words to Live By…
Three Training Events…
Two Twitter Handles…
and Mirrored Disks with Parity
Five is a Magic Number
When initially configuring a SQL Server be sure to fight the basic drive of your server administrators to simple when it comes to creating your disk 
configuration; whether local storage or SAN-attached. A single C: volume means that your data, log and backup files for both user databases and system databases are hosted on the same volume as your operating system (an more often than not a variety of application files such as anti-virus programs.) This means simple issues that arise – such as not configuring transaction log backups to run as frequently as they should based upon transactional activity or auto-growth run amok could conceivably fill your operating system volume and bring the server down. My opinion: push for a minimum of five logical volumes. You’re the DBA. This is a SQL Server. You’re the boss. Be the SQL Server Tony Danza and stake your claim!
O/S Volume
Install the O/S and any SQL and non-SQL (if you must) application files here. You should not see considerable growth in these files and you should not encounter a noticeable performance hit on your server for doing so (at least for O/S and SQL binaries. I make no assertion or claim on your installing any non-SQL programs on a SQL Server.)
Data File Volume
Dedicate at least one drive for data files. If you have a robust system that could benefit from splitting tables into separate files and file groups then perhaps you’ll need more than one. I and just about every SQL Professional you’ll ever encounter will recommend RAID 5 for optimal read performance as SQL will read pages from the data files into memory to satisfy user requests. This is not to say that data files are not written to. They are. But not synchronously to satisfy requests in real time. They are written to periodically when transactional metadata is recorded from the transaction log.
Log File Volume
As goes with data so goes with log. A separate volume for your transaction log files in highly recommended. If possible the drive should be configured as a RAID 1+0 array to allow for optimal write speed since a user request involving an insert, update, or delete is not completed until the transaction log is updated with the pertinent information. Yes, it is a more-expensive option than a RAID 5 array. Yes, it’s a hard sell. No, I don’t often if ever succeed in getting a dedicated RAID 1+0 array for transaction logs, but yes, I do at least always have a separate RAID 5 array for transaction logs.
System Database Volume
Yes, a separate volume for the log and data files associated with master, model, and msdb. Can the log and data files live on the same volume? Yes. Can you host the data files for these three databases on the same volume as the data files for the user databases? Yes, and before you ask the same holds for the log files for user databases as well as master, model, and msdb. But I at least like to ask for this volume and cede defeat (without losing much) if need be.
tempdb Volume
You may notice I didn’t include tempdb in the itemized list of system databases mentioned above. This is because tempdb is on the whole more important to the fulfillment of user requests than the other three; sorting, hashing, calculations doomed to failure and performance cramps if tempdb’s file system is not robust. Segregation is key in my opinion. If possible dedicate a volume (be it RAID 5 or 1+0) to tempdb. A separate RAID 5 array for the tempdb data files (yes fileS – I recommend one data file per two-to-four cores assigned to SQL Server with two data files as a minimum and equally sized.) is nice as is a separate RAID 1+0 array for the associated log file for edge cases, but I’ve always been satisfied with a single RAID 5 array for both data and log files for tempdb.