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!
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
- Trace flags
- Any non-default options that have been configured, such as MAXDOP or Optimize for ad hoc workloads.
- Virtual server name
- Virtual IP address
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.
- 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
- 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
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
- Linked Servers
- 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.
- Tasks performed
- 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!
A nice little utility I use:
Works great to get a baseline established at least.
I use powershell scripts to run once or so a month in my large multi-national environment. Scripts are blogged about on my site 🙂
I absolutely agree that there is a minimum level of documentation that is needed. Just make sure that if you store your Run Books in something like SharePoint, you have another copy somewhere else, just incase the emergency you’re faced with is your SharePoint DBs being down!
Excellent! Now if the word that robust documentation for semantics ever gets out…, 😉
Thanks for a great post!
Excellent post. The list looks very familiar. Over the years I learned the hard way to write down at the beginning of a project all of the gnarly details. Otherwise, you do forget. I now find it much easier make notes as I go in some kind of documentation. Now I have convenient lists of who, what, where when and why that also include scripts, stored procedures and all of the goodies that would be so difficult to duplicate.
I’m with you, Jes.
These don’t seem to be loading? I may have an issue from being at work.
Check that… IE settings at work preventing. It loads in Firefox.
I have downloaded the XLSX files but when I open it, Excel said it’s corrupted.
Got to love documentation! 🙂 Thanks for the article!
you can download http://download.microsoft.com/download/4/6/F/46F45C42-D679-404E-9812-6053DD59A0D2/Microsoft_Assessment_and_Planning_Toolkit_Setup.exe and configure map for document your sql environment too, as a free option
I realize I’m responding to a quite old post, but I noticed in the sample book that you have a worksheet for databases that include table and field names, including their datatypes. Is this wise to have such granular detail? It is possible that you could have scores of tables, each with dozens of fields. I wholeheartedly agree that documentation is well-worth maintaining, but to the table field name level? Isn’t that a bit overkill?
Depends on the source. For example, in regulated data warehouses, it’s often even more granular than that – talking about where each field’s data source is, what tools populate it, how it was transformed, etc.