SQL Server File System Layout

SQL Server File System Layout ├─── Central Theme: Separation, Organization, Performance, Manageability │ ├─── 📂 Operating System Volume (C:\) │   ├─── Purpose: Windows OS, Paging File, essential system files │   ├─── RAID:  OS volume - RAID 1 (Mirroring) is good, but not performance critical for SQL Server │   └─── Contents: │       ├─── Windows OS Files │       ├─── Pagefile.sys │       └─── (Ideally) Minimal Application Data - Keep C:\ clean │ ├─── 💾 Binary Volume (S:\  - Example, can be other drive letter)  <-- **Custom Binary Path!** │   ├─── Purpose: SQL Server Program Files (Binaries) │   ├─── RAID:  Not performance critical, RAID 1 or even single disk is acceptable. Focus on redundancy over speed for binaries. │   └─── Contents: │       └─── S:\SQLBinaries\ (Example Root Folder) │           ├─── MSSQL16.INSTANCE1\ (Instance Root Directory - e.g., MSSQL16.MSSQLSERVER) │           │   ├─── MSSQL\ (SQL Server Engine Binaries) │           │   ├─── Binn\  (Executable binaries) │           │   ├─── <... other binary folders and files ...> │           └─── MSSQL16.INSTANCE2\ (For Named Instance "INSTANCE2") │               └─── ... │ ├─── 🗄️ Data Volume(s) (D:\, E:\, F:\...) │   ├─── Purpose: SQL Server Data Files (.mdf, .ndf) for user databases │   ├─── RAID:  RAID 10 (Recommended for performance and redundancy), RAID 5/6 acceptable for read-heavy workloads │   └─── Directory Structure (Example for D:\) │       └─── D:\SQLData\ │           ├─── INSTANCE1\ │           │   ├─── Database1\ │           │   │   ├─── Database1.mdf │           │   │   ├─── Database1_02.ndf │           │   │   └─── ... │           │   ├─── Database2\ │           │   │   ├─── Database2.mdf │           │   │   └─── ... │           │   └─── ... │           └─── INSTANCE2\ │               └─── ... │ ├─── 📜 Log Volume(s) (L:\, M:\, N:\...) │   ├─── Purpose: SQL Server Transaction Log Files (.ldf) for user databases │   ├─── RAID:  RAID 10 (Recommended for optimal write performance and redundancy) │   └─── Directory Structure (Example for L:\) │       └─── L:\SQLLogs\ │           ├─── INSTANCE1\ │           │   ├─── Database1\ │           │   │   └─── Database1_log.ldf │           │   ├─── Database2\ │           │   │   └─── Database2_log.ldf │           │   └─── ... │           └─── INSTANCE2\ │               └─── ... │ ├─── ⏳ TempDB Volume(s) (T:\, U:\, V:\...)  <-- Separate Data & Log! │   ├─── Purpose: TempDB Data and Log files - **CRITICAL for Performance** │   ├─── RAID:  Fast Storage (SSDs ideal if possible). RAID 10 or RAID 0 (Data - carefully consider), RAID 10 (Log) │   └─── Directory Structure: │       ├─── T:\TempDBData\  (TempDB Data Files) │       │   └─── INSTANCE1\ │       │       ├─── TempDB_Data1.mdf │       │       ├─── TempDB_Data2.ndf  (Multiple files recommended) │       │       └─── ... │       └─── U:\TempDBLog\  (TempDB Log File - Separate Disk/Volume from Data) │           └─── INSTANCE1\ │               └─── TempDB_log.ldf │ ├─── 💾 Backup Volume(s) (B:\, P:\, Q:\...) │   ├─── Purpose: SQL Server Backup Files (.bak, .trn) │   ├─── RAID:  RAID 5, RAID 6, or RAID 10 (depending on needs) - Capacity and reasonable speed │   └─── Directory Structure (Example for B:\) │       └─── B:\SQLBackups\ │           ├─── INSTANCE1\ │           │   ├─── Database1\ │           │   │   ├─── Full\ │           │   │   │   └─── Database1_Full_YYYYMMDD.bak │           │   │   ├─── Diff\ │           │   │   │   └─── Database1_Diff_YYYYMMDD.bak │           │   │   └─── Log\ │           │   │       └─── Database1_Log_YYYYMMDD.trn │           │   ├─── Database2\ │           │   │   └─── ... │           │   └─── ... │           └─── INSTANCE2\ │               └─── ... │ ├─── 🗂️ Full-Text Catalog Volume (Optional - F:\) │   ├─── Purpose: Full-Text Catalog files (if used heavily) │   ├─── RAID:  SSD or RAID 10 for performance if full-text search is critical │   └─── Directory Structure (Example for F:\) │       └─── F:\SQLFTCatalogs\ │           ├─── INSTANCE1\ │           │   ├─── Database1\ │           │   │   ├─── Catalog1\ │           │   │   └─── <Full-Text Catalog Files> │           │   └─── ... │           └─── INSTANCE2\ │               └─── ... │ ├─── 📂 FileStream/FileTable Volume (Optional - W:\) │   ├─── Purpose: FileStream/FileTable data (if used heavily) │   ├─── RAID:  Depends on workload (RAID 5, 6, or 10) │   └─── Directory Structure (Example for W:\) │       └─── W:\SQLFileStream\ │           ├─── INSTANCE1\ │           │   ├─── Database1\ │           │   │   ├─── FileStreamData1\ │           │   │   └─── <FileStream Files> │           │   └─── ... │           └─── INSTANCE2\ │               └─── ... │ └─── 📝 Error/Trace Log Volume (Optional - E:\, TR:\)     ├─── Purpose: SQL Server Error Logs, Trace Files, etc.     ├─── RAID:  Less critical, standard storage acceptable     └─── Directory Structure:         ├─── E:\SQLErrors\  (Error Logs)         │   └─── INSTANCE1\ (or just root if single instance)         │       ├─── Errorlog.log         │       ├─── Errorlog.1         │       └─── ...         └─── TR:\SQLTraces\ (Trace Files)             └─── INSTANCE1\ (or just root if single instance)                 ├─── TraceFile_YYYYMMDD.trc                 └─── ... * **Drive Letters are Examples:**  Use drive letters that are available. Mount points are an alternative. * **Instance Specific Folders:**  Structure includes instance-specific folders if you have multiple SQL Server instances on the same server, making it organized. * **Optional Volumes:**  Full-Text, FileStream, and Error/Trace Log volumes are optional and depend on your usage and needs. * **Binary Path Highlight:** The "Binary Volume (S:\)" clearly illustrates the custom path for SQL Server binaries, moved off the C:\ drive. * **RAID Recommendations are General:**  Choose RAID levels based on your specific performance, redundancy, and budget requirements. RAID 10 is frequently recommended for Data and Logs. * **Adapt to Your Needs:** This is a template; adjust the number of volumes, drive letters, RAID levels, and optional volumes based on your specific SQL Server environment and hardware.