Adding Servers to SQL CMS (Central Management Server) using Powershell

One challenge I’ve had recently is getting a list of servers imported in to a CMS (Central Management Server). While this is easy if a CMS already existed, it isn’t so straight forward if this is the first time CMS has been used in an environment.

Enter Powershell and SMO. Like great warriors, Powershell and SMO seem to have a weapon for every occasion. On this occasion, the SMO RegisteredServers class comes in handy.

With the Powershell code below, you can take a pipe delimited list of AKA_Name|Instance in a text file and import those servers into the CMS. This could be quickly modified to import into a specific group within the CMS or to exclude the AKA_Name and make the name in the CMS the name of the Instance.

It’s nearly Christmas – so when the demands on your time get frightful, let it SMO, Let it SMO, Let it SMO!

$CMSInstance = "CMSSQLInstance"
$ServersPath = "C:\users\KyleNeier\PSSCRIPTS\allservers_w_Names.txt"
    
#Load SMO assemplies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null


$connectionString = "Data Source=$CMSINstance;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)
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"]

$Servers = Import-Csv -Delimiter "|" -Header "Name","Instance" -Path $ServersPath

foreach($Server in $Servers)
{
      #Put this in loop to deal with duplicates in list itself
      $AlreadyRegisteredServers = @()
      $CMSDBStore.GetDescendantRegisteredServers()|%{$AlreadyRegisteredServers +=$_.Name.Trim()}
      
      $RegServerName = $Server.Name
      $RegServerInstance = $Server.Instance
      
      if($AlreadyRegisteredServers -notcontains $RegServerName)
      {
            Write-Host "Adding Server $RegServerName"
            $NewServer = New-Object Microsoft.SqlServer.Management.REgisteredServers.RegisteredServer($CMSDBStore, "$RegServerName")
            $NewServer.SecureConnectionString = "server=$RegServerInstance;integrated security=true"
            $NewServer.ConnectionString = "server=$RegServerInstance;integrated security=true"
            $NewServer.ServerName = "$RegServerInstance"
            $NewServer.Create()
      }
      else
      {
            Write-Host "Server $RegServerName already exists - cannot add."
      }
}