Sql-server – How should I configure the RAID array of SSD drives on the SQL Server

configurationsql server

I'm building a SQL Server with 48 GB RAM, 1 CPU, & 8 SATA III (6GB/s) SSD drives (128 GB Crucial m4) and an LSI MegaRAID controller (SAS 9265-8i). I expect the typical work load to be mostly reads. There will be some periods of heavier write activity (hourly data syncs w/ 3rd party data providers – nightly backups), but the I suspect the typical read/write ratio is about 90% reads/10% writes.

Option 1:
Logical Drive C: – RAID 1 (2 physical drives) – OS
Logical Drive D: – RAID 10 (6 physical drives) – DB files/logs/tempdb/backups?

Option 2:
Logical Drive C: – RAID 1 (2 physical drives) – OS
Logical Drive D: – RAID 1 (2 physical drives) – Db Files
Logical Drive E: – RAID 1 (2 physical drives) – log files/backups?
Logical Drive F: – RAID 1 (2 physical drives) – tempdb

Option 3:
Other suggestions?

I'm thinking option 1 would give me better performance, since all DB activity would be striped across 3 drives (and mirrored across the other 3 in the array), although option 2 seems to mimic conventional wisdom (which appears to apply more to mechanical drives than SSDs). It seems like Stack Overflow has gone with option 1.

I'm guessing with SSD's it's OK to put every thing on a single logical drive since your server is probably more CPU constrained instead of I/O constrained at that point?

Another question I have is where should I place the nightly backups? We don't want backups slowing down the rest of SQL server, and I'm guessing writing the backups the same location as the logs is a good practice because the read/write behavior in both those cases is sequential writes.

Best Answer

Conventional wisdom about RAID doesn't apply well to SSDs. They don't really need striping (RAID0). They are prone to failures by-design, but RAID-1 is usually not the right answer for SSD for two reasons: a) is wasteful, halves the capacity of the SSD array (and they are pricey) and 2) SSDs failure characteristics leads towards both drives in the mirror to fail at very close intervals (ie. correlated failures) and thus render the entire array useless. See Differential RAID: Rethinking RAID for SSD Reliability for a lengthier discussion. Some have recommended using Raid-6 for SSDs.

Additionally, the conventional wisdom of SQL Server file layout doesn't apply to SSDs. I would recommend you watch SQL on SSDs: Hot and Crazy Love and go over the benchmark links in this answer.

Related Solutions

Sql-server – SQL Server configuration / specification recommendations and advice!

What I'd do

4 disks for RAID 10 for all log files
Disk RAID 5 for all data files + OS
Don't bother with RAM disks

Why?:

With 24GB RAM all databases will be in memory so RAID 5 doesn't matter because disk reads won't really happen
Write performance is determined by log file throughput
Data files are larger then log files so RAID 5 is more efficient

What isn't ideal:

Not having one volume per database log file
Separation of local backup drives
Not having separate RAID 1 for OS + binaries

Other options:

Define "heavy write": you could go to RAID 1 for LDFs and another for OS for less than a few million rows per day

Sql-server – tempdb on raid1 isolated or raid10 shared with high write db

I found these articles helpful:

Further reading suggests that the benefits of using RAID1 for log files (assuming isolated from data files) is lost when more than 1 log file exists on that RAID1 array. This is due to the sequential nature of the transaction log writes to disk. The sequential write benefit is lost when multiple log files are accesses on the same RAID1 array due to the random nature of access on a spinning disk. This would suggest that RAID10 is the better choice in a multiple DB environment unless you have the disks to isolate each log file.

These stats below sold me on proposal 3, isolating tempDB on RAID1 by stealing 2 disks from LOG array moving log array from RAID10 to RAID1. Basing much of this on RAID1s ability to maintain good WRITE speed.

TEMPDB is clearly under more stress than I have realised.

These table rankings ring true for snapshot values during normal operation (not just the accumulated totals) as we do have intensive out of hour routines.

TOTAL IO:

db.tempdb.mdf = 144,747,290,352

db.2.mdf =         100,482,243,080

db.2.ldf =           2,571,065,773

db.s.mdf =         1,702,508,040

db.s.ldf =           223,032,162

TOTAL READS:

DB.2.mdf =         84,851,614,280.00

db.tempdb.mdf = 72,271,813,552.00

db.s.mdf =         1,691,504,864.00

db.2.LDF=          93,822,304.00

TOTAL WRITES:

db.tempdb.mdf = 72,475,476,800

db.2.mdf =         15,630,628,800

db.2.ldf =           2,477,243,469

db.tempdb.ldf =   222,946,079

One possible concern maybe the additional of the tempdb ldf and mdf on the same raid1 array but if this is a problem tempdb.ldf can be moved to the log array.

These are helpful links that explain TEMPDB usage, what it does and how it may effect my apps:

Best Answer

Related Solutions

Sql-server – SQL Server configuration / specification recommendations and advice!

Sql-server – tempdb on raid1 isolated or raid10 shared with high write db

Related Question