Sizing guidelines for storage capacity on SQL Server systems
When sizing storage capacity for an SQL Server system, you need to consider the following SQL Server database components:
- Database files
- Transaction log files
- Tempdb and backup
Storage capacity
Database element | Space requirement | 20,000 nodes | 50,000 nodes | 100,000 nodes | 150,000 nodes | 200,000 nodes |
|---|---|---|---|---|---|---|
Database files | DB size | 20 GB | 50 GB | 100 GB | 150 GB | 200 GB |
Transaction log "Simple" | DB size * 0.2 | 4 GB | 10 GB | 20 GB | 30 GB | 40 GB |
tempdb | DB size * 0.1 | 2 GB | 5 GB | 10 GB | 15 GB | 20 GB |
Backup | DB size * 1.0 | 20 GB | 50 GB | 100 GB | 150 GB | 200 GB |
Total |
| 46 GB | 115 GB | 230 GB | 345 GB | 460 GB |
Database volume creation
BMC recommends that you use a basic disk storage type for all volumes. Also use the default disk alignment provided by Windows 2012 or later, and use the NTFS file system with a 64-KB allocation unit for SQL database and log partitions.
For tempdb , Microsoft recommends up to a 1:1 mapping between the number of files and logical CPUs. A more reasonable approach is to have a 1:1 mapping between files and logical CPUs up to eight files. If, for example, you have a server with 4 logical CPUs that manages 20,000 nodes, it creates 4 files of 500 MB for tempdb.