Tuesday, December 13, 2011

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"
            Write-Host "Server $RegServerName already exists - cannot add."


Derek said...

This is AWESOME, thanks for shareing!!

Justin said...

excellent, thanks Kyle, this will save me a lot of time today !

Matt said...

I cant seem to get this to work on an already existing cms with instances in it. It doesnt populate the newer ones into the msdb tables. Any idea as to why this might be?