The Importance of SQL Documentation
The other day, I was perusing around the SQLPass website and came across something interesting: #EntryLevelBlogChallenge. Which of course is a personal challenge to technical bloggers by Tim Ford (b|t).
The concept is simple, write one entry-level blog post a month over the next year and tag it on twitter with #iwanttoshare
Well, CHALLENGE ACCEPTED!
My series of blogs posts will be tagged/filed under the heading of “Back to Basics (B2B)”
My first blog post in this series, entitled “B2B: Documentation” will be out next week!
Today’s post will discuss the importance of documentation and the best way I know of getting that information. Next week, I will show you how to automate the collection of all this data.
One of the most important tasks you can do as an entry level Database Administrator (DBA), is to write down what you do, how you do it and what you did it to. Documentation does at least two important things: 1) provides a history and 2) most people remember things if they write them down.
Why is knowing the history of your server important?
I’m glad you asked! Data by its very nature is always evolving and growing which produces trends. These trends if un-checked or undocumented will present problem in the future, I promise. As data grows, hard drive space is consumed. As data evolves complex queries are developed to pull data and resources are consumed. A DBA must be able to monitor these aspects of SQL Server in order to provide optimal performance.
The first step in documentation is to determine what you have and what you are working with. One of the most effective ways of doing this is using Glenn Berry’s (b|t) SQL Diagnostic Information Queries (Jan 2016 version) to obtain all the server/hardware information you will need to document your servers. Glenn has developed his Dynamic Management View (DMV) queries based on SQL Server Version 2005, 2008, 2008 R2, 2012, 2014 and 2016. Each new version provided different DMV functionality and Glenn has kept up with the times.
The queries are very extensive and provide a wealth of information. At last count there were 70 queries included in the SQL Server 2016 January 2016 version. That is a boat load of information.
The queries are numbered and designed to be executed one at a time. Once you have started SQL Server Management Studio (SSMS) you would open the file in SSMS by either clicking the OPEN folder or selecting FILE >> OPEN >> Open File or simply pressing CTRL + O.
If you have selected the correct version of the Information query then you should be seeing this message from SQL Server.
From this point, you highlight and execute the next query to see the results.
With each result you can go to the FILE >> Save Results As…>>> command and actually save the results to a Comma Delimited (.csv) for viewing later. As effective as this is; this is a very tedious method for collecting the results for all 70 queries.
Next post, I will show you a method of collecting the results for all 70 queries into one single Excel spreadsheet!