sql - 使用Powershell将SQL Server实例添加到中央管理服务器组

Question

我正在尝试创建一个脚本，以自动遍历我们所有SQL Server实例的文本文件，并将每个脚本添加到CMS(如果尚不存在)中。我想尝试通过SMO而不是对sql字符串进行硬编码来完成此操作。以下是我到目前为止所拥有的，但似乎无法正常工作。任何帮助，将不胜感激。谢谢。

最终，我将添加更多的If语句，以将实例分配给某些组，但是现在，我只是想让它填充所有内容。

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

Answer 1

我将您的脚本缩减为基本内容，并且对我有用。我确实必须更改连接命令才能在我的环境中工作，但除此之外，注册SQL Server的默认实例没有任何错误。刷新CMS服务器后，新注册的服务器便可见并可以访问。

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