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

What error are you getting? "it doesnt seem to be working" isn't very specific. — dpw
– dpw, Commented Jan 28, 2014 at 21:29
Start smaller. Pick one server that you know isn't already in the CMS and try to add that. — Ben Thul
– Ben Thul, Commented Jan 28, 2014 at 23:23
It doesnt add the servers is whats wrong with it, Ive tried adding just one server that I know isnt there already, ive even tried clearing the cms tables in msdb and starting from scratch and that doesnt work either :/ — Skunny11
– Skunny11, Commented Jan 29, 2014 at 12:59

Bruce · Accepted Answer · 2014-01-29 20:58:59Z

3

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()

edited Jan 29, 2014 at 20:58

answered Jan 29, 2014 at 16:05

Bruce

1,6231 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Skunny11 Over a year ago

Thanks, I will test that out as soon as I can and let you know how it goes.

Skunny11 Over a year ago

Thanks that worked, I will open up a new question on getting it to iterate through my servers list and add each instance from that server.

Bruce Over a year ago

Glad it helped. I expect named instances will work but haven't tried it with the script. I might be able to try that tomorrow. Or you could give it a try and let us know.

Skunny11 Over a year ago

Id appriciate it. I can have it look through a text file of all the named instances and add them that way but I wanted to bypass that by having it iterate a much shorter list of servers and find each named instance on that server (if there are multiple) and add each one. Having trouble doing that though because im not sure of the syntax or objects id have to set up for getting instance names.

Collectives™ on Stack Overflow

Add SQL Server Instances to Central Management Server Groups with Powershell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related