Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using PowerShell to Register All SQL Instances Into Central Management Server


By:   |   Last Updated: 2012-03-29   |   Comments (14)   |   Related Tips: More > Central Management Servers

Problem

Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. SQL Server has introduced a new feature called Central Management repository which can be used to manage multiple instances. One of the challenges to using CMS is that you have to register all the SQL Servers manually into CMS.  The below article will guide you on how to register hundreds of servers in a second for SQL Server CMS.

Solution

As a best practice, most organizations maintain the inventory of SQL Server instances so they now what is running where.  To register multiple servers into CMS you will need to have this list of all SQL Server instances. If you don't have an inventory you can create the database inventory using SQLCMD as shown below.

Run the SQLCMD -L to get a list of all SQL Server instances registered in the network and store them into a SQL table.

SQLCMD Command to retrieve the servers in network

Minimum Requirement to register the multiple servers into CMS

  • SQL Server Instance Inventory
  • SQL Server 2008 Instance to act as the CMS

You can follow the below steps to register the SQL Instances into CMS.

Step 1: Copy and Paste the below PowerShell script into NotePad and save the file as "RegSvr.ps1" in "c:\myscripts\" folder. (Please note you can save the file at any location, but in the next step you will need to make that adjustment.)

$cmsservers = invoke-sqlcmd -serverinstance 'Put SQL Instance name here' 
-database 'Put SQL Inventory DB Here'
-query "SELECT SERVERNAME FROM SERVERLISTTABLE" cd 'SQLSERVER:\sqlregistration\Central Management Server Group\CMSServerName\CMSGroupName\
CMSSubGroupName\' foreach ($server in $cmsservers) { $svrname = $server.serverName $regsvrname = $svrname if ($regsvrname -like "*\*") { $regsvrname = $regsvrname.replace("\", "%5C") } $svrname new-item $regsvrname -itemtype registration -value "server= $regsvrname;
integrated security=true" }

Step 2: Modify the PowerShell script and put the SQL Instance Name, Inventory Database Name, Query to Retrieve the SQL Instance Names, and Central Management Server Name and the CMS Path where you want to register the SQL Server Instances. Once you update the script, save it again.

For example:

  • SQL Instance Name is MSSQLCMDB
  • Inventory Database Name is CMDB
  • Table name where the SQL Instances name stored is TBLSERVERLIST
  • and CMS server name and path is WIT\Production\SQL2008

Please take a look at the my CMS server and the path. I want to register the SQL instances into SQL2008 group.

Central Management Server

You have to edit the powershell script as below.

Powershell Script Example

Step 3: Go to object explorer and connect to the Central Management Server. Right click on the CMS Server Name and click on "Start PowerShell" as shown below.

Step to start the powershell

Step 4: Once the PowerShell prompt opens, you have to enter the command SL "Location of PowerShell script file" as shown below.

PS command to change the directory

Step 5: Now here is the last step that will execute the PowerShell script and it will register the servers into CMS. To execute the script you have to run "./regsvr.ps1" on the prompt as shown below.

PS command to execute the power shell script

Step 6: Go to the CMS server and expand the group to check the list of registered servers or you can query the CMS tables as shown below.

/* Servers and groups */
SELECT DISTINCT groups.name AS 'Server Group Name'
     ,svr.server_name AS 'Server Name'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal groups 
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr
 ON groups.server_group_id = svr.server_group_id;
GO
Next Steps
  • Create the different groups into CMS and register the SQL Servers into each group.
  • Evaluate your Policies using the CMS registered servers.
  • Maintain the CMS and add or remove SQL instances as needed.


Last Updated: 2012-03-29


next webcast button


next tip button



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, July 24, 2018 - 6:42:14 AM - Vusi Back To Top

HI Jagul 

this is the error i am getting when running the scrip, please advise . much appriciated fro the script .

PS H:\MSSQL$AIMS_MAIN1_LIVE\MntVol-Remote-BACKUP01\Tumi> .\RegSrv.ps1

- : Missing expression after unary operator '-'.

    + CategoryInfo          : ParserError: (-:String) [], ParseException

    + FullyQualifiedErrorId : MissingExpressionAfterOperator


Wednesday, March 08, 2017 - 3:25:34 AM - Seshatheri Back To Top

i am getting below error can you please help me

 

New-Item : Cannot bind argument to parameter 'Path' because it is null.

At T:\Register_Server_automation\RegSrvs.ps1:12 char:10

+ new-item $regsvrname -itemtype registration -value "server= $regsvrname;

+          ~~~~~~~~~~~

    + CategoryInfo          : InvalidData: (:) [New-Item], ParameterBindingVal

   idationException

    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,M

   icrosoft.PowerShell.Commands.NewItemCommand


Wednesday, March 01, 2017 - 4:03:17 PM - Sundar Back To Top

For below (without - symbol)

cd 'SQLSERVER:\sqlregistration\Local Server Groups\Delta\'

 

I get following error

PS D:\dummy> ./RegSrvr.ps1

cd : Cannot find path 'SQLSERVER:\sqlregistration\Local Server Groups\D1\' because it does not exist.

At D:\dv\RegSrvr.ps1:2 char:3

+ cd <<<<  'SQLSERVER:\sqlregistration\Local Server Groups\D1\'

    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\sqlr...r Groups\D1

   a\:String) [Set-Location], ItemNotFoundException

    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

ServerName1

new-item : The type is not a known type for the file system. Only "file" and "directory" can be specified.

At D:\dummy\RegSrvr.ps1:12 char:9

