Automate Registering and Maintaining Servers in SSMS

By: Jeffrey Yao   |   Updated: 2014-07-01   |   Comments (38)   |   Related: 1 | 2 | 3 | 4 | > SQL Server Management Studio Configuration

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:

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:

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:

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

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

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

cd c:\temp
./register_svr.ps1

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

• Import and Export Registered SQL Servers To Other Machines
• Registering SQL Server instances for easier management
• SQL Server Multi Database Query with Registered Servers
• Using PowerShell to Register All SQL Instances Into Central Management Server

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips