patternsqlMinor
SQL Server disk layout
Viewed 0 times
sqllayoutdiskserver
Problem
For a new SQL Server installation, I plan a disk layout.
My new server has a 10 disk bay, I also plan to use 256GB SSD as 5x Raid1.
My idea is:
I checked some discussion, but just don't know which one will be better solution, also my questions are:
-
What's better, OS separately and SQL Server + user DB data together or OS+SQL Server on one disk and USER DB data separately
-
For tempdb need mirrored disk??
-
Tempdb logs with tempdb data on same disk or tempdb logs together with USER DB logs?
My new server has a 10 disk bay, I also plan to use 256GB SSD as 5x Raid1.
My idea is:
- OS install W2k19
- SQL Server 2019 standard + USER DB data
- USER DB logs
- tempdb
- Backups
I checked some discussion, but just don't know which one will be better solution, also my questions are:
-
What's better, OS separately and SQL Server + user DB data together or OS+SQL Server on one disk and USER DB data separately
-
For tempdb need mirrored disk??
-
Tempdb logs with tempdb data on same disk or tempdb logs together with USER DB logs?
Solution
In my experience, separating data and log files from the O/S is a frequently found default setup used by experienced DBAs. Use Windows Backup (or some other full-disk backup system) to backup the O/S drives. Use SQL Server backup to backup the data and log files. Backing up the O/S allows you to easily restore the entire thing to a different machine as part of disaster recovery.
There are two schools of thought for the location of SQL Server data files and log files.
-
Brent Ozar thinks keeping both data and log on the same volume is a good idea since it reduces the chances that a disk problem will result in the database going offline. As long as you have good backups, this is a sound strategy.
-
Other people1 recommend having the data separately from the log files which allows you to provide better performance for both since you'll have doubled the I/O capacity. This can be an important factor if you have dedicated local devices for each volume, and they're not actually on the same SAN or RAID set.
It makes a lot of sense to have tempdb on it's own volume, and best to keep that local if you can. Throw as many resources as you can at it. As in putting it on a fast local SSD can have real performance benefits, especially if you have queries that spill to tempdb.
I tend to put the SQL Server installation files on their own volume, but this is not strictly necessary.
If the data you'll be storing in SQL Server is important, and honestly what data isn't, then you must ensure the location where you store backups is wholly unrelated to the location where you store the data and log files. In other words, don't store backups and data on the same volume, SAN, or RAID set. Also, no backup should be considered "good" unless and until you successfully restore that backup and validate its integrity via
1 - myself included
There are two schools of thought for the location of SQL Server data files and log files.
-
Brent Ozar thinks keeping both data and log on the same volume is a good idea since it reduces the chances that a disk problem will result in the database going offline. As long as you have good backups, this is a sound strategy.
-
Other people1 recommend having the data separately from the log files which allows you to provide better performance for both since you'll have doubled the I/O capacity. This can be an important factor if you have dedicated local devices for each volume, and they're not actually on the same SAN or RAID set.
It makes a lot of sense to have tempdb on it's own volume, and best to keep that local if you can. Throw as many resources as you can at it. As in putting it on a fast local SSD can have real performance benefits, especially if you have queries that spill to tempdb.
I tend to put the SQL Server installation files on their own volume, but this is not strictly necessary.
If the data you'll be storing in SQL Server is important, and honestly what data isn't, then you must ensure the location where you store backups is wholly unrelated to the location where you store the data and log files. In other words, don't store backups and data on the same volume, SAN, or RAID set. Also, no backup should be considered "good" unless and until you successfully restore that backup and validate its integrity via
DBCC CHECKDB, or some other method.1 - myself included
Context
StackExchange Database Administrators Q#256341, answer score: 4
Revisions (0)
No revisions yet.