Some parts of our jobs are not glamorous, but necessary. For example, I have to brush Brent’s Bob Dylan wig weekly, to make sure it’s shiny and perfect. Documentation is a task many people roll their eyes at, procrastinate about starting, have a hard time keeping up-to-date, and in general avoid.

Stop avoiding it, and embrace the benefits!

The most important part of documentation is starting, so I’d like to help you by giving you a list of things to document. It’s going to take time and won’t be as fun as tuning queries from 20 minutes to 2 seconds, but it could save the day sometime in the future.

You can call this your SQL Server Run Book, your SQL Server Documentation, your SQL Server Best Practices Guide – whatever works for your environment. Make sure it’s filled in for each server, and kept up to date, and you’ll soon realize the benefits.

Click here to download a template SQL Server Documentation Run Book.

Need an example? Here is a sample SQL Server Documentation Run Book!

Server-Level Settings

You’ll want basic information about your instance recorded so that if you have to rebuild it, the environment will be the same.

• Number and type of CPUs
• Amount of memory
• Storage – type and amount
• SAN settings (if applicable)
• Server name
• Instance name
• SQL Server version and edition
• Service account
• Min and max memory settings
• Collation
• Trace flags
• Any non-default options that have been configured, such as MAXDOP or Optimize for ad hoc workloads.
• Clustering
  • Virtual server name
  • Virtual IP address

Databases

Each database on your server – including system databases – should be well-documented. Beyond knowing what is contained in the database, you should also know why it exists and whom to contact with questions.

• Name
• Purpose
• What application or program it is for
• What person, business unit, or department is the owner
• Are two or more databases on the server related? Do they need to be restored on the same server, or in a specific order?
• Location of data and log files
• Recovery model
• Backup schedule and file locations
• Collation
• Information about tables, views, stored procedures, and functions, such as table names, field names, data types, default values, or parameters
• Database roles
• Database users and permissions
• Is log shipping enabled?
  • What instance are you restoring to or from?
  • Frequency of backups
• Is mirroring enabled?
  • Operating mode
  • Principal or mirror?
  • Instance that is the principal or mirror
• Is replication enabled?
  • Type of replication
  • Publisher, subscriber, or distribution database
  • Distribution server name

 Security

Having a secure SQL Server is highly important. Only the users that need access to the server should be granted access, and they should only be granted the minimum rights needed.

• Authentication mode
• Logins – Windows and SQL authentication
  • What roles a login is granted
  • Databases the login has permissions to and the roles in those databases
• Server roles
• Credentials
• Certificates
• Linked Servers
  • Name
  • Type
  • Login/Security Context

Maintenance Plans/Agent Jobs

There are many important tasks that can be set up and scheduled on your database server, such as backups, restores, and maintenance. Document these, as well, so you know what each job is for, and can recreate it in the event of a disaster.

• Name
• Purpose
• Owner
• Tasks performed
• Schedule
• Are any related, and do they need to be run in a specific order?

Anything that goes against best practices, and why

Do you have xp_cmdshell enabled because a third-party application requires it? Are all of your linked servers configured with an sa account? Any special circumstances should be documented and explained. In the case of an emergency, you don’t want to have to think about why these things were turned on. You also want this information readily available for anyone else who is or will be responsible for the instance.

Documentation is Worth the Effort

You will thank yourself for doing this work. Don’t let the task overwhelm you – start with one server, and work your way through your environment. Once you have to rebuild a server, train a new DBA, or explain what a specific Agent job does, you will appreciate the time you have put in to building your run book!