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

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

Automate Registering and Maintaining Servers in SQL Server Management Studio (SSMS)


By:   |   Read Comments (33)   |   Related Tips: 1 | 2 | 3 | 4 | More > SQL Server Management Studio

Problem

When you have a lot of SQL Server instances to manage and servers are retired and new ones are added, it becomes a headache to manually maintain these registered servers in SQL Server Management Studio (SSMS).  In this tip we look at how to use a control file that lists the structure to use within SSMS and a PowerShell script that will automatically maintain the list of registered servers within SSMS. 

Solution

An easy way to solve this issue is to list all folders and the SQL Server instance names in a spreadsheet or a plain text file, like an INI file in the following format:

[HR]
HR_Server1
HR_Server2

[HR\REPORT]
HR_RPT1
HR_RPT2

[HR\REPORT\AUDIT]
MyServer

[Accounting]
AC_Server1
;AC_Server2 #comment out
AC_Server3

[Test]
T_Server1
T_Server2

[DEV]
Dev_1
Dev_2
Dev_3\Inst_1

The [HR\REPORT] section indicates there is a sub-folder REPORT under the HR folder. You can create multiple subfolder levels as needed.  With this information, we can automatically recreate the registered servers and folders in SSMS using a PowerShell cmdlet and the final results in SSMS will look like the following:

register_svr.jpg

Technical Challenges Registering the Servers in Management Studio

There are a few challenges in this solution:

  • We need to extract the folder information from the INI file, i.e. we need to know [HR] is a folder name
  • We need to extract the subfolder information if needed, i.e. we need to know that [HR\REPORT] means [REPORT] is a subfolder under parent folder [HR]
  • We need to know any item without square brackets, like HR_Server1, is a server name.

To solve these challenges and make the code elegant, I choose to use Regular Expressions with PowerShell to "interpret" the INI file. Each item in the INI file will have a corresponding PSObject to save its information. The PSObject has three properties:

  1. ParentFolder, where '.' means the root folder
  2. Type, where 'Directory' means a folder and 'Registration' is a server under a folder
  3. Value, is the name of a folder or a server

All items in the INI file will finally be translated into a PSObject array, and this array will be looped through to generate the corresponding folder/subfolder/registered servers in SSMS.

So here is the source code (it should be run in SQL Server 2008 or later environments, where SQLPS is installed)

#ensure the script is run under SQL Server PowerShell window
if ((get-process -id $pid).MainWindowTitle -ne 'SQL Server PowerShell')
{
        write-error "`r`n`r`nScript should be run in SQL Server PowerShell Window. `r`n`r`nPlease start SQLPS from SSMS to ensure the correct SQLPS version loaded";
        return;

}

[string]$choice = 'Database Engine Server Group' # 'Central Management Server Group\CentralServerName';
$srv = @();

#part 1: Interpret the INI file
$pf='.';
SWITCH -Regex -File C:\TEMP\server_list.TXT # change to your own INI file path
{
    "^\s*\[(\w+[^\\])]$" #folder, the format is [folder]
    {
        $srv +=New-Object -TypeName psobject -Property @{ParentFolder='.'; Type='Directory'; Value=$Matches[1]; };
        $Pf = $matches[1];       
    }

    "^\s*\[(\w+\\.+)]$" #sub-folder, the format is [folder\subfolder]
    {
        $pf = split-path -Path $matches[1];
        [string]$leaf = split-path $matches[1] -Leaf;
        $srv +=New-Object -TypeName psobject -Property @{ParentFolder=$pf; Type='Directory'; Value=$leaf; };
        $pf = $matches[1];
             
    }

    '^\s*(?![;#\[])(.+)' # if you want to comment out one server, just put ; or # in front of the server name    
    {
        $srv += New-Object -TypeName PSObject -Property @{ParentFolder=$pf; Type='Registration'; value=$matches[1];}
    }

}

#part 2: create the folder/registered server based on the info in $srv

