Register All SQL Instances Into Central Management Server | Adding Multiple SQL Servers to SQL CMS
Set-ExecutionPolicy unrestricted
$CMSInstance = "LENOVO\SQLMARCH"
$ServersPath = "C:\Test\serverlist.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")
$secureString = ConvertTo-SecureString "server=$RegServerInstance;integrated security=true" -AsPlainText -Force
$NewServer.SecureConnectionString = $secureString
$NewServer.ConnectionString = "server=$RegServerInstance;integrated security=true"
##$NewServer.ServerName = "$RegServerInstance"
$NewServer.Create()
}
else
{
Write-Host "Server $RegServerName already exists - cannot add."
}
}
Watch video Register All SQL Instances Into Central Management Server | Adding Multiple SQL Servers to SQL CMS online, duration hours minute second in high quality that is uploaded to the channel MS SQL DBA Tech Support 11 May 2023. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 1,946 times and liked it 27 visitors.
