Creative Writing Week: Documentation

First off, welcome back class! So this week we’re talking about writing. As a DBA or a developer you are going to be asked at some point to perform a necessary evil called documentation. Why is this evil? Well, it’s not really evil but it is not one of the more glamorous parts of the job, yet it is one of the most essential. Good documentation is everything! Without documentation you can spend all the time in the world developing the greatest system on earth, utilizing some of the most complex and beautiful code ever written but when something eventually (and it will) break, you’re going to be expected to be the one to fix it.

“But I’m working on this really important production issue, I can’t stop everything just to troubleshoot a system I put into production 3 years ago and can’t remember half the things I put into it!” Ah, but if you had only documented it someone else could be supporting the system and you could be blissfully working on the here and now. When we talk about documentation, however, that word means different things to different people. Let’s go over some of the different aspects of documentation within a system and why they are important.

Code Documentation

In the database world you have lots of objects in your databases such as tables, views, stored procedures, triggers, etc. Some objects can be fairly easy to deduce what they do, for example, a stored procedure named usp_DisplayAllInventory. From the name alone you can probably tell that executing it will return a listing of all the inventory in your system. But is that necessarily correct? Not really. That stored procedure could accept a parameter that tells it a specific department and displays all of the inventory for that given department. But how can truly know what it does? Glad you asked!

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author:        Jorge Segarra

— Create date: 4/7/10

— Description:    Example Stored Procedure that pulls back all inventory items

— =============================================

CREATE PROCEDURE usp_DisplayAllInventory

— Add the parameters for the stored procedure here

@departmentid INT

AS

BEGIN

/*SET NOCOUNT ON added to prevent extra result sets from

interfering with SELECT statements. */

SET NOCOUNT ON;

— Insert statements for procedure here

SELECT ProdID,ProdName,ProdPrice,InventoryQty FROM dbo.Products WHERE ProdDept = @departmentid

END

GO

In the above stored procedure, you’ll see a few lines of code that aren’t truly code. In T-SQL you can leave comments within your code one of two ways:

1: You can precede a line with two hyphens (–). This will cause that entire line of code to be commented out. This allows you leave quick explanatory notes about a particular piece of code. In the above example you can see I used this method to document who wrote the stored procedure, its creation date, and a brief description of what the stored procedure’s overall function is. I’ve also used the line comment code method after the CREATE PROCEDURE command to explain what the next line of code is doing or why its important to the overall process.

2: If you plan on leaving a longer comment that explains something, and you don’t want to have to start each line with the double-hyphens, you can comment out an entire block of text by adding /* to the beginning of the text and closing the comment block with */. You can see an example of this after the BEGIN statement. I’ve used this method to write out a longer explanation of why I’ve chosen to add the SET NOCOUNT ON to the stored procedure.

It’s always good practice to comment your code because even though you may be intimately familiar with the  inner workings of a particular piece of code, truth is a few years down the line you may not be around. Someone else will be looking at that code and need to know what is going on in there. By documenting your code as you go you not only make it easier for yourself to figure out what you’re looking at (since it may be quite awhile before you look at that piece of code again) but you make it easier for others to figure out what your though process was as well.

System Documentation

Oh to only have to deal with SQL, wouldn’t that be wonderful? Well truth of the matter of is that many of us also double as system administrators. As such we often called (and rightfully expected to) be able to put together a run-book of the systems you manage. A run-book is basically a document you put together that covers important details about your system such as IP addresses, support contact info, operational information, backup windows, etc. Below I’ve linked to a sample run book you can use as a template in your shop, if you don’t have one already. This document should be a “living” document in that it should periodically and regularly reviewed and updated to make sure you have all of the latest information available in case of emergency. Nothing is worse for a company to discover during a disaster that the one guy who knew what was on the server or how to fix it, was let go 6 months ago and nobody else knows anything about the system. Let’s use another, less drastic, example. Imagine you’re on your first vacation in over 3 years with your family. You’re at the airport getting ready to  board when you get a frantic call that a critical system went down. Now from listening to the call you can tell you’ll be on the phone awhile trying to explain or walk-through someone how to fix said issue (which would probably be a 5-minute fix for you if you were there but the operator calling knows nothing about the system). In the meantime your spouse is giving you the stink-eye because you promised to focus on vacation and not your work and your day just quickly goes downhill. Wouldn’t it be nicer if that person that called you had a step-by-step manual detailing important information on exactly how to fix common issues? This way they could refer to the document first, apply any troubleshooting tips/steps you have supplied and save you a headache (heck, they might even learn something themselves).

Due to the high importance of this kind of documentation, make sure you place it somewhere protected (read also: being actively backed up) yet accessible to others within your group (or whomever needs access to it). It does nobody any good if you spend 100 hours documenting all of your systems and all those documents only exist on your hard drive. If your drive goes (as most eventually do), then you’ll have lost all of that hard work and vital information. No fun. Instead I recommend placing these files out on either a shared network drive or SharePoint site. SharePoint sites are great for this kind of thing since you can ratchet down permissions based on groups/users, gets backed up regularly (at least it should be if you don’t want to be sorry), and provides an easy platform for collaborating on and editing the document on a regular basis. You can even setup alerts on a particular list/folder to alert you if an item gets added or changed. I’d also recommend keeping copy of these documents on a portable drive that should be part of your takeaway kit. To read more about takeaway kit (or bug-out bag) and how it relates to emergencies, check out Brent Ozar’s article on the topic.

Lastly, having this kind of documentation on-hand makes you look great to management. If your boss (or anyone really) ever has any basic questions regarding the systems in your care you can refer to them your document store and they can check it out themselves. When your boss sees how organized you are and how in-control you are of the environment I’m sure that certainly won’t hurt come review time!

That’s all for today’s lesson. For Wednesday’s lesson we’ll be covering blogging and how starting one can help you in your documentation efforts.

• Download sample Run-Book template (Word Macro-Enabled Template & Regular Doc for 2003 compatibility)
  • NOTE: This is only a sample template. Feel free to modify it to fit your needs.

Continue to part II