I have been at my current place of employment for a few months now. I have yet to meet everyone on my floor, let alone in the building. I have yet to figure out who uses what systems, or even who has a stake in what SQL Server instances. And forget about knowing who is using what database(s). In fact, I never fully figured that out at my last job! I suspect (hope) this is often the norm for someone walking into an existing environment, especially when there is minimal, or no, documentation. Or if documentation exists, no one knows where to find it.

The good news is that for the most part it does not seem to matter. But what about when you are ramping up to do an upgrade? Or even something as "trivial" as applying a service pack? I don't know about you, but I want to inform the key stakeholders prior to doing anything of that magnitude. Heck, it's just simply a good idea to know who to go to and/or inform anytime there are issues/questions/whatever.

So the first problem is gathering the list of stakeholders. It may be as simple as taking a look at the logins for the instance. Perhaps there is that one developer who knows all the power users that would have your head should the server go down for 10 minutes. Or maybe you end up putting the list together through responses to mass emails! I don't know, but once you have the list what do you do?

Well, I've known people that love excel and will keep the list of contacts in a spreadsheet. I was one of those people. But what inevitably happens to me is I end up with a dozen spreadsheets, cryptically named, with overlapping info, in 10 different locations. Organization is not necessarily my strong suit. The point is, I needed something better.

Enter Extended Properties and Central Management Server. The CMS, besides being useful for multiple-instance queries and policy-based management, allows the entering of a "Description" for a registered server. The only way I know to do this is through SSMS: right-click on the registered server, select Properties, enter a description, and click Save. Then by querying the correct tables on your CMS server you can get that info back:

SELECT a.[name] AS 'Logical Name'
    , a.[server_name] AS 'Server Name'
    , b.[name] AS 'Group Name' 
    , a.[description] 
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers] a 
JOIN [msdb].[dbo].[sysmanagement_shared_server_groups] b 
    ON a.[server_group_id] = b.[server_group_id] 
ORDER BY b.[name] ASC, a.[server_name] ASC

Extended properties are even more flexible since you can create your own name-value pairs, on multiple levels of objects. Although there are a variety of possible uses for extended properties, we are focusing on adding some info at the database level to document stakeholders. The following code handles adding a couple extended properties to the current database:

EXEC sp_addextendedproperty @name = N'Description', @value = N'Test database'

EXEC sp_addextendedproperty @name = N'Stakeholders', @value = N'John Doe, Jane Doe'

And then we can retrieve the information either with fn_listextendedproperty, or by querying sys.extended_properties, which is shown here:

SELECT [name] 
    , [value] 
FROM sys.extended_properties 
WHERE [class] = 0   -- database

Combining these two features can provide inline documentation at the instance level, as well as the database (and lower) level. It eliminates the need for maintaining one or more pieces of external documentation. It's simple. And the best thing is, by implementing them, you are putting features in place that can be used for much more than this simple need in the future!