Many people have published SQL Server installation checklists before. Here is one I have been using for several installations without issue, six for SQL Server 2012 and three for SQL Server 2008 R2. This list has evolved over the years, but primarily comes from many sources, including my own experience as well as Jonathan Kehayias, Glenn Berry, and Paul Randal.

This list is just a base configuration and is by no means complete. It is intended to be a starting point, not just to standardize your SQL Server installations, but also just to make sure you do not miss anything.

Pre-SQL Installation

1.     Service Account

a.     Procure all AD Service accounts. Preferably one for each SQL Server service.

b.    Register an SPN. DBAs often do not have permission to do this. Have this done by a domain admin now so it is ready to go after the installation:

                                          i.    Add the readServicePrincipalName and writeServicePrincipalName permissions to the SQL Server service account in AD.

                                         ii.    Run the following:
setspn -S MSSQLSvc/ServerName SQLServiceAccountName
setspn -S MSSQLSvc/ServerName:1433 SQLServiceAccountName
setspn -S MSSQLSvc/ServerName.root.DomainName:1433 SQLServiceAccountName

2.     Server Configuration

a.     Install the latest applicable Windows Updates. Ensure there are no pending reboots before continuing. Run Disk Defragmenter afterwards.

b.    Add the SQL Server service account to the following Local Security Policy.

                                          i.    Replace a process level token

                                         ii.    Lock Pages in Memory (LPIM).  May not be necessary in Windows 2008 or newer. Do it anyway.

                                        iii.    Perform Volume Maintenance Task

c.     Set the queue depth to an appropriate level. Preferably 128 or higher.

d.    Set any virus scan software to not scan any MDF, NDF, LDF, BAK or TRN files.

e.     Set all NICs, physical or VM, to full duplex.

f.     Set BIOS or VM power management to OS managed, then set to full power.

g.    Format data and log drives with an allocation unit size of 64k.
Verify using the fsutil utility: fsutil fsinfo ntfsinfo [drive letter]:

h.     Set the pagefile to 150% of the total RAM. If this is too large, then set it to 100% + 1 MB.

i.      Create local “SQL Server Admins” group, add applicable users.

j.      Add applicable users to local Administrators and RDP groups.

3.     Drives and Directories - Create the following directory structure for a single SQL instance. Also consider directory structure requirements for other services, such Analysis Services.

a.     OS – C:

b.    SQL  Installation – D:

c.     Data drive(s) – E:\SQLData, add more as needed.

d.    Log drive – L:\SQLLogs

e.     Backup drive – N:\Backups

f.     TempDb drive – T:\TempDb

4.     Service Account Permissions

a.     Remove “Everyone” from non-C drives.

b.    Grant the following permission to the SQL Server and SQL Agent service accounts

                                          i.    SQL Server

1.     D: - Full control

2.     E: - List folder contents

3.     L: - List folder contents

4.     T: - List folder contents

5.     E:\SQLData - Full control

6.     L:\SQLLogs - Full control

7.     T:\TempDb - Full control

                                         ii.    SQL Server Agent

1.     D: - Full control

2.     N: - List folder contents

3.     N:\SQLBackups - Full control

SQL Installation

1.     Features - Install the features needed for the instance. Do not install something “just in case.”

2.     Shared feature directory: D:\Program Files\Microsoft SQL Server\

3.     Shared feather directory (x86): D:\Program Files (x86)\Microsoft SQL Server\

4.     Instance root: D:\Program Files\Microsoft SQL Server\

5.     Service Accounts: add applicable AD service accounts with Automatic Startup

6.     SQL Server Bowser: Disabled if installing only one instance.

7.     Authentication Mode: Mixed, or as appropriate.

8.     Add “SQL Server Admins” local group to SQL Server Administrators

9.     Data Directories

a.     Root: E:\SQLData

b.    User database: E:\SQLData\

c.     User database logs: L:\SQLLogs

d.    Tempdb data and logs: T:\TempDb

e.     Backup: N:\Backups

Post-SQL Installation

1.     Install any SQL Server or Visual Studio service packs and updates.

2.     Check that Lock Pages in Memory is being used. Restart SQL service if not done after updates.

3.     Confirm the SPN was registered:

a.     Restart SQL service if not done after updates.

b.    The query below should return “KERBEROS” if registration was successful. Run from a remote connection:
SELECT auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@spid ;.

4.     Added applicable users to sysadmin server role.

5.     Set Model database settings. Adjust the sizes as appropriate. Here is a good start:

a.     SIMPLE recovery

b.    Data file: 2048 MB Initial Size, 1024 MB autogrowth, unlimted size

c.     Log file: 1024 MB Initial Size, 512 MB autogrowth, unlimited size

d.    Move .mdf and .ldf if necessary.

6.     Set master and msdb database settings. Adjust the sizes as appropriate. Here is a good start:

a.     SIMPLE recovery

b.    Data file: 1024 MB autogrowth, unlimted size

c.     Log file: 512 MB autogrowth

7.     Set tempdb database settings. Use Glenn Berry’s suggested settings for a starting point.

a.     SIMPLE recovery

b.    Add additional data files as appropriate:
Logical name = tempdev2, File name = tempdb2.ndf.

c.     All tempdb data files: 4096 MB Initial Size, 1024 MB autogrowth, unlimted size

d.    Log file: 1024 MB Initial Size, 512 MB autogrowth, unlimited size

8.     Set trace flag -T1118. This helps elevate contention in tempdb. There is some debate on this, but it looks like it does not hurt to have it on. Paul Randal’s blog goes into detail.

9.     Enable the DAC. (sp_configure 'remote admin connections', 1)

10.  Set Optimize for Ad hoc Workloads to True

11.  Set Min and MAX Server Memory to an appropriate level. Use Glenn Berry’s suggested settings for a starting point.

12.  Set Cost Threshold for parallelism as appropriate.

13.  Set Max Degree Parallelism as appropriate.

14.  Setup database mail.

15.  Create Administrator Operators.

16.  Enable Alert System in SQL Agent using the profile just created.

a.     Enable fail-safe operator for notification types.

17.  Configure SQL Error Log retention for 31 log files, or whatever is appropriate.

18.  Create job to cycle error logs: sp_cycle_agent_errorlog, sp_cycle_errorlog.

19.  Configure alerts for severity 16 through 25 as well as specific alerts for 823, 824 and 825 errors.

20.  Confirm SQL Server service account is NOT in the local Administrators group.

21.  Install Brent Ozar’s sp_blitz. Address issues.

22.  Install and MS Baseline Config Analyzer and SQL Server 2012 BPA on your workstation, run. Address issues.

23.  Install Ola Hallengren’s Maintenance Solution

a.     Setup backups

b.    Setup integrity checks

c.     Setup index maintenance

d.    Setup history maintenance