Managing SQL servers for a multitude of customers with different environments (production, test, development and staging) requires a lot of administration. In our case, we work with an Access frontend on a SQL server database, in which we register instances, contacts, contract types, working hours, et cetera.

Not being able to use a Central Management Server (it doesn’t support SQL logins), we rely on Local Server Groups for our Registered Servers. And with all the changes in the customers’ environments, it can become quite cumbersome to keep your registered servers up to date, especially when working with a team of DBA’s. I therefore developed a SQL script that generates a SQL PowerShell script which automates this process.

The code below clears the Local Servers Group completely (be aware of thsi), then generates Registered Servers grouped by Customer and Environment and sorted on Customer, Environment and Instance name. 

SET NOCOUNT ON;
CREATE TABLE #connections
(
    CustomerName VARCHAR(100),
    Environment  VARCHAR(20),
    InstanceName  VARCHAR(100),    
    ConnectionString VARCHAR(255)  -- See demo code for examples
)
DECLARE @Customer VARCHAR(100),
        @Environment VARCHAR(20),
        @OldEnvironment VARCHAR(20),
        @SortOrder INT,
        @InstanceName VARCHAR(100),
        @ConnectionString VARCHAR(255)
-- INSERT DEMO CODE -----------------------------------------------------------------------------------------
-- This inserts some demo data in de #connections table.
-- An option would be to insert data based an a CMDB or some instance registration (spreadsheet, DB)
INSERT INTO #connections
VALUES
('Customer01', 'Production',    'MSSQLP01',     '"Server=MSSQLP01; User Id=RRunner01; Password=MeepMeep"'),
          ('Customer01', 'Production',    'MSSQLP02',     '"Server=MSSQLP02; User Id=RRunner01; Password=MeepMeep"'),
          ('Customer01', 'Test',          'MSSQLT01',     '"Server=MSSQLT01; User Id=RRunner01; Password=MeepMeep"'),
          ('Customer01', 'Test',          'MSSQLT02',     '"Server=MSSQLT02; User Id=RRunner01; Password=MeepMeep"'),
          ('Customer01', 'Development',   'MSSQLD01',     '"Server=MSSQLD01; Integrated security=true"'),
          ('Customer01', 'Development',   'MSSQLD02',     '"Server=MSSQLD01; Integrated security=true"'),
          ('Customer02', 'Production',    'CUS02SQLP01',  '"Server=CUS02SQLP01; User Id=RRunner01; Password=MeepMeep"'),
          ('Customer02', 'Production',    'CUS02SQLO02',  '"Server=CUS02SQLP02; User Id=RRunner01; Password=MeepMeep"'),
          ('Customer02', 'Test',          'CUS02SQLT01',  '"Server=CUS02SQLT01; User Id=RRunner01; Password=MeepMeep"'),
          ('Customer02', 'Test',          'CUS02SQLT02',  '"Server=CUS02SQLT02; User Id=RRunner01; Password=MeepMeep"');
-- END DEMO CODE --------------------------------------------------------------------------------------------
WITH PS_Tree AS
(
    SELECT  DISTINCT 1      AS SortOrder,
            CustomerName    AS Customer,
            ''              AS Environment,
            ''              AS RegServer,
            ''              AS ConnectionString
    FROM    #connections
    UNION ALL
    SELECT  DISTINCT 2,
            CustomerName,
            Environment,
            '',
            ''
    FROM    #connections
    UNION ALL
    SELECT  3,             
            CustomerName,
            Environment,
            InstanceName,
            ConnectionString
    FROM    #connections
)
SELECT  CASE SortOrder
        WHEN 1 THEN
               CASE WHEN ROW_NUMBER() OVER (ORDER BY Customer, Environment, SortOrder, RegServer) = 1
                    THEN    'Set-Location "SQLServer:\SqlRegistration\Database Engine Server Group"' + CHAR(13) + CHAR(10) +
                                'dir -Recurse | Remove-Item -force; #clean up everything' + CHAR(13) + CHAR(10)
                    ELSE    ''
               END +
                    'Set-Location "SQLServer:\SqlRegistration\Database Engine Server Group"' + CHAR(13) + CHAR(10) +
                    'new-item "' + Customer + '"' + CHAR(13) + CHAR(10)
        WHEN 2 THEN
               'CD "SQLSERVER:\sqlregistration\Database Engine Server Group\' + Customer + '\"' + CHAR(13) + CHAR(10) +
               'new-item "' + Environment + '"' + CHAR(13) + CHAR(10) +
               'CD "SQLSERVER:\sqlregistration\Database Engine Server Group\' + Customer + '\' + Environment + '\"'
        WHEN 3 THEN
               'New-Item $(Encode-Sqlname "'+ RegServer + '") -itemtype registration -Value ' + ConnectionString
        END
FROM    PS_Tree
ORDER BY Customer, Environment, SortOrder, RegServer
DROP table #connections

Here is an example of the generated Powershell code:

Set-Location "SQLServer:\SqlRegistration\Database Engine Server Group"
dir -Recurse | Remove-Item -force; #clean up everything
Set-Location "SQLServer:\SqlRegistration\Database Engine Server Group"
new-item "Customer01"
CD "SQLSERVER:\sqlregistration\Database Engine Server Group\Customer01\"
new-item "Development"
CD "SQLSERVER:\sqlregistration\Database Engine Server Group\Customer01\Development\"
New-Item $(Encode-Sqlname "MSSQLD01") -itemtype registration -Value "Server=MSSQLD01; Integrated security=true"
New-Item $(Encode-Sqlname "MSSQLD02") -itemtype registration -Value "Server=MSSQLD01; Integrated security=true"
CD "SQLSERVER:\sqlregistration\Database Engine Server Group\Customer01\"
new-item "Production"
CD "SQLSERVER:\sqlregistration\Database Engine Server Group\Customer01\Production\"
New-Item $(Encode-Sqlname "MSSQLP01") -itemtype registration -Value "Server=MSSQLP01; User Id=RRunner01; Password=MeepMeep"
New-Item $(Encode-Sqlname "MSSQLP02") -itemtype registration -Value "Server=MSSQLP02; User Id=RRunner01; Password=MeepMeep"
CD "SQLSERVER:\sqlregistration\Database Engine Server Group\Customer01\"
new-item "Test"....

And now it’s a simple case of starting Powershell: right click on your server and select ‘Start Powershell:

Paste the code in the Powershell window and press Enter. After running, refresh the Database Engine in your Registered Servers pane, and all the instances are visible, grouped and sorted: