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?
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:
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.