B2B: SQL Checklist


Working with a SQL Server Checklist

This is the 2nd 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s