Working with a SQL Server Checklist

This is the 2^nd post in my “Back 2 Basics” series following the introduction of topics.

Part of a DBAs role is to ensure SQL Server is installed and configured properly for optimal performance. Just about every DBA that has any experience validates their SQL installations with a checklist.  For years, my “go-to” checklist was Brent Ozar’s SQL Server Installation Checklist.  It has been a few years since it has been updated; but it still is the standard.  Based off his and many other I wrote my own check list of things to do on installation of a SQL Server.

These are the important things you need to remember when it comes to an installation checklist or a checklist for optimal performance.

For me, if I am given a new SQL instance to manage or installing a new SQL instance these are my personal checklist items:

1. Server Level Configurations
   1. Enable DAC
   1. Configure TempDB
   1. Max Degree of Parallelism
   1. Cost Threshold for Parallelism
   1. SQL Server MAX Memory
      1. Memory Management
   1. Ad Hoc Workloads
2. Database Configurations
   1. Individual Volumes for Data (mdf) and Transaction Log (ldf) files
   1. Auto Growth Settings
   1. Auto-Shrink
   1. Auto-Close
   1. Auto-Update Stats
   1. Compatibility Level
3. Setup Maintenance – I recommend Ola Hallengren Scripts for this.  Ola’s scripts are world famous script used to manage all your databases.  On smaller setups, you can install and configure with a single script and never have to worry again.  However, I strongly recommend you learn the ins & outs of his scripts to customize them for your needs.  You can even extend its functionality with Configuration Tables.
   1. Backups
   1. Integrity Check
   1. Index
4. SQL Agent Alerting
   1. Database Mail
   1. Configure Operator
   1. SQL Agent Failsafe
   1. SQL Agent Alerts