{"id":16,"date":"2013-06-12T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/querying-central-management-servers"},"modified":"2026-03-18T21:50:02","modified_gmt":"2026-03-18T21:50:02","slug":"querying-central-management-servers","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2013\/06\/12\/querying-central-management-servers\/","title":{"rendered":"Querying Central Management Servers"},"content":{"rendered":"<p>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.<\/p>\n<p>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\u2019s SSMS Registered Servers window (just a quick refresh is needed for DBAs who keep their SSMS open at all times).<\/p>\n<p>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.<\/p>\n<p>At one of my jobs I setup a simple hierarchy for databases:<\/p>\n<ul>\n<li>Level 1: Environment (Dev, QA, Prod, etc.)<\/li>\n<\/ul>\n<ul>\n<li>Level 2: Data Center name<\/li>\n<\/ul>\n<ul>\n<li>Level 3: Registered Servers<\/li>\n<\/ul>\n<p>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:<\/p>\n<p>> Purpose: Communication System; Host Name: DEV-DB-003; DNS Aliases: CommunicationSystem.mydomain.com; Former name: V-DEV-DB-001;<\/p>\n<p>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\u2019s IP address as a DNS for backwards compatibility.<\/p>\n<p>Here\u2019s 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 \u2018V-DEV-DB-001\u2019 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.<\/p>\n<p>I have found these queries to be very useful so here\u2019s a couple to get you start. They should be customized to your exact folder structure.<\/p>\n<h2>Permissions<\/h2>\n<p>You will need the MSDB fixed database role <strong><em>ServerGroupReaderRole<\/strong> <\/em>to execute the below script. I\u2019d avoid granting db_datareader on MSDB, which I\u2019ve seen recommended in the past.<\/p>\n<h2>Generic Script<\/h2>\n<p>This script I wrote for this post specifically. It dumps out basic information which can be used no matter what your folder structure is.<\/p>\n<pre><code class=\"language-\">SELECT CASE (SELECT name FROM msdb.dbo.sysmanagement_shared_server_groups WHERE server_group_id=Grps.parent_id) WHEN 'DatabaseEngineServerGroup' THEN 'NONE'\nELSE (SELECT name FROM msdb.dbo.sysmanagement_shared_server_groups WHERE server_group_id=Grps.parent_id) END AS [Parent Group]\n, Grps.name AS [Group Name]\n, Srv.name AS [Display Name], Srv.server_name AS [Server Name], Srv.[description]\nFROM msdb.dbo.sysmanagement_shared_registered_servers Srv\nLEFT OUTER JOIN msdb.dbo.sysmanagement_shared_server_groups Grps ON Srv.server_group_id = Grps.server_group_id<\/code><\/pre>\n<h2>The script I use<\/h2>\n<p>This script is what I use for my searching and it is based on that Environment\u2013>Data Center\u2013>Server folder structure that I mentioned above.<\/p>\n<pre><code class=\"language-\">--I have this script attached to a keyword so I include this\n--SQLCMD mode connection to my central management server\n:CONNECT DatabaseServerList.mydomain.com\n\n<p>--Search word(s)\n--Wild cards are appended to the start and end for you\nDECLARE @Search NVARCHAR(250) = N'search-keyword'<\/p>\n\n<p>--Query your central management server\nSELECT CASE Grps.parent_id\nWHEN NULL THEN Grps.name\nELSE (SELECT name FROM msdb.dbo.sysmanagement_shared_server_groups WHERE server_group_id=Grps.parent_id)\nEND\nAS [Environment]\n, CASE Grps.parent_id\nWHEN NULL THEN 'Unknown'\nELSE Grps.name\nEND\nAS [Data Center]\n, Srv.[name] AS [Display Name]\n, Srv.[server_name] AS [Fully Qualified Name]\n, Srv.[description] AS [Description]\nFROM msdb.dbo.sysmanagement_shared_registered_servers Srv\nLEFT OUTER JOIN msdb.dbo.sysmanagement_shared_server_groups Grps ON Srv.server_group_id = Grps.server_group_id\nWHERE Srv.[name] LIKE '%' + @Search + '%'\nOR Srv.[server_name] LIKE '%' + @Search + '%'\nOR Srv.[description] LIKE '%' + @Search + '%'\nORDER BY [Environment], [Data Center] DESC, [Display Name], [Fully Qualified Name]<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3,5,7,10,13,14,21],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-tsql2sday","category-administration","category-azure","category-community","category-disaster-recovery","category-microsoft-sql-server","category-performance","category-t-sql"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":279,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/16\/revisions\/279"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}