Querying Central Management Servers

SQL Server Central Management Servers have a number of great features such as, policy enforcement, spawning group connection query windows, and organizing your list of servers.

The server list is what I wanted to talk about today. I find this a simple, yet extremely useful feature because it can be difficult to maintain a comprehensive server list when you have multiple DBAs with the need to know what servers exist and no longer exist at any given time. The Central Management Server services this purpose well because changes to the list will be readable on all authorized DBA’s SSMS Registered Servers window (just a quick refresh is needed for DBAs who keep their SSMS open at all times).

Now that we have a central list, we begin to populate it and for some of this this list can get very large. So large that folders are created to organize and categorize but at some point this list can become difficult to navigate or find what you are looking for.

At one of my jobs I setup a simple hierarchy for databases:

  • Level 1: Environment (Dev, QA, Prod, etc.)
  • Level 2: Data Center name
  • Level 3: Registered Servers

In registering these server names you can also include descriptions, which I love. I make sure that myself and other DBAs include a standardized set of information such as:

Purpose: Communication System; Host Name: DEV-DB-003; DNS Aliases: CommunicationSystem.mydomain.com; Former name: V-DEV-DB-001;

In my example above the server was named V-DEV-DB-001 but was then migrated to a physical server and renamed to DEV-DB-003. V-DEV-DB-001 was set to DEV-DB-003’s IP address as a DNS for backwards compatibility.

Here’s where the query comes into play. If I ever have someone inquire about an old server that we no longer have in our list I can query the central management server for ‘V-DEV-DB-001’ and find out that that is a deprecated name for DEV-DB-003. I could also decide that I want to know all servers related to our communication system. Filtering on those keywords would give me a list of all servers in all environments related to that system.

I have found these queries to be very useful so here’s a couple to get you start. They should be customized to your exact folder structure.

Permissions

You will need the MSDB fixed database role ServerGroupReaderRole to execute the below script. I’d avoid granting db_datareader on MSDB, which I’ve seen recommended in the past.

Generic Script

This script I wrote for this post specifically. It dumps out basic information which can be used no matter what your folder structure is.

SELECT CASE (SELECT name FROM msdb.dbo.sysmanagement_shared_server_groups WHERE server_group_id=Grps.parent_id) WHEN 'DatabaseEngineServerGroup' THEN 'NONE'
ELSE (SELECT name FROM msdb.dbo.sysmanagement_shared_server_groups WHERE server_group_id=Grps.parent_id) END AS [Parent Group]
, Grps.name AS [Group Name]
, Srv.name AS [Display Name], Srv.server_name AS [Server Name], Srv.[description]
FROM msdb.dbo.sysmanagement_shared_registered_servers Srv
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_server_groups Grps ON Srv.server_group_id = Grps.server_group_id

The script I use

This script is what I use for my searching and it is based on that Environment–>Data Center–>Server folder structure that I mentioned above.

--I have this script attached to a keyword so I include this
--SQLCMD mode connection to my central management server
:CONNECT DatabaseServerList.mydomain.com

--Search word(s)
--Wild cards are appended to the start and end for you
DECLARE @Search NVARCHAR(250) = N'search-keyword'

--Query your central management server
SELECT CASE Grps.parent_id
WHEN NULL THEN Grps.name
ELSE (SELECT name FROM msdb.dbo.sysmanagement_shared_server_groups WHERE server_group_id=Grps.parent_id)
END
AS [Environment]
, CASE Grps.parent_id
WHEN NULL THEN 'Unknown'
ELSE Grps.name
END
AS [Data Center]
, Srv.[name] AS [Display Name]
, Srv.[server_name] AS [Fully Qualified Name]
, Srv.[description] AS [Description]
FROM msdb.dbo.sysmanagement_shared_registered_servers Srv
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_server_groups Grps ON Srv.server_group_id = Grps.server_group_id
WHERE Srv.[name] LIKE '%' + @Search + '%'
OR Srv.[server_name] LIKE '%' + @Search + '%'
OR Srv.[description] LIKE '%' + @Search + '%'
ORDER BY [Environment], [Data Center] DESC, [Display Name], [Fully Qualified Name]

Leave a Reply

%d bloggers like this: