Optimal RAID setup for SQL server
We have an SQL 2005 database backend for our website, currently about 10GB in size. There are a lot more reads than writes, though I don't have the exact statistics.
We're upgrading our database server and I was thinking of getting 4 disks and setting them up in two RAID 1 arrays - one for the data files and the other for the OS and log files. Would this be the optimal set-up or would RAID 5 be better for the data files? RAID 10 gets a bit pricey and is probably overkill for us.
At this stage SQL Server should keep much of the database in RAM (8GB), but it will grow, so I don't want to entirely rely on that.
Edit: we definitely want redundancy on a production server, so RAID 0 by itself is out. RAID 10 is nice, but might be a bit expensive for us.
Answer
Your concept of using independent RAID 1 mirrors is the correct strategy.
We have implemented similar scenarios at my work and they work very well.
RAID 1
RAID 1 gives you the speed of 1 disk for writing but 2 disks for reading.
When you write data to a RAID 1 array, it has to write that data to both disks, so you do not gain any performance increase, however this is where you get your data security.
When reading from a RAID 1 array the controller will read from both disks as they have the same data on them.
RAID 5
This is useful for protecting larger amounts of data. The cost of RAID 5 increases a lot slower than RAID 1 (or RAID 0+1 once you are doing capacities beyond the size of the individual disks) for the same amount of data.
If you want to protect 600gb in with RAID 5 you can achieve that with 4x200gb drives or 3x300gb drives, requiring 800-900gb of total purchased drive space. RAID 1 would be 2x600gb drives requiring 1,200gb of purchased space (with 600gb drives being quite more expensive) or RAID 0+1 allowing you to use less expensive capacity drives (ie: 4x300gb or 6x200gb) but still requires a total of 1,200gb of purchased space.
RAID 0+1
Offers similar advantages as RAID 1 taking it up another notch with the striping across disks. I am assuming that if you are concerned about higher simultaneous reads, you will also be using multi-processors/multi-cores. You will be processing multiple queries at once and so the striping isn't going to help as much. You would see a better advantage on a RAID 0+1 for single applications using large data files, such as video editing.
When I was researching this same issue a while ago for a customer I found this article to be very interesting http://blogs.zdnet.com/Ou/?p=484. On the second page he dicusses the change from a RAID 0+1 to independent RAID 1 arrays creating a lot of performance improvements. This was on a much larger scale (a 20 disk and 16 disk SAN) but same concepts. The ability for SQL Server to load balance the data between multiple volumes instead of using just basic uninformed striping of RAID 0+1 is a great concept.