+ new-item <<<<  $regsvrname -itemtype registration -value "server= $regsvrname;integrated security=true"

    + CategoryInfo          : InvalidArgument: (:) [New-Item], PSArgumentException

    + FullyQualifiedErrorId : Argument,Microsoft.PowerShell.Commands.NewItemCommand

 

 


Wednesday, March 01, 2017 - 3:58:18 PM - Sundar Back To Top

It fails for

 $cmsservers = invoke-sqlcmd -serverinstance 'C' -database 'D' -query "SELECT SERVERNAME FROM SERVERLISTTABLE"

-cd 'SQLSERVER:\sqlregistration\Local Server Groups\D1\'

 

with following error message:

PS D:\dummy> ./RegSrvr.ps1

- : Missing expression after unary operator '-'.

    + CategoryInfo          : ParserError: (-:String) [], ParseException

    + FullyQualifiedErrorId : MissingExpressionAfterOperator

 

 


Sunday, September 07, 2014 - 2:34:55 PM - Yunus Parvez Back To Top

Hello,

 

This script is working using SQLPS but when i am using the same script in JOB in SQL Server Agent using type as Powershell getting error that 'Cannot find path 'SQLSERVER:\SQLRegistration\Central Management Server Group

 

Can you please help as daily we have 1-2 server added to our env.


Tuesday, May 27, 2014 - 4:44:23 AM - SD Back To Top

Does anyone know how one goes about removing registered servers using powershell. The reason for this is to remove registered servers when any get decommissioned.

I was thinking of using the following code, but this does not seem to work.

foreach ($server in $cmsdelservers)
{
$svrname = $server.SQLInstance
$regsvrname = $svrname
if ($regsvrname -like "*\*")
{
$regsvrname = $regsvrname.replace("\", "%5C")
}
cd 'SQLSERVER:\sqlregistration\Central Management Server Group\BBAMINFDEV01\BBAM\PRD\'
remove-item $regsvrname
}

 

Thanks


Thursday, April 17, 2014 - 1:45:45 PM - Crazy DBA Back To Top

I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from your posts and other SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports. Can you please check it and give me any suggestions to improve it?

Thnx


Thursday, April 03, 2014 - 6:23:08 AM - Martin Back To Top

To solve the issue with named instances I replaced $regsvrname with $svrname in the last line, like this:

$svrname
new-item $regsvrname -itemtype registration -value "server= $svrname;
integrated security=true" }

Tuesday, July 30, 2013 - 5:00:34 PM - Hawk Back To Top

This is a great tip, but I've found the registration of named instances is a problem.  Because the backslash is converted to a "%5C" in the "Server Name" value of the registration, but the "Registered Server Name" is correct with the backslash.

 

I see the script seeks to overcome the issue of a backslash in Powershell with the hexidecimal "%5C", but it does not appear to be working properly.

 

Has anyone found a means to overcome this?  I've got hundreds of instances to register and many have a backslash in the name, since they're named instances.


Wednesday, July 04, 2012 - 2:53:31 AM - Amey Back To Top

Hi Jugal, as you see I have just added my servername  , database name and Query and the path of CMS where I wanted to register the SQL instance. I have not changed the code.

I am getting the below error in the powershell window :

 

New-Item : A parameter cannot be found that matches parameter name 'registration' . At E:\scripts\RegSrv.Ps1:14 char:9

+ new-item <<<< $regsvrname-itemtype registration - value "server='$server.Mach_lpar_nm;integrated security =true"

 

Please suggest, 

Thanks, 

 

Amey


Tuesday, July 03, 2012 - 1:51:53 PM - Jugal Back To Top

Instead of modifying the code, can you please add your Server Name, Query into script. That will resolve the issue


Monday, July 02, 2012 - 5:02:14 PM - Amey Back To Top

Hello Jugal , 

Thanks for the script  ! I modified the script  for my environment as below  

-----------modified script--------------------------------------

$cmsservers = invoke-sqlcmd -serverinstance 'ax-siscdcsql821\sql821'-database'ETXDSDBS'-query"select distinct MACH_LPAR_NM from [ETXDSDBS].[dbo].[v_Sql_metrics_extended_no_dl] where MACH_LPAR_NM like '%AX-RISC%'

"cd'SQLSERVER:\sqlregistration\Central Management Server Group\AX-SISCCMSQLPOC\PBM_DEV\test\'

 

foreach ($server in $cmsservers)

{

$svrname = $server.serverName

$regsvrname = $svrname

if ($regsvrname -like "*\*")

{

$regsvrname = $regsvrname.replace("\", "%5C")

}

$svrname

new-item $regsvrname -itemtype registration -value "server= $regsvrname;

integrated security=true"

}

 

------------------------------------modified script-------------------------------------------

 

When I ran this script on the CMS , I am getting the following error in powershell 

 

PS E:\scripts> ./RegSvr.ps1

Incomplete string token.

At E:\scripts\RegSvr.ps1:8 char:25

+ if ($regsvrname -like "*\ <<<< *")

 

Can you please shed some light on this error message ? Thanks again for your time and the script!

 

- Amey Thakur 

 

 

Saturday, March 31, 2012 - 4:01:49 PM - Jugal Shah Back To Top

You can try alternative, setSPN -L (Service Account), if the Service Account is common for all the SQL instances...


Thursday, March 29, 2012 - 9:04:52 AM - Karsten Mueller Back To Top

Hello!

First of all - thanx for this tip! I think I can use it, because we´ve got a large SQL-Server Environment.

But one question: Do you know how to use the sqlcmd -L command to list also instances on SQL-Server-Failover-Clusters.
I tested it, but I can see all my normal instances, except clustered instances.

Thanks in advence!

Greeting,

Karsten Mueller


Learn more about SQL Server tools