Set-Location "SQLServer:\SqlRegistration\$($choice)";
dir -Recurse | Remove-Item -force; #clean up everything
foreach ($g in $srv)
{
    if ($g.Type -eq 'Directory')
    {
        if ($g.ParentFolder -eq '.')
        {
           Set-Location -LiteralPath "SQLServer:\SqlRegistration\$($choice)"
        }
        else
        {
           Set-Location -LiteralPath "SQLServer:\SqlRegistration\$($choice)\$($g.ParentFolder)";
        }
        New-Item -Path $g.Value -ItemType $g.type;
    } 
    else # it is a registered server
    {
        $regsrv = $g.value.replace("%5C","\")
        New-Item -Name $(encode-sqlname $g.value) -path "sqlserver:\SQLRegistration\$($choice)\$($g.parentfolder)" -ItemType $g.type -Value ("Server=$regsrv ; integrated security=true");
    }
    
}

Set-Location "SQLServer:\SqlRegistration\$($choice)";



How To Use The Code to Register Servers in SSMS

1. Copy and paste the code into Notepad or a PowerShell ISE window, and save it as a ps1 file, let's say, it is saved as:

c:\temp\register_svr.ps1

2. Create an INI file with your required folder/server information and save it to a folder, let's say, it is saved as:

c:\temp\server_list.txt

3. From SSMS, right-click on any SQL Server instance in "Object Explorer" and start the SQLPS environment as shown below:

start_sqlps.jpg

4. In the new opened SQLPS window, run the following two lines of code:

cd c:\temp
./register_svr.ps1

run_script.jpg

5. Go back to your Registered Servers in SSMS and right click on Database Engine and select Refresh.  You should now see the new list of servers.

I have tested the solution with SSMS for SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 and they are working. Note: In the screenshot above, \TP_W520\ is my computer name and this will be your machine name when you run this script.

Next Steps

Read more articles on MSSQLTips.com on similar topics and the use of registered servers



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips





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.



    



Friday, April 13, 2018 - 5:21:19 AM - Srinivas Back To Top

Hello All,

 

I faced issue like the CMS path is not valid.

later i came to know that we need to save both server list and powershel file in C:\ Drive and run the ps1 file using SQL Server powershell.

 


Friday, December 02, 2016 - 10:13:32 AM - Prasad Back To Top

This is a great script. Thanks !

Also, Do you have any idea where I can get the property names for the configuration other than server name. like Description, Default database and color scheme. 

 

Thanks in Advance 

Prasad Chillara

 


Monday, April 18, 2016 - 4:15:21 PM - jeff_yao Back To Top

 

Hi @K,

If you really want to change the Register server name, the key point is in this line

    New-Item -Name $(encode-sqlname $g.value) -path "sqlserver:\SQLRegistration\$($choice)\$($g.parentfolder)" -ItemType $g.type -Value ("Server=$regsrv ; integrated security=true");

If you give the expected value to -Name parameter, the registered server name will be changed.

But in this tip, I only use the real server instance name in the configuration file, i.e. there is no additioal Registered Server name matching for each real server instance name.

One potential change to the configuration ini file is that we create a dictionary like mapping

RegisterServer1 = RealServerName_1

RegisterServer2 = RealServerName_2

...

But this means, this tip's PS parsing function need to be revised to accomodate the new content format.

 

Thanks,

Jeff_yao


Monday, April 18, 2016 - 6:23:00 AM - K Back To Top

 Greetings Jeff,

I would like to ask you the same question as Ajay Babu did. I am willing to rewrite script in order to fill in registered server name (display name) different from Server Name. However I failed to find what parameter to add for it. 

 "Server=$regsrv; Regsvrname='My own display name' ; integrated security=true"

Do you know what should be the proper name of parameter instead of Regsrvname to set up display name?

with best regards,

K.


Sunday, December 20, 2015 - 12:57:22 PM - jeff_yao Back To Top

 

You are very welcome @Kannan G, and I am glad this tip is of help to you.


Saturday, December 19, 2015 - 1:10:56 PM - Kannan G Back To Top

Hi Jeff,

 

Very helpful script.. Thank you so much!!! 

 


Tuesday, October 13, 2015 - 3:13:34 PM - jeff_yao Back To Top

@Ajay, in theory, yes. However, this tip cannot do this without a big change because we need to introduce the information of the "display name" for each real server name. This will lead to the format change of the ini file and then the parsing logic in the PS script. But if I do see some high interests of such requirement from readers, I can try to refactor the whole process to make this happen.


Tuesday, October 13, 2015 - 11:44:24 AM - Ajay Babu Back To Top

Is there a way I can use registered Server name(display name) different from servername?

Not sure how to modify the script. but if the registered servername also  can be given the ini file that would be great.


Tuesday, July 21, 2015 - 1:07:48 PM - jeff_yao Back To Top

Thanks @Arvind, glad it is of help to you, and also thanks for confirming that using SQL standard authentication works (as I did not try it myself)


Tuesday, July 21, 2015 - 11:42:58 AM - ARVIND RAVISH Back To Top

Wonderful Sir. I modified the PS script to even include sa authentication. It works great!!


Wednesday, September 17, 2014 - 5:53:31 PM - jeff_yao Back To Top

Thanks @Jimit and I am glad it is of some help to you.


Wednesday, September 17, 2014 - 4:46:09 AM - Jimit Back To Top

This script works like a miracle.You are awesome. 


Tuesday, August 05, 2014 - 1:31:21 PM - jeff_yao Back To Top

Thanks for your update, @PC, and very glad to hear it works for you now.


Tuesday, August 05, 2014 - 5:06:51 AM - PC Back To Top

Hi Jeff, thank you very much for detail explanation. Now I fixed the issue with the format of config file and able to create sub-folders.


Wednesday, July 30, 2014 - 1:14:29 PM - jeff_yao Back To Top

Hi @PC,

Can you please post a sample part of your configuration file here? and also the exact error message?

In my test (yes, I did very extensive test on sql2008 to sql2012, on various computers in my environment), and I have not encountered any issue. Just some quick notice

The format of the configuration file should be:

Parent Folder to be listed first before sub folder, even if there is no direct server under the parent folder.

For example, the following is accepted:

[Production]

[Production\ABC]

Server_1

Server_2

[Production\ABC\DEF]

Server_3

Server_4

 

The following is NOT accepted:

[Production\ABC]

Server_1

Server_2

 

The reason it is not accepted is because there is no parent folder [Production] in the configuration file. You should put [Production] before [Production\ABC], i.e.

[Production]

[Production\ABC]

....

....

 

Thanks,

Jeff_yao

 

 

 

 


Wednesday, July 30, 2014 - 9:07:40 AM - PC Back To Top

 

Hi Jeff, thank you very much for this valubale tip. Here I am also facing the similar issue by George. I am using SS2008 R2. Its working perfect with parent folder and if you speciofy the sub folders its throwing similar errors specified by George. Could you please help me to get rid of this issue. 


Monday, July 21, 2014 - 1:30:30 PM - jeff_yao Back To Top

Hi George,

I am "glad" you encounter this issue too, as I believe this is indeed a bug which I logged it myself. This happens when sql server 2012 and 2014 are installed side by side. 

I have a pretty detailed blog about its root cause. Please see here.

http://www.sqlservercentral.com/blogs/jeffrey_yao/2014/06/07/bug-in-sql-server-2014-sqlps-for-registered-serversgroups/

 

Thanks,

Jeff_yao


Sunday, July 20, 2014 - 4:02:52 PM - George Back To Top

I have Sql Server 2012 Express + Management Studio installed, plus 2014 Management Studio (standalone).  When I ran this from powershell within Sql Server 2014 Management Studio; a few things happened:

 

1.  The "[Production/A]" lines had the 'production' part omitted in SQL 2014; it just created a folder based on the sub folder name (in this case "A")

2.  In some instances, the directories were created, but the servers were not created at all.

3. The following error occured when running the powershell script:

Set-Location : Cannot find path 'SQLServer:\SqlRegistration\Database Engine Server Group\PRODUCTION' because it does not exist. At C:\temp\Register_SQL_Servers.ps1:59 char:12 + Set-Location -LiteralPath "SQLServer:\SqlRegistration\$($choice)\$($g ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~ + CategoryInfo : ObjectNotFound: (SQLServer:\SqlR...roup\PRODUCTI ON:String) [Set-Location], ItemNotFoundException + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLo cationCommand New-Item : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Database Engine Server Group\PRODUCTION\A'. At C:\temp\Register_SQL_Servers.ps1:66 char:9 + New-Item -Name $(encode-sqlname $g.value) -path "sqlserver:\SQLRegistrat ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~ + CategoryInfo : InvalidData: (SQLSERVER:\SQLR...ODUCTION\A: SqlPath) [New-Item], GenericProviderException + FullyQualifiedErrorId : InvalidPath,Microsoft.PowerShell.Commands.NewIte mCommand 
Incidentally, if I run it in SSMS 2014; the folders show up when I open SSMS 2012; but they don't show in SSMS 2014.

Friday, July 18, 2014 - 2:32:43 PM - jeff_yao Back To Top

Hi Wei,

I used your config file

[BACKUP]
BACKUP-SVR\BKUPEXEC

[xxxx]
xxxx-SVR\xxxxx
xxxx-SVR\SQLEXPRESS

[xxxxx]
xxx-SQL-SVR
xxx-SQL-SVR\SALTO

[PROD]
XX-DEV
XX-PROD
XX-SVR
XX-SQL-SVR
XX-SQL-SVR\INST01
XX-SQL-SVR\INST02
XXX-PRO

(note: I only changed your first [xxxxx] to [xxxx], i.e. 4 'x' instead of 5 'x because your 2nd [xxxxx] has 5 'x' already)

and then I run the PS script, and I can create the registered servers in both sql2k8 R2 and sql 2012 without any problems.

Thanks,

Jeff_yao


Thursday, July 17, 2014 - 7:34:02 PM - WEI Back To Top

Thanks Jeff,

You are right, there was blank space in the config. file.

After clearing the blank space, I re-run and refresh CMS, all good.

However, I still getting the same error message but working.

Below is partial of the config file:

[BACKUP]
BACKUP-SVR\BKUPEXEC

[xxxxx]
xxxx-SVR\xxxxx
xxxx-SVR\SQLEXPRESS

[xxxxx]
xxx-SQL-SVR
xxx-SQL-SVR\SALTO

[PROD]
XX-DEV
XX-PROD
XX-SVR
XX-SQL-SVR
XX-SQL-SVR\INST01
XX-SQL-SVR\INST02
XXX-PRO

...

 


Thursday, July 17, 2014 - 1:17:37 PM - jeff_yao Back To Top

Hi Wei,

From the error msg,  Item ' ' already exists, It seems you have a blank space some where in your configuration file, I'd recommend you to do two things:

1. Clean up your registered servers in SSMS

2. Put one or two items only in your configuration file and test it.

If there is still problem, please update here and I'll contact you directly to solve your issue.

 

Thanks,

Jeff_yao



Wednesday, July 16, 2014 - 9:52:02 PM - WEI Back To Top

Hi Jeff,

got the below error, can you help?

PS SQLSERVER:\SQL\SQL05-TEST\DEFAULT> cd c:\temp
PS C:\temp> ./register_svr.ps1
New-Item : SQL Server PowerShell provider error: Item ' ' already exists.
At C:\temp\register_svr.ps1:59 char:17
+         New-Item <<<<  -Name $(encode-sqlname $g.value) -path "sqlserver:\SQL
Registration\$($choice)\$($g.parentfolder)" -ItemType $g.type -Value ("Server=$
regsrv ; integrated security=true");
    + CategoryInfo          : OperationStopped: (SQLSERVER:\SQLR...L05-TEST\TE
   ST\ :SqlPath) [New-Item], GenericProviderException
    + FullyQualifiedErrorId : ItemAlreadyExists,Microsoft.PowerShell.Commands.
   NewItemCommand


Wednesday, July 16, 2014 - 12:45:32 PM - jeff_yao Back To Top

@Mark, thanks for your question / suggestion.

So first to your question: Yes, you need to maintain a text file (or spreadsheet) AND make changes (manual or automatic is another topic) when servers get added/deleted before running the script.

As for automation of the text file, it is actually not complex in automation itself, but "complex" in your business practice on how to record the changes when a server is added/deleted/renamed etc. 

IMHO, this tip is more for individual DBA according to his/her own taste. For example, I may choose a group name [HR] for a bunch of servers I manage, while my colleage may prefer [HumanResource] as his preferred group name.

There can be quite a few revised ways to use the tip:

1st scenario: You have a team of several DBAs, and if you all agree to the naming conentions (like Group names, Group structures, Group members), you can create the configuration text file in a shared folder, and in the PS script, change to this shared config file before running the script. This way, you only maintain one file, and then share all changes by whole team.

2nd scenario: assuming you maintain all your server inventory in tables, then depending on your data modelling, you can write your own customized solution to dump the data out to a configuration file, you can do it via SSIS package, PowerShell script or anything of your choice. 

Thanks,

jeff_yao

 


 

 

 


Wednesday, July 16, 2014 - 10:55:09 AM - Mark Back To Top

Hi Jeff,

Thanks for the solution, one question before I test it in my environment - this assumes that we maintain a text file/spreadsheet containing the list of SQL servers we manage correct? If servers get added/deleted, it becomes imperative that the text file/spreadsheet be up to date MANUALLY before running the powershell script, correct?

I think you see where i'm getting :) (a suggestion for the next version would be to automatically generate a list of SQL servers in your environment (automate that!), without manual maintenance - but hey, just a simple suggestion)

Thanks again!


Friday, July 11, 2014 - 12:51:19 PM - jeff_yao Back To Top

Glad it works for you, Scott, and appreciate your update/feedback.


Friday, July 11, 2014 - 1:19:58 AM - Scott_Davis Back To Top
Jeff, Thanks. Your solution solved the problem. I'm modifying the script to pull this info from a View with folder hierarchies pre-defined

Thursday, July 10, 2014 - 1:44:32 PM - jeff_yao Back To Top

Hi @Scott Davis, thanks for reading my article. 

From the error msg  Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\XXCLLEVEL1\XXCLLEVEL2'

I guess, you did not REGISTER a  "Central Management Server" first.

From SSMS (I am using Sql server 2012 as an example), click menu View -> Registered Servers, right click "Central Management Servers", and click "Register Central Management Server..", let's say your CMS name is CMS_Svr1

Then you need to edit the PS script line as follows:

[string]$choice =  'Central Management Server Group\CMS_Svr1';

 
 
In your error msg, there is no central server in the path (I assume XXCLLEVEL1 is not your CMS server name).
 
I just tested the script in my SQL SERVER 2012 envrionment, it works as expected.
 
Please let me know if you still have issues.
 
Thanks,
Jeff_yao

 


Thursday, July 10, 2014 - 4:12:30 AM - Scott Davis Back To Top

Thanks for this code.

 

I am having trouble with running this when trying to use it to create registrations for a central management server

regardless of whether I update the path or use the script as is, I get an error in SQL2012 suggesting invalid path

** I have sanitized this **

New-Item : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\XXCLLEVEL1\XXCLLEVEL2'.At D:\scripts\populatecms\CMS_SvrReg_Grouping.ps1:59 char:17
+         New-Item <<<<  -Name $(encode-sqlname $g.value) -path "sqlserver:\SQLRegistration\$($choice)\$($g.parentfolder)" -ItemType $g.type -Value ("Server=$regsrv ; integrated security=true");    + CategoryInfo          : InvalidData: (SQLSERVER:\SQLR...0\XXSERVERNAME:
   SqlPath) [New-Item], GenericProviderException    + FullyQualifiedErrorId : InvalidPath,Microsoft.PowerShell.Commands.NewItemCommand

I have edited the ps1 to reflect the CMS path


[string]$choice = 'Central Management Server Group' # 'Central Management Server Group\SQLOPS';
$srv = @();

 can you shed some light?  

 

 


Wednesday, July 02, 2014 - 7:58:34 PM - jeff_yao Back To Top

Thanks Erik for your comment.

I think registered server in SSMS cannot take property such as applicationIntent=Readonly in its connection string. In SSMS\Registered Servers window, if you right-click any registered server and select "Properties", you can see all the configurable items a registered server can take, such as Windows login or regular SQL Login / password, and the database to be connected to (by default) etc.

In my test environment, I try to add ApplicationIntent=Readonly in the -value () clause, the registered server can be created, but if I open a new query window connecting to the new registered server, and then run select @@servername, it still goes to the primary node of HAG. (in the -value connectinon string, I use HAG group listener in the Server-"" not the primary node name). So this seem to prove, applicationintent value is ignore. 


Wednesday, July 02, 2014 - 8:27:33 AM - Erik Back To Top

Thanks for the great script, worked great!  I have one question though that has prevented us from using registered servers in our environment.  Is there a way to include optional parameters for the connection?  We have AAG and in order to connect to the read only instances you have to include applicationIntent = ReadOnly or you get errors.

Thanks,

Erik


Tuesday, July 01, 2014 - 4:17:59 PM - jeff_yao Back To Top

Thanks Hiren and heslous for yur comments. for

I'd also like to thank Greg Robidoux for reviewing and improving this tip.

To heslour, if you want to register a server using SQL account instead of a Windows one, you can simply modify the 2nd bottom line (not counting the "}"), where you can replace 

integrated security = true

with

user id=<sql_login_acct>; password='password'

 

for example, say I am using sa as the sql account, I will change

-Value ("Server=$regsrv ; integrated security=true");
to
-Value ("Server=$regsrv ; user id=sa; password='sa_password' ");
 
However because I am doing a batch registration, and if you need to use sql login account, it means, all the registered servers need to have the same sql login account with the same password. 

Tuesday, July 01, 2014 - 9:59:57 AM - heslous Back To Top

Great  !!This is a pretty fast method

But how do you handle the fact to get name registered using a SQL account ??  vs a Windows one


Tuesday, July 01, 2014 - 8:46:28 AM - Hiren Patel Back To Top

Awesome!! script, i was able to get my server register in no time. Thank you!!


Learn more about SQL Server tools