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."
}
}
Смотрите видео Register All SQL Instances Into Central Management Server | Adding Multiple SQL Servers to SQL CMS онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MS SQL DBA Tech Support 11 Май 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 1,946 раз и оно понравилось 27 людям.