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.
Brad M McGehee 
Sure, I use tools from Idera and Red Gate, as well as the following for the instance in general –
To collect information for your SQL Server Infrastructure –
The first, as mentioned from Technet recently, is to execute the following parameter details on any SQL Server installation (I tested back to 2000), by run the following command.
exec xp_msver “ProductName”, “ProductVersion”, “Language”, “Platform”, “WindowsVersion”, “PhysicalMemory”, “ProcessorCount”
— result set is a table, with a row for each parameter
The second, and my preference as best pratice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):
select serverproperty(‘MachineName’) MachineName
,serverproperty(‘ServerName’) ServerInstanceName
,replace(cast(serverproperty(‘Edition’)as varchar),’Edition’,”) EditionInstalled
,serverproperty(‘productVersion’) ProductBuildLevel
,serverproperty(‘productLevel’) SPLevel
,serverproperty(‘Collation’) Collation_Type
,serverproperty(‘IsClustered’) [IsClustered?]
,convert(varchar,getdate(),102) QueryDate,
case
when exists (select * from msdb.dbo.backupset where name like ‘data protector%’) then ‘HPDPused’
else ‘NotOnDRP’ — where you would replace the
–data protector string with your third party backup solution
end
To run either of these queries across multiple servers in SSMS 2008 (assuming that you have more than one), under Registered Servers, right click on Local Server Groups, and select New Query.
http://www.sqlservercentral.com/blogs/hugo/2010/04/30/determine-system-and-server-information-on-sql-server-2008-from-management-studio/
Pingback: Something for the Weekend - SQL Server Links 08/06/12
How did you pick these counters? Do you have a link to the documentation for them?
SQL Server Counters
SQLServer:Access Methods Page Splits/sec:
SQLServer:Access Methods Workfiles Created/sec:
SQLServer:Access Methods Worktables Created/sec:
SQLServer:Buffer Manager Buffer Cache Hit Ratio:
SQLServer:Buffer Manager Free Pages:
SQLServer:Buffer Manager Lazy Writes/Sec:
SQLServer:Buffer Manager Page Life Expectancy:
SQLServer:Buffer Manager Page Lookups/Sec:
SQLServer:Buffer Manager Page Reads/Sec:
SQLServer:Buffer Manager Page Writes/Sec:
SQLServer:Latches Latch Waits/Sec:
SQLServer:Locks Lock Wait Time (ms):
SQLServer:Locks Lock Waits/Sec:
SQLServer:Locks Number of Deadlocks/Sec:
SQLServer:Memory Manager Target Server Memory(KB):
SQLServer:SQL Statistics Batch Requests/Sec:
SQLServer:SQL Statistics SQL Compilations/Sec:
SQLServer:SQL Statistics SQL Re-Compilations/Sec:
More documentation
Security credentials are stored in a KeePass (www.keepass.info) password safe:
For each instance
– service account username(s) & password(s)
– Password for sa and other SQL authentication accounts that are not user specific.
(eg those that applications use to connect to the database)
– In user application documentation we refer people to the people in the first instance for security information then keepass afterwards
– password is known by 3 people. In addition to this, it is written down in 2 places:
a) in a sealed envelope in a safe on site outside that the 3 people don’t have access to (and no single individual can access due to the way it is secured)
b) in a sealed envelope at an offsite disaster recovery/business continuity site
Robert, this list is a subset of the counters used by the PAL tool, for monitoring SQL Server performance. You can download this freeware at pal.codeplex.com.
Woo! What a long list. I don’t think I am able fill out within one week.
But it’s very very helpful!
Thanks!
Unfortunately we do it manually where I work, and guess what. All of our documentation is out of date. I’m new at my company, but plan on going down the automation route very soon. The only place I’ve ever seen manual documentation work is where it was a team of 1 , and the person was beyond meticulous. Get more than a couple people on the team and manual documentation is doomed to fail.
Powershell, TSQL, SSIS, pick your poison, but automation is the only way ot make sure your documentation is current. So many tools to pick from I’ll never understand why admins resort to manual documentation. We have computers. Use them.
Rule #1 of being a good dba/sa…Automate Everything!