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

Read More on SQL Server Installation

How to Install SQL Server service pack silently - SQL Server DBA

SQL server 2008 R2 – Silent installation for the SSMS and client tools

TPC-H generate test data , test queries and sql database benchmark ...