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): | |||
| 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: | |||
| 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!
