Creating Registered Servers in SSMS via PowerShell

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!) :).

Advertisements

4 thoughts on “Creating Registered Servers in SSMS via PowerShell

  1. 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

    Like

    1. 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).

      Like

    2. 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?

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s