{"id":89,"date":"2014-11-05T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/query-central-management-server-with-powershell-smo"},"modified":"2026-03-18T21:51:22","modified_gmt":"2026-03-18T21:51:22","slug":"query-central-management-server-with-powershell-smo","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/11\/05\/query-central-management-server-with-powershell-smo\/","title":{"rendered":"Query Central Management Server with PowerShell SMO"},"content":{"rendered":"<p>All production or operational DBAs need to maintain a server \/ instance list. SQL Server\u2019s <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/bb895144.aspx\">Central Management Server<\/a>\u00a0(CMS)\u00a0is a great means of managing that list with auxiliary benefits such as group queries, a central location for managing policies and SQL Agent jobs, and exposing the list via MSDB system views.<\/p>\n<p>The goal of the below\u00a0function is to retrieve the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/microsoft.sqlserver.management.registeredservers.registeredserversstore.aspx\">Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore<\/a> object and then return a list of <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/microsoft.sqlserver.management.registeredservers.registeredserver.aspx\">RegisteredServers<\/a>\u00a0based on a folder that exists in your CMS.<\/p>\n<pre><code class=\"language-\">Import-Module SQLPS -DisableNameChecking\nFunction Parse-ServerGroup($serverGroup)\n{\n    $results = $serverGroup.RegisteredServers;\n    foreach($group in $serverGroup.ServerGroups)\n    {\n        $results += Parse-ServerGroup -serverGroup $group;\n    }\n    return $results;\n}\nFunction Get-ServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse)\n{\n    $connectionString = \"data source=$cmsName;initial catalog=master;integrated security=sspi;\"\n    $sqlConnection = New-Object (\"System.Data.SqlClient.SqlConnection\") $connectionstring\n    $conn = New-Object (\"Microsoft.SQLServer.Management.common.serverconnection\") $sqlconnection\n    $cmsStore = New-Object (\"Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore\") $conn\n    $cmsRootGroup = $cmsStore.ServerGroups[\"DatabaseEngineServerGroup\"].ServerGroups[$serverGroup]\n    \n    if($recurse)\n    {\n        return Parse-ServerGroup -serverGroup $cmsRootGroup | select ServerName\n    }\n    else\n    {\n        return $cmsRootGroup.RegisteredServers | select ServerName\n    }\n}<\/code><\/pre>\n<p>> See how this script was put to use\u00a0in my article on <a href=\"http:\/\/www.sqlshack.com\/backup-testing-powershell-part-1-test\/\">automated backup file testing<\/a>.<\/p>\n<p>Once you have the function loaded it is simple to search different server groups and return a list of RegisteredServers for display or use in code.<\/p>\n<pre><code class=\"language-\">$serverList = Get-ServerList -cmsName '.\\SQL2012' `\n    -serverGroup Production -recurse\n$serverList | Format-Table<\/code><\/pre>\n<p><a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2014\/11\/Get-ServerList-syntax.jpg\"><img decoding=\"async\" src=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2014\/11\/Get-ServerList-syntax.jpg\" alt=\"\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>All production or operational DBAs need to maintain a server \/ instance list. SQL Server\u2019s Central Management Server\u00a0(CMS)\u00a0is a great means of managing that list with auxiliary benefits such as group queries, a central location for managing policies and SQL Agent jobs, and exposing the list via MSDB system views. The goal of the below\u00a0function [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":568,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-89","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/89","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=89"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/89\/revisions"}],"predecessor-version":[{"id":352,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/89\/revisions\/352"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/568"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=89"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=89"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=89"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}