Database Configuration Checklist

This is number four in a series of checklists that I am putting together for a new book I am writing. Previously, I blogged about my SQL Server Hardware Checklist, my SQL Server Operating System Checklist, and my SQL Server Instance Checklist. The goal of the SQL Server Database Configuration Checklist is to help DBAs document their databases, which in turn could reveal some potential changes that could be made in order to optimize their performance and availability.

Below is my current version of my Database Configuration Checklist, which is in the form of a spreadsheet that can be used to collect and store the database configuration information. The rows refer to various database configuration settings, and the columns can be used to list each of your databases by their logical names. I suggest that all the databases on a SQL Server instance be included in this checklist, including both system and user databases.

The Basics DB1 DB2 Etc.
High Availability Class(1):      
Date Created:      
Database State:      
Recovery Model:      
Compatibility Level:      
(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):      
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:      
Quoted Identifiers Enabled:      
Recursive Triggers Enabled:      
Restrict Access:      
Service Broker Enabled:      
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:      
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:      
SQL Server 2008 Change Tracking      
Change Tracking Enabled:      
Retention Period:      
Auto Cleanup Enabled:      
SQL Server 2008 Page/Row Compression      
Row Compression Used:      
Page Compression Used:      
Compression Analyzed for Optimal Performance:      
SQL Server 2008 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:      

I would like your feedback on my SQL Server Database Configuration 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 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!

5 thoughts on “Database Configuration Checklist

  1. Pingback: Tweets that mention Database Configuration Checklist | SQL Aloha --

  2. Brad,

    I would add a section on security–SQL/Domain accounts allowed in the DB and permission levels. Also if there are any Application roles and permissions for this database.

    I am not sure how to handle the Last Full/Differential/Log Backup rows. Mine are all scheduled up (SQL Jobs calling Stored Proc using Red-Gate) and fulls are taken weekly, differentials nightly and tlogs hourly (or every half hour/quarter hour depending on DR level).



  3. I also use in my documentation “Application information” section:
    – Application name
    – Application’s Vendor (contact)
    – Internal Application Support Contact
    – Application connectivity (uses DSN ODBC, Web Application etc.)

Comments are closed.