SQL Server Instance Checklist

Posted on by

This is my third in a series of checklists that I am putting together for a new book designed for DBAs to help audit their SQL Server instances. Previously I blogged about my SQL Server Hardware Checklist and my SQL Server Operating System Checklist. The goal of this checklist is to help DBAs document how their SQL Server instances are configured, which in turn may reveal some potential changes that could be made in order to better optimize their performance and availability.

Below is my current version of the SQL Server Instance Checklist, which is in the form of a spreadsheet that can be used to collect and store the information. As you review this checklist, you may notice that I have excluded some instance-related information, such as that relating to databases, security, database maintenance, SQL Server agent jobs, and high availability. Because each of these topics are large, I will devote separate checklists for each of these topics. In essence, this checklist covers generic instance configuration settings.

The Basics        
Real or Virtual Machine:        
Default or Named Instance:        
Single or Multiple Instances:        
Instance Name:        
Port Number:        
SQL Server Version:        
SQL Server Edition:        
32-Bit or 64-Bit:        
Service Pack Level:        
Version (Build) Number:        
Server Language:        
Server Collation:        
Latest Updates Applied:        
List Hotfixes Added:        
License Type:        
Is Properly Licensed?        
Product Key:        
         
Aliases        
Server Aliases & Settings:        
         
Network Protocols        
Shared Memory:        
Named Pipes:        
TCP/IP:        
VIA:        
Protocol Encryption Enabled:        
         
Clustering        
Is This Instance Part of a Windows Cluster:        
Virtual Server Name:        
Virtual Server IP Address:        
         
Type of Workload        
OLTP:        
OLAP:        
Combination:        
         
File Locations        
SQL Server Executables & Related Files:        
Location of System Databases:        
Production MDF/NDF Files:        
Production LDF Files:        
Tempdb:        
BAK/TRN Files:        
         
Tempdb Configuration        
Is Tempdb Pre-Sized to Optimal Size:        
Current Size of Tempdb:        
Is Tempdb Located on Its Own Array:        
Is Tempdb Divided Into Multiple Files:        
If Multiple Files, How Many Physical Files:        
If Multiple Files, Does Each File Have Identical Size:        
         
Services Installed/Running On This Instance        
SQL Server Database Services (MSSQLSERVER):        
SQL Server Agent:        
Integration Services:        
Analysis Services:        
Reporting Services:        
Full-Text Search:        
SQL Server Activity Directory Helper:        
SQL Server Browser:        
SQL Server VSS Writer:        
         
OS-Related Settings        
Is Instant File Initialization On:        
Is the "Lock Pages in Memory" Setting On (64-bit only):        
         
32-Bit Memory Configuration (If 32-Bit Memory)        
How Much 32-Bit Memory is Available to the Instance:        
Does Boot.ini File Have the /3GB Switch:        
Does Boot.ini File Have the /PAE Switch:        
Is the "awe enabled" Server Setting On:        
The "max server memory" Server Setting Is:        
         
SP_Configure Settings Minimum Value Maximum Value Default Current Setting
access check cache bucket count (2) (3) 0 16384 0  
access check cache quota (2) (3) 0 2147483647 0  
ad hoc distributed queries (1) (2) 0 1 0  
affinity I/O mask (1) (2) (3) -2147483648 2147483647 0  
affinity64 I/O mask (1) (2) (4) -2147483648 2147483647 0  
affinity mask (1) (2) (3) -2147483648 2147483647 0  
affinity64 mask (1) (2) (3) (4) -2147483648 2147483647 0  
Agent XPs (1) (2) (3) 0 1 1  
allow updates (5) 0 1 0  
awe enabled (1) (2) (3) 0 1 0  
backup compression default (2) (3) 0 1 0  
blocked process threshold (1) (2) (3) 0 86400 0  
c2 audit mode (1) (2) (3) 0 1 0  
clr enabled (1) (2) (3) 0 1 0  
common criteria compliance enabled (1) (2) (3) 0 1 0  
cost threshold for parallelism (1) (2) (3) 0 32767 5  
cross db ownership chaining (1) (2) (3) 0 1 0  
cursor threshold (1) (2) (3) -1 2147483647 -1  
Database Mail XPs (1) (2) (3) 0 1 0  
default full-text language (1) (2) (3) 0 2147483647 1033  
default language (1) (2) (3) 0 9999 0  
default trace enabled (1) (2) (3) 0 1 1  
disallow results from triggers (1) (2) (3) 0 1 0  
EKM provider enabled (2) (3) 0 1 0  
filestream_access_level (2) (3) 0 2 0  
fill factor (1) (2) (3) 0 100 0  
ft crawl bandwidth (max): (1) (2) (3) 0 32767 100  
ft crawl bandwidth (min): (1) (2) (3) 0 32767 0  
ft notify bandwidth (max): (1) (2) (3) 0 32767 100  
ft notify bandwidth (min): (1) (2) (3) 0 32767 0  
index create memory (1) (2) (3) 704 2147483647 0  
in-doubt xact resolution (1) (2) (3) 0 2 0  
lightweight pooling (1) (2) (3)

