Add SQL Server Instances to Central Management Server Groups with Powershell

Question

I am trying to create a script to automatically iterate through a text file of all our SQL Server instances and add each on if it doesn't already exist to the CMS. I want to try doing this through SMO instead of hardcoding sql strings in. Below is what I have so far but it doesn't seem to be working. Any help would be appreciated. Thanks.

Eventually I will add more If statements in to distribute the instances to certain groups but for now I'm just trying to get it to populate everything.

$CMSInstance = "cmsinstancename"
$ServersPath = "C:\Scripts\InPutFiles\servers.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 = Get-Content $ServersPath;

foreach($Server in $Servers)
{
      #Put this in loop to deal with duplicates in list itself
      $AlreadyRegisteredServers = @()
      $CMSDBStore.GetDescendantRegisteredServers()

      $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."
      }
}

Answer 1

I cut your script down to just the basics and it works for me. I did have to change the connection command to work in my environment but other than that and registering a default instance of SQL Server there were no errors. Once I did a refresh of the CMS server the newly registered server was visible and accessible.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | Out-Null

$CMSInstance = 'CMS_ServerName'
$connectionString = "Data Source=$CMSInstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = New-Object System.Data.SqlClient.SqlConnection("Server=$CMSInstance;Database=master;Integrated Security=True")
$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"]

$RegServerName = 'ServerToRegister'
$RegServerInstance = $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()