SQL Server Install Checklist
Troubleshooting is essential for SQL Server issues – but focusing on a solid architecture and sql tuning large amounts of troubleshooting can be avoided.
Part of a solid SQL Server architecture is to install on an infrastructure supporting the workload using SQL Server test data generation testing tools and other workload analysis.
The infrastructure should also include redundancy. Redundancy levels should reflect the agreements between the DBA and customer. Redundancy is a tricky issue and there is a cost of having extra levels of redundancy. For example, server hardware redundancy requires extra servers.
I use this basic checklist to review an existing SQL Server installation or as a basis for a more detailed checklist.
1. Separate RAID Arrays for Data and Log files.SQL Server – RAID levels overview
2. Tempdb on dedicated array.SQL Server - TempDB performance and strategy checklist
3. Data, Log, and Tempdb drives formatted with 64K Allocation Unit Size.
4. Configure the Data drive with Drive letter E in Windows.
5. Configure the Log drive with Drive letter F in Windows.
6. Configure the TempDB drive with Drive letter G in Windows.
7. Exclude Data, Log, Tempdb, any Backup file paths, and the SQL Server Binaries folders from AntiVirus Scans Virus scan exclusions for SQL Server
8. Tempdb .Perform some workload analysis and configure data files in 4096MB increments for Datafiles, and 1024MB increments for Log files. Configure AutoGrowth to 1024MB for data files and 512MB for Log file.
9. Set Max Server Memory based on installed RAM and installation type. Be aware of multiple SQL Server Instances on the same server and set memory at the instance level accordinglyExample of a server with 1 SQL Server Instance max server memory and sql server memory – why is total memory greater than max memory?
8GB RAM = 6144 Max Server Memory
16GB RAM = 12228 Max Server Memory
32GB RAM = 28672 Max Server Memory
Measure memory usage of non SQL Server applications such as virus scanner, monitoring software and other systems management software
10.Instant File Initialization - SQL Server faster restores with instant file initialisation
11.Perform Volume Maintenance Tasks
12.Lock Pages In Memory - Powershell,PSEexec and lock pages in memory – made easy
13. Place Windows pagefile on separate drive - Windows Pagefile size for dedicated SQL Servers
No comments:
Post a Comment