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