0 1 0  
locks: (1) (2) (3) 5000 2147483647 0  
max degree of parallelism (1) (2) (3) 0 64 0  
max full-text crawl range (1) (2) (3) 0 256 4  
max server memory (1) (2) (3) 16 2147483647 2147483647  
max text repl size (1) (2) (3) 0 2147483647 65536  
max worker threads (1) (2) (3) 128 32767 0  
media retention (1) (2) (3) 0 365 0  
min memory per query (1) (2) (3) 512 2147483647 1024  
min server memory (1) (2) (3) 0 2147483647 0  
nested triggers (1) (2) (3) 0 1 1  
network packet size (1) (2) (3) 512 32767 4096  
Ole Automation Procedures (1) (2) (3) 0 1 0  
open objects (5) 0 2147483647 0  
optimize for ad hoc workloads (2) (3) 0 1 0  
PH_timeout (1) (2) (3) 1 3600 60  
precompute rank (1) (2) (3) 0 1 0  
priority boost (1) (2) (3) 0 1 0  
query governor cost limit (1) (2) (3) 0 2147483647 0  
query wait (1) (2) (3) -1 2147483647 -1  
recovery interval (1) (2) (3) 0 32767 0  
remote access (1) (2) (3) 0 1 1  
remote admin connections (1) (2) (3) 0 1 0  
remote login timeout (1) (2) (3) 0 2147483647 20  
remote proc trans (1) (2) (3) 0 1 0  
remote query timeout (1) (2) (3) 0 2147483647 600  
Replication XPs Option (1) (2) (3) 0 1 0  
scan for startup procs (1) (2) (3) 0 1 0  
server trigger recursion (1) (2) (3) 0 1 1  
set working set size (5) 0 1 0  
show advanced options (1) (2) (3) 0 1 0  
SMO and DMO XPs (1) (2) (3) 0 1 1  
SQL Mail XPs (1) (2) (3) 0 1 0  
transform noise words (1) (2) (3) 0 1 0  
two digit year cutoff (1) (2) (3) 1753 9999 2049  
user connections: (1) (2) (3) 0 32767 0  
User Instance Timeout (6) 5 65535 60  
user instances enabled (6) 0 1 0  
user options (1) (2) (3) 0 32767 0  
Web Assistant Procedures (1) 0 1 0  
xp_cmdshell (1) (2) (3) 0 1 0  
         
Key for Above        
(1) 2005        
(2) 2008        
(3) 2008 R2        
(4) 64-bit only        
(5) deprecated        
(6) SQL Server 2008 Express only        
         
Linked Servers        
Is This Instance Linked to Other Instances:        
Described How Instances are Linked:        
         
Instance Endpoints        
Database Mirroring:        
Service Broker:        
SOAP:        
TSQL:        
         
Replication        
Is Replication Used:        
Type of Replication Used:        
Replication Role(s):        
Names of Instances Involved in Replication:        
         
SQL Server 2008 Policy-Based Management        
Is Policy-Based Management Being Used:        
Is This Instance Used to Manage Policies:        
List All Instances Managed by This Instance:        
List Policies:        
         
SQL Server 2008 Resource Governor        
Is the Resource Governor Being Used:        
List the Workload Groups:        
List the Resource Pools & Their Settings:        
Has the Classification Function Been Fully Tested:        
Has the Resource Governor Been Evaluated for Effectiveness:        
         
SQL Server 2008 Data Collector
        
Is the Data Collector Used:        
Does This Instance Have the MDW:        
Is the Instance With the MDW Dedicated:        
Location of the MDW:        
List Instances Monitored by the Data Collector:        
Has the Data Collector Overhead Been Reviewed:        
Has the Data Collector Retention Policy Been Reviewed:        
         
SQL Server 2008 R2 Features Used        
PowerPivot:        
Master Data Services:        
Multi-Server Administration & Data-Tier Application:        

I would like your feedback on my SQL Server Instance Checklist. For example, I would like your input on:

–What is missing from the list? Keep in mind that I will be creating additional lists to cover more specific topics, and that I can’t include every possible instance variation.

–What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.

–Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate?

Please add your comments below, and as I get feedback, I will update the checklist. Thanks!

7 thoughts on “SQL Server Instance Checklist”

  1. Pingback: Tweets that mention SQL Server Instance Checklist | SQL Aloha -- Topsy.com

  2. the only things I would add to it would be a section to list any 3rd party tools that might be installed, like backup agents, monitoring agents, etc and a place to list the dbmail profiles that are setup

  4. Pingback: Something for the Weekend – SQL Server Links this Week 02/07/10 | John Sansom - SQL Server DBA in the UK

  5. Hi Brad,

    I would add initial Alerts with notifications. I have set of alerts that I always create on each new SQL Server instance. Examples: Severity 19 to 25 errors, Severity 14 (permissions) errors, Deadlocks, Disk Space Alerts (if there are no other monitoring tools used).

  6. Svetlana, thanks for your feedback. I will be creating a separate checklist on monitoring which will include alerts, as you have suggested.

  7. What about adding to the Linked Servers section “Proveders Installed” and “Providers Enabled”.

Comments are closed.