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
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 drives with an allocation unit size of 64k, and 8192 bytes for log drives.
Verify using the fsutil utility: fsutil fsinfo ntfsinfo [drive letter]:
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 feature 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
SQL Logs 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 ;.
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 to desired data and log folders 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
a. SIMPLE
recovery
b. Add additional
data files as appropriate:
Logical name = tempdev2, File name = tempdb2.ndf.
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.
PaulRandal’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. Install Brent Ozar’s sp_blitz. Address issues.
18. Install Brent Ozar’s sp_blitz. Address issues.
19.
Create job to cycle error logs: sp_cycle_agent_errorlog,
sp_cycle_errorlog.
20.
Configure alerts for severity 16 through 25 as well as specific
alerts for 823, 824 and 825 errors.
21.
Confirm SQL Server service account is NOT in the local
Administrators group.
22.
Install and MS Baseline Config Analyzer and SQL Server 2012 BPA on
your workstation, run. Address issues.
a. Setup
backups
b. Setup
integrity checks
c. Setup
index maintenance
d. Setup
history maintenance