Registering multiple servers in Central Management Server (CMS) using PowerShell Script

  • [アーティクル]

 

Below are the steps using PowerShell script (learned from another Kalyan Yella, Microsoft):

Step 1: Open notepad and copy and paste below PowerShell script and save the file as “RegisterServers.ps1”  at c:\scripts\   --- you can save anywhere you want – just make a note of it as you will use this in next step

param($server,$path = ‘SQLSERVER:\SQLRegistration\Central Management Server Group\TestGrp’,[bool]$allowDups=$false) SET-LOCATION $path if (!(Test-Path $(Encode-Sqlname $server))) {New-Item $(Encode-Sqlname $server) -itemtype registration -Value “server=$server;integrated security=true” }

Make sure to change the highlighted GroupName to whatever name you want to keep.

Step 2. Go to SSMS and right click on CMS server and click Start PowerShell – this will open up PowerShell window

Step 3: type following command:

invoke-sqlcmd -Query "select SQLServerName from ServerListTable where SQLServerName like '%DEV%' and SQLVersion like '9.%'" –serverinstance ‘<SQLInstanceWhere ServerListTable is stored>’-database '<DB where ServerListTable is stored>' | %{c:\scripts\RegisterServers.ps1 $_.SQLServerName}

Highlighted T-SQL Query can be anything that can provide you a list of multiple SQL Server Instances that you want to register.

Make sure the entire command below is in one line

Step 4: hit enter

This should register all the SQL Servers resulted from above T-SQL SQL query by using RegisterServers.ps1 script in to appropriate folder structure you choose in step 1.  Verify by going to Registered Servers and expanding Central Management Server group

Comments

  • Anonymous
    November 29, 2011
    I am getting the following error while running the script in power shell: Encode-SqlName : Cannot validate argument on parameter 'SqlName'. The argument is null or empty. Supply an argument that is not null or empty and then try the command again. At C:scriptsRegisterServers.ps1:1 char:168
  • param($server,$path = `SQLSERVER:SQLRegistrationCentral Management Server G roupTestGrp',[bool]$allowDups=$false) SET-LOCATION $path if (!(Test-Path $(Enc ode-Sqlname <<<<  $server))) {New-Item $(Encode-Sqlname $server) -itemtype regi stration -Value "server=$server;integrated security=true" }    + CategoryInfo          : InvalidData: (:) [Encode-SqlName], ParameterBind   ingValidationException    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.SqlSe   rver.Management.PowerShell.EncodeSqlName Test-Path : Cannot bind argument to parameter 'Path' because it is null. At C:scriptsRegisterServers.ps1:1 char:151
  • param($server,$path = `SQLSERVER:SQLRegistrationCentral Management Server G roupTestGrp',[bool]$allowDups=$false) SET-LOCATION $path if (!(Test-Path <<<< $(Encode-Sqlname $server))) {New-Item $(Encode-Sqlname $server) -itemtype regi stration -Value "server=$server;integrated security=true" }    + CategoryInfo          : InvalidData: (:) [Test-Path], ParameterBindingVa   lidationException    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,M   icrosoft.PowerShell.Commands.TestPathCommand