SQL Server Checklists

SQL Server Check-lists
Basic check-lists at SQL Instance level and Database level, this is just to help DBAs to better understand the environment and document their Instance and databases level configurations.


SQL Instance Level Check-list:

Basics Points at Instance
Physical\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 Binary files & 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
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:
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:
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:
Shared Features Used
PowerPivot:
Master Data Services:
Multi-Server Administration & Data-Tier Application:
SSRS related reports (if any configured)

Database Level Check-list:

Points	Database_1 Name	Database_2 Name	Etc.
High Availability Class(1):
Owner:
Date Created:
Database State:
Recovery Model:
Compatibility Level:
Collation:
(1) High Availability Class Key:
Tier 1: >99.99% Uptime (1 Hour or Less of Downtime Acceptable Annually)
Tier 2: 99.9% to 99.99% Uptime (1 to 8.5 Hours of Downtime Acceptable Annually)
Tier 3: <99.9% Uptime (Hours to Days of Downtime Acceptable Annually)
Database File Information
Physical File Name:
MDF Location:
NDF Location(s) (add more rows as needed):
Filegroup(s) (add more rows as needed):
Includes Partitioned Tables/Indexes:
Part of a Federation:
Database Size:
Space Available:
Was Database Initially Pre-Sized:
Is Database Size Optimized for Future Growth:
Has Database File Layout Been Optimized:
Database Autogrowth Setting:
Log File Information
Physical File Name:
LDF Location:
Log Size:
Space Available:
Was Log Initially Pre-Sized:
Is Log Size Optimized for Future Growth:
Log Autogrowth Setting:
Number of Virtual Log Files:
Backup Information
Last Full Database Backup:
Last Database Log Backup:
Average Database Backup Time:
Database Backup Size:
Average Log Backup Size:
Number of Database Backup Copies Retained:
Backups Encrypted:
Backups Compressed:
Backup To Location:
Offsite Backup Location:
Backup Software/Agent Used:
List Applications Accessing Database
Application Name(s) (add more rows as needed):
Settings
ANSI NULL Default:
ANSI NULLS Enabled:
ANSI Paddings Enabled:
Arithmetic Abort Enabled:
Auto Close:
Auto Create Statistics:
Auto Shrink:
Auto Update Statistics Asynchronously:
Auto Update Statistics:
Close Cursor on Commit Enabled:
Concatenate Null Yields Null:
Cross-Database Ownership Chaining Enabled:
Data Correlation Optimization Enabled:
Database Read-Only:
Default Cursor:
Numeric Round-Abort:
Page Verify:
Parameterization:
Quoted Identifiers Enabled:
Recursive Triggers Enabled:
Restrict Access:
Service Broker Enabled:
Trustworthy:
VarDecimal Storage Format Enabled:
Full-Text Search
Is Full-Text Search Being Used:
Full-Text Catalog Name(s):
Full-Text Index Name(s):
Has Full-Text Search Configuration Been Documented:
Database Snapshots
Database Snapshots Used:
Existing Number of Snapshots:
Are Old Snapshots Being Dropped:
Total Sparse File Size:
Log Shipping
Log Shipping Used:
Instance Name(s) Where Log Shipped to:
Database Name(s) Where Log Shipped to:
Transaction Log Backup Schedule:
Has All Log Shipping Configuration Been Documented:
Has HA Documentation Been Created and Tested:
Database Mirroring
Database Mirroring Used:
Operating Mode:
Is Principal:
If Mirror, Instance Name of Principal Database:
Witness Instance Name:
Has Database Mirroring Compression Been Enabled (2008):
Has All Database Mirroring Configuration Been Documented:
Has HA Documentation Been Created and Tested:
Replication
Is Database Replicated:
Is Database a Publisher:
Is Database a Subscriber:
Is Database a Distribution Database:
Type of Replication:
Instance Name(s) Where Database is Subscribed:
Database Name(s) Where Database is Subscribed:
Instance Name of Distribution Server:
Is Replication Part of HA Strategy:
Has All Replication Configuration Been Documented:
Change Tracking
Change Tracking Enabled:
Retention Period:
Auto Cleanup Enabled:
Page/Row Compression
Row Compression Used:
Page Compression Used:
Compression Analyzed for Optimal Performance:
Filestream
Is Database Using Filestream:
Filestream Filegroup:
Filestream Data Physical Location:
Is SQL Server 2008 Transparent Data Encryption Used
Is TDS Enabled?
Is EKM Used?
Is Certificate and Private Key Backed Up and Protected: