What's the modern recommendation for SQL Server's physical storage configuration

All we need is an easy explanation of the problem, so here it is.

It’s 2022. I’m setting up a SQL Server that will support a single medium size web app. I have physical hardware with 4 NVMes that I need to configure. What’s the best practice? On which physical drives do the volumes for OS, data, logs, tempdb, and backups go? Or should I just go RAID 10 and create a volume for each concern?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’ll keep this short and sweet, because you have one of the simplest cases:

  • Drive 1: OS
  • Drive 2: Data (MDF) Files
  • Drive 3: Log (LDF) Files
  • Drive 4: tempdb

Backups I’d recommend offloading to a remote place so you’re not hosed if the server itself has an issue. (Been there, done that, not fun.)

I’d recommend pre-growing your Data and Log files to the size of the disks (since each disk is dedicated to each function), and monitoring their consumption inside the SQL Server instance, to maximize performance.

Alternatively, follow Brent Ozar’s recommendation on auto-growth settings for your database.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply