Background
I am quite far into the process of setting up a 2 server SQL Server 2012 Always on availability cluster to provide redundancy with a readable secondary replica of all our databases.
Since the hardware was ordered we've had a professional DBA audit our database and he's suggested separating our data, logs and tempdb. Unfortunately we do not have the capacity for the ideal configuration he's suggested. I therefore need feedback on my ideas for a suitable compromise…
Current setup
single server has 8 hard drive slots. We have a 2 disk RAID 1 for the OS, a 4 disk RAID 10 for data, logs and tempdb and 2 spares. I want to change this to increase performance.
Proposed setup
2 servers, each with 8 slots. Synchronous replication in the AG with auto-failover and the secondary to be readable for backups and reporting.
OS leave as is, a 2 disk RAID 1
tempdb has the most stalls by a long way so first off I'd like to put that on a separate drive, a single SSD for optimal random reads and writes.
data The read/write ratio of our data files is about 12:1 so read performance is key here. Ideally for this drive we would use RAID 10 or 0+1 with 4 disks for redundancy but we don't have enough slots. As we have the redundancy and auto-failover of the AG (the data is replicated on an identical drive on another server) I'm proposing a 2 disk RAID 1.
logs We have 3 slots left and need optimal write performance. Again, redundancy is not an issue as the 2nd host in the AG will have a replica of the logs. I am leaning towards a 2 disk RAID 0 here to avoid the write overhead of RAID 1.
We would then have a slot spare for backups and anything else non-critical.
Question
- Should the log disk be RAID 1, RAID 0 or other?
- Is the overall solution above the best option for me? If not, why
Best Answer
I would only use RAID 0 for the logs if you're okay with having reduced disaster recovery capability. You won't be able to make log tail backups in the event of a disk failure, and will only be able to restore to your most recent backup. However, this may be less of an issue if you're running Availability Groups, particularly with synchronous mirroring. You generally won't have any performance overhead for RAID 1 vs. a single non-RAID disk, but you will have better I/O performance with RAID 0 instead of RAID 1 for an equal number of disks.
Be aware that tempdb also has both data files and log files, and those could end up interfering with each other (performance wise) if placed on the same volume. You may want to consider foregoing the spare disk and using it for tempdb logs instead if performance still isn't acceptable. If your C: array is large enough, you might be able to get away with using it for on-disk backups, though you should probably partition it to prevent runaway backups from filling your OS disk.