SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

Kyle Neier

Kyle has been working with computers professionally since 1995 in both a development and administrative capacity. For over 10 of those years, SQL Server has been the focus of both a professional and personal passion. Kyle has worked in online retail, manufacturing, EMR software, and pharmaceutical industries. Kyle joined PTI in 2011 as a Senior Microsoft Consultant where he continues to chase his passion of automating DBA processes and doing everything humanly possible to prevent the 0200 page. Kyle serves as the Vice-President of the Indianapolis chapter of PASS (www.indypass.org) and served on the committee to bring SQL Saturday to Indianapolis. He shares his passion for SQL Server with others through speaking and mentoring at local and regional events.


Leave a comment on the original post [sqldbamusings.blogspot.com, opens in a new window]

Loading comments...