The question:
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?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
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