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.

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]
    
    if($recurse)
    {
        return Parse-ServerGroup -serverGroup $cmsRootGroup | select ServerName
    }
    else
    {
        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.

$serverList = Get-ServerList -cmsName '.\SQL2012' `
    -serverGroup Production -recurse
$serverList | Format-Table

Get-ServerList-syntax

 

This article has 2 comments

  1. 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.

Leave a Reply