When I was a novice DBA, I spent little time documenting my SQL Servers. But as I became more experienced, I realized the importance of having complete, up-to-date documentation for all of the SQL Servers I manage.
Some of the benefits of documentation include: 1) It is easier and quicker to find information when needed, especially if it is located in a central location; 2) Not having to reinvent the wheel every time I need to resolve a problem that I had solved in the past, but now have forgotten; 3) Being kept aware of what other DBAs are doing to the instance; 4) Helping to establish “standards” that can be used throughout the organization; 5) Additional information to help when troubleshooting problems; 6) The ability to duplicate an existing server, such as when you need to move an instance from one box to another; 7) For disaster recovery when you need to rebuild a new box to replace one that has died and can’t be rebuilt; among many other reasons.
Of course, the problem with creating and maintaining current documentation is that it is time-consuming, and boring. In a recent poll, I asked visitors to this website about how they document their SQL Server instances.
As you can see, just over half of those DBAs who document their servers claim they document their servers manually, not using any automated tools. Of course the poll can’t tell me what data they are collecting, or how are they storing it, but it speaks to the point that documentation, for the most part, is a manual process.
About 16% say they use some sort of automated tool to help document their instances. This is a good step towards making it easier to document instances, but the poll can’t tell us what tools they use, such as home-grown tools, or third-party documentation tools, or what type of data do they collect.
About 17% said that they would like to document their instances, but that they don’t have time. I think these were the most honest answers given, and in the real world, I think this number would be higher, at least based on my experience as a DBA.
About 15% said “What’s documentation?” I am assuming that this response was supposed to be a joke (or at least I hope so), and that really this number should also fall into the group of people who say they don’t have time to document their servers, bringing that to about 32%.
Another issue that is rarely talked about when documenting instances is what should be documented. I have been working on this problem for some time, and I have come up with a spreadsheet that can be used to help document an instance. You can download it here. I am constantly working on this spreadsheet to improve it, and perhaps you can use it as a basis for documenting your own instances. Besides the types of data I collect for each instance with the spreadsheet, I also keep a separate log for each instance in a Word document, describing any changes I made and why, documenting problems that occurred and how I solved them, and much more. Between my spreadsheet and Word document, I have comprehensive documentation for my servers that I find invaluable.
If you have any valuable insight on how you think SQL Servers should best be documented, please share them below. Also, if you have any feedback on by documentation spreadsheet, please let me know.