2

I am in the process of building a new server to run our SQL Server databases (we have many TB's of data). I am looking for some help on what I should request for hard drives. My thought was to have 5 logical drives:

Drive 1: System drive Drive 2: Database drive for DB's with a lot of read/writes Drive 3: Database drive for other DB's and TempDB Drive 4: Log files Drive 5: Backups and other storage

Would this be a good setup? What type of disk (SSD or other) and RAID should I use for each drive? Keep in mind I'm trying to get the most bang for the buck. I don't have an unlimited budget, and I want to spend a lot on RAM.

Mike S
  • 121
  • 1
  • 1
    That's a bit tricky; hardware config questions tend not to age well, and Stack Exchange sites prefer questions which have a single fairly objective answer. Consider this question, which is similar but may not help you simply because a lot has changed in seven years. – Jon of All Trades Jun 27 '19 at 01:44

1 Answers1

2

If this is local storage, layout matters a lot more. If it's SAN storage, things are a little bit different.

Here's what I do for local storage:

  • Put big data files (mdf, ndf) for user databases on non-SSD storage
  • Put log files (ldf), and tempdb (all files) on SSD

Data files are typically around 80-90% reads to writes. You can mitigate the "slow" part of reading from them by, as you say, "spend[ing] a lot on RAM". RAID 5 or 6 is the usual choice here.

Log files and tempdb are different, and they need to absorb a lot of writes quickly. They're a much better fit for faster tiers of storage. RAID 10 is the usual choice here.

The system drive storage class isn't terribly important here, but I'd make a note that backups either shouldn't be done locally, or they should be copied to redundant storage. If you lose your backup drive, you, well... Don't have backups.

If this is SAN storage, what matters even more than drive layout and storage tiers is making sure you have adequate networking paths between the server and the SAN.

For more details why, see this Q&A: SQL Server has encountered occurences of I/O requests taking longer than 15 seconds.

Erik Darling
  • 40,781
  • 14
  • 130
  • 456
  • This would all be local storage. The reason that my original plan had two different database drives is because we do have a few databases where it is not uncommon to run an update or delete that affects 50 million rows of data at once. Would you still recommend putting a database like that on non-SSD? – Mike S Jun 26 '19 at 21:24
  • @MikeS yes -- the shock absorber for that would be lots of memory, and the log file being on fast storage. Of course, the wiser thing to do would be to batch your modifications. – Erik Darling Jun 26 '19 at 21:25