Query Central Management Server with PowerShell SMO

All production or operational DBAs need to maintain a server / instance list. SQL Server’s Central Management Server (CMS) is 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 function is to retrieve the Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore object and then return a list of RegisteredServers based on a folder that exists in your CMS.

[sql]Import-Module SQLPS -DisableNameChecking
Function Parse-ServerGroup($serverGroup)
$results = $serverGroup.RegisteredServers;
foreach($group in $serverGroup.ServerGroups)
$results += Parse-ServerGroup -serverGroup $group;
return $results;
Function Get-ServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse)
$connectionString = "data source=$cmsName;initial catalog=master;integrated security=sspi;"
$sqlConnection = New-Object ("System.Data.SqlClient.SqlConnection") $connectionstring
$conn = New-Object ("Microsoft.SQLServer.Management.common.serverconnection") $sqlconnection
$cmsStore = New-Object ("Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore") $conn
$cmsRootGroup = $cmsStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$serverGroup]

return Parse-ServerGroup -serverGroup $cmsRootGroup | select ServerName
return $cmsRootGroup.RegisteredServers | select ServerName

See how this script was put to use in my article on automated backup file testing.

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.

[sql]$serverList = Get-ServerList -cmsName ‘.\SQL2012’ `
-serverGroup Production -recurse
$serverList | Format-Table[/sql]









2 responses to “Query Central Management Server with PowerShell SMO”

  1. Chrissy LeMaire (@cl) Avatar

    Thanks for the straightforward recursion code, Derik. I used the idea within my own code posted at https://blog.netnerds.net/smo-recipes/central-management-server/. Props went to you.

    1. Derik Hammer Avatar

      I am very glad to hear that my post was helpful to you. Happy scripting!

Leave a Reply

%d bloggers like this: