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...
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.
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.
- Should the log disk be RAID 1, RAID 0 or other?
- Is the overall solution above the best option for me? If not, why