08-04-15, 15:16 #1Registered User
Provided Answers: 1
- Join Date
- Oct 2010
- Orlando, FL
Unanswered: Best Practices for Data Paths in SQL Server?
I'm curious if there's a "best practice" for setting up the data directories MS SQL will use for each operation? I've allocated independent disks for things like C:\ (OS), E:\ (DATA), etc etc etc but I'm not familiar w/ MS SQL to understand how DBA's commonly configure the folders under each unique disk for things like DATA, LOGS, BACKUP, INDEXES, and TEMPDB. Should I have an identically name folder as show below in my example?
You can see I've just mirrored the drive name to a new folder under the partition so data is being written to: F:\DATA and E:\LOGS. Is this considered correct / good practice? I assume naming the folder in each mount point to whatever I logically called the drive is correct but if I should change how I configure my drive paths above, please let me know. I'm trying to learn common good SQL Server practices and while I work on properly installing SQL Server 2012/2014, I want to make sure I configure my partition names SQL will utilize correctly.
Thanks for any feedback!
08-04-15, 16:43 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Currently, I use from one to seven drives in an idea configuration. In an ideal configuration, I use:
C: Local OS and swap file
D: SAN MS-SQL DATA
E: SAN Build and Backup files
F: SAN MS-SQL Log files
G: SAN MS-SQL TempDB data and log
O: DVD VM CD/DVD mount
Q: SAN Cluster Quorum
I'm moving to a slightly different configuration, especially for clusters with Availability Groups and/or multiple SQL Server instances.
C: Local Swap and OS files
D: MPs Mount point holder for SQL instance 1
** LUN1 SQL Instance 1 MS-SQL Data
** LUN2 SQL Instance 1 Log files
** LUN3 SQL Instance 1 Tempdb
E: LUN Build and backup files
F: MPs Mount point holder for SQL instance 2
** LUN1 SQL Instance 2 MS-SQL Data
** LUN2 SQL Instance 2 Log files
** LUN3 SQL Instance 2 Tempdb
G: MPs Mount point holder for SQL instance 3
** LUN1 SQL Instance 3 MS-SQL Data
** LUN2 SQL Instance 3 Log files
** LUN3 SQL Instance 3 Tempdb
O: Mount DVD/CD ISO mount point
Q: SAN Quorum
In the case of a development server, it is possible to collapse everything onto a single drive. If need be, you can make almost anything work, but this is my new preferred configuration.
Reporting Services, SSIS, etc. can each have a drive too.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.