I wrote a script that creates a “Registered Server” group and then a “Server Registration” for each of the servers found in a text or csv file. This comes in handy when you want to create lots of registrations (groups and/or servers).
Basically, to use the script, you create a file (txt or csv) for each group you want to add in “Registered Servers”. The script can be run against a folder or a file, so if you want to create all groups at one time you can put all the files in one folder and the script will process each file in the folder. The name of the file is the name of the group you’ll create, i.e., if I wanted to create a group named “ConfigMgr SQL Servers” I’d create a file named “ConfigMgr SQL Servers.txt” or “ConfigMgr SQL Servers.csv”. Within this file I’d add a line for each server I want to add in the group, and then I could run the PowerShell script which would create the group and all the servers that were in the file.
In addition to the comments and examples in the script, let me try to explain the CSV file formats that are accepted by the script. The txt or csv file(s) must have at least a “SQL Server Name” in order for the script to work correctly. But, it can have additional properties as well as long as they follow this format: ServerName,DatabaseName,DisplayName,ConnectionString (or Connection String Options to add to the connection string). Perhaps the best way to do this is to show examples; here I created a csv file with some fake servers and named the file “Example Group For Blog.csv”:
Next, I’ll run the following command (notice that I’m using the “HasHeaderRow” flag because I do have a header row in the csv file):
& 'C:LocationWhereTheScriptIsStoredCreateSqlServerRegistrationsInSSMS.ps1' -Path "F:Example Group For Blog.csv" -HasHeaderRow
And, when I look in SSMS’s “Registered Servers” I see:
You can see that each of the servers listed in the csv file was created within the group. And, to show the properties from the UI for each of the registered servers to help make sense of how the properties were used:
DisplayName: “My Azure Datawarehouse”
DisplayName: “MySqlServer1”
DisplayName: “MySqlServer2”
DisplayName: “MySqlServer4”
DisplayName: “The New XYZ Server”
Does that help explain without words? Hopefully. The rest of the functionality should be fairly well documented in the script comments itself, so please make sure to read those.
The blog platform seems to strip out the help comments when I try to paste the code directly in this blog using ‘code’ blocks so here is a OneDrive link to the script. And, here’s a link to the OneDrive folder which contains the script as well as the example csv for reference.
If you find any issues or have some improvements please let me know (and share the updated script!) :).
Hi Benjamin,
It is very useful script. I found some issue when my server name is like ‘Hostname,Portnumber’ in the text file, script is registering the SQLInstance name but port is assigning to default database name. Can you please update the script to accept port numbers?
Below is my example text file with SQL Instancec:
Hostname1,
Hostname2\SQLInstance1
Hostname2\SQLInstance2,15001
Hostname3.abc.com\SQLInstance2,15001
LikeLike
That’s a good point! I hadn’t accounted for that. Let me take a look when I get a little free time (honestly, not getting a lot of that lately so it could be a couple weeks).
LikeLike
Okay, I took a look at this and while I don’t have the time to update the script for this exact scenario, you could do the following:
1. Replace “$Props[0]” with “$Props[0].Replace(‘|’,’,’)” everywhere you see it (there’s only 4 places it exists) in the script
2. When you need to use a port in the server instead of using a comma use a pipe.
If you do this the script will work the same way it does now but will replace the pipe used for the port with a comma as it should during run time and everything else will work as is.
So, your sample text file would look like this (notice the pipes separating the port from the server):
Hostname1
Hostname2\SQLInstance1
Hostname2\SQLInstance2|15001
Hostname3.abc.com\SQLInstance2|15001
That’s really the quickest solution you can do yourself. Can you give it a whirl?
LikeLike
It would be great if you post the updated script. Thank you very much!
LikeLike
Hi Benjamin,
When I use the provided script to register the SQL Instances, it’s not appearing in SSMS Registered servers. Whereas when I use the below query I am seeing the group that I have registered.
get-childitem ‘SQLSERVER:\sqlregistration\Database Engine Server Group’
When I use the below command I see the servers in the group
Set-Location “SQLServer:\SqlRegistration\Database Engine Server Group”
DIR GroupName
Why I am not able to see the Registered groups in Registered servers of the SSMS?. I have tried your script in couple of other servers where we have powershell 5.0 + and SQLServer module Installed.
LikeLike
Hi tries to use this script but I can not see anything. I am new with Powershell. Is there any special configuration?
LikeLike
Where is the script? I cannot find it. Thanks
LikeLike
The second to last paragraph has the link you’re looking for. Are you not able to access it? It is shared with everyone
LikeLike
Hi
While running:
.\CreateSqlServerRegistrationsInSSMS.ps1 “PP” -Path “D:\PS1\CreateSqlServerRegistrationsInSSMS.csv” -HasHeaderRow
I got an error :
The script ‘CreateSqlServerRegistrationsInSSMS.ps1’ cannot be run because the following modules that are specified by the “#requires” statements of the script are missing:
SqlServer.
Any Idea?
Thank you
LikeLike
Sounds like you need to install the SqlServer module. https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15#:~:text=%20There%20are%20two%20SQL%20Server%20PowerShell%20modules%3A,job%20steps%20using%20the%20PowerShell%20subsystem.%20More%20
LikeLike
Hello I did it, its working now, BUT, Its create the Local server group – I can’t see it in SSMS, As I try to run again – It says that this Local server group already exists. I can’t see it, Even after resetting the SSMS .
Thank you for your help Yossi Hergass
LikeLike
Can you send me a private message with the details via Twitter? (SqlBenjamin) Or by using the “Contact” page (maybe by going to “Home” first)?
LikeLike
Hi,
From powershell, I can see that the groups are created, but I cannot see it listed n SSMS??!
did i do something wrong?
LikeLike
Interesting…can you send me a private message and we’ll try to figure this out? Use the contact button or hit me up on Twitter…
LikeLike
Hello Benjamin,
I was looking for exact script to create .regsrvr file and to register more than 50 servers in SSMS. However, the link to the script is not working. Would you pl check and let me know. You can email both (script and csv) files. I really appreciate your help.
Regards.
Muhammad
LikeLike
Let me check the link and get back to you
LikeLike
Okay I’ve fixed the links so they should work. If not feel free to contact me (click the “Connect” button up at the top and send me a direct message and I’ll get back to you and help you out. Thanks!
LikeLike
Thanks Benjamin,
I have successfully downloaded the script and cvs files, and ran the script. However, i could not able to create .regsrvr file. I have also created a Non_Prod.txt (NotePad++) file with only server list and ran the script couple of times again, and i got following message. Please let me know if i need to run the script different way.
Again, i need to add more than 50 servers from txt (or cvs) file into .regsrvr format and import it in SSMS.
Note: I am running the script on my laptop in PowerShell.
Command:
& ‘.\CreateSqlServerRegistrationsInSSMS.ps1’ -Path “.\Non_Prod.txt”
Error Message:
WARNING: Server Group ‘Non_Prod’ Already Exists! Use the ‘OverwriteGroup’ switch to recreate this group or the ‘AddServersToExistingGroup’ switch to
add servers to the group. This file/group will be skipped!
I really appreciate your help.
LikeLike
Did you open SSMS and look in the registered servers pane? The script doesn’t create a .regsrvr file for importing, it actually does the creation/importing. That error message is because you ran it one time and it created the registered servers, when you run it again it throws this error because the group already exists. If you want to run it again you can add “-OverwriteGroup” or “AddServersToExistingGroup” to the end of your command and it will either delete the group and recreate it or just add the servers in the txt file to the group (depending on which flag you use). Does that make sense?
LikeLike
Yes:) i see the groups and the servers under these groups. However, as same as Mahmoud, i could not able to see these groups/servers in SSMS. I do see all the other groups/servers, which i have created manually. Pl let me know how did Mahmoud fixed the issue.
Regards.
LikeLike
I’m not sure…can you send me an email via the “Contact” button above? I’d like to get some info about your versions and help troubleshoot. If this is the second time I’m seeing someone with issues I want to see if I can figure it out and fix this for everyone.
LikeLike
Hi Muhammad,
It will very helpful if you can provide the script to me.
Thanks in advance
LikeLike
Sure, go ahead and send me an email via the “Contact” button at the top of the blog.
LikeLike