SQL Server 2008R2 minimum RAID/Disk config

Question

I want to take advantage of the great price/performance offered by the new Intel E5-2620 (v2) machines being offered by some dedicated server webhosts.

Unfortunately most of these E5-2620v2 dedicated server offerings are a 1CU and limited to 4 drive bays. My production SQL 2008 R2 (2GB) database is supporting an auction web application with a very high frequency of small updates by many users.

I currently have an 8 drive configuration on my SQL Server with: - RAID 1 array (OS & Program files) - RAID 1 array (SQL log files) - RAID10 (SQL Data & TempDB)

My question is would I creating a disk performance issue for my OLTP SQL server if I went with: 1) A single 4 drive RAID 10 array (600 GB SAS drives with OS and all SQL Files). Would I be better off going with a much slower & more expensive machine that supports 8 drives?

2) I found one that offers 2 internal SSDs plus 4 hostswap drives, what would be the recommended disk layout. Is it still recommend to have SQL Logs on separate disk with high frequency of small db updates? 2 Drive SDD RAID 1: OS & Temp files & SQL Log file 4 Drive RAID 10: SQL DATA & System DB files, TempDB

3) Is there still a significant performance difference between SATA IIIs and SAS 15K disks as some wehosts only offer SATAs?

Many thanks in advance for any advice.
arf

Answer 1

For a 4 bay configuration with a high insert/update workload I would go with one of these:

1) Spindle only

• One RAID1 for the log. Back this up with a a few hundred MB of write cache
• One RAID1 for the data files and tempdb

Rationale: you will mostly be writing to the log. Latency of log is king for OLTP and it doesn't get much better than a RAID1 with a write cache. You can use SATA - which should give you at least 80MB/sec sequential writes at latency less than 1ms.

2) SSD plus spindle

• One RAID1 for log with same configuration as the above.
• One RAID1 for data files and tempdb

Rationale: This config will favour higher tempdb load and would be good if you are skimping on DRAM (as this will cause checkpoint activity to go up and drive read I/O)

3) Pure SSD

• Put them in a RAID5 if you are not worried about wearing them out.
• If you are using consumer grade SSD - put them in a RAID10.

Rationale: RAID5 with 4 x SSD is fast enough for nearly all purposes.

Everything goes on same LUN

4) 8 bay, spindle only config

• Put 2 disks in RAID1 for the log file.
• Rest goes into a RAID10 or RAID5 (RAID10 if you are worried about tempdb)

Overall: always prefer a 2 spindle RAID1 with a write cache for logs. It's plenty for 99% of all cases (80MB/sec for SATA and up to 120MB/sec for SAS).