Script to Generate Registered Servers

I've always been a fan of Registered Servers in SSMS.  However, a few years ago I took a job with a large company that had several hundred servers distributed across DEV, Test and Prod environments.  The existing DBA's had made an attempt to organize the servers in a Central Management Server but it quickly became out of date as servers came and went on almost a daily basis and maintenance quickly became a nightmare.

The script below utilizes metadata stored in a maintenance database of some sort.  You have to figure that out.  I've included a sample table and view.  The particular table I used stores the server name, instance name, and environment among other things.  Several cursors run consecutively to build the various parts of the XML script.  If you have a place where you store this kind of metadata about your SQL Servers, you should be able to modify the cursors (there are 4 of them) and variables. 

I've modified the script I posted back in 2013 and it currently works for SQL Server 2016.  It should still work for previous versions as well as I have not needed to touch the XML that is generated.

Here is the table, view, main script and instructions.

/****** Object:  Table [dbo].[SQL_Metadata_Table]    Script Date: 4/9/2017 10:40:19 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
Here's a sample metadata table and view design. The view must also be created to use the "Registered Servers"
script as written.  Obviously, you can make modifications to suit your own needs. The script as I've 
written it only requires an "Environment" and "Server_Instance_Name" column but you may want to track 
more data elements than this.j

To run the script, you must have created the table and view and entered some SQL Instance data.
The output from the script should be saved as a '.regsrvr' file.  You then 'import' the file into
Management Studio.  Right-click 'Local Server Groups', 'Tasks' and 'Import'.  Choose the file you
just saved and click 'OK'.  File should import successfully and your registered servers appear.

*/
CREATE TABLE [dbo].[SQL_Metadata_Table](
[Server_Name] [varchar](255) NOT NULL,  
[Instance_Name] [nvarchar](255) NULL,
[Server Desc] [nvarchar](255) NULL,
[Environment] [nvarchar](255) NULL, -- i.e. DEV, TEST, PROD
[Primary_DBA] [int] NULL,   -- Numeric value for each DBA i.e. Dave = 1, Tom = 2, Mary = 3
[Secondary_DBA] [int] NULL, -- Same as above
[Status] [smallint] NULL,   -- Active = 1
[Server_Comment] [varchar](255) NULL)

go

CREATE VIEW [V_SQL_Metadata_Table]
AS
SELECT [Environment],
  Server_Instance_Name = CASE 
        WHEN [Instance_Name] IS NULL THEN [Server_Name]
        ELSE [Server_Name] + '\' + [Instance_Name]
       END
FROM [dbo].[SQL_Metadata_Table];
go

-- Script follows

declare @Environment varchar (50), 
  @Server varchar (50),
  @Header varchar (8000),
  @MasterServerName varchar (50),
  @SubGrpLine1 varchar (8000),
  @SubGrpLine2 varchar (8000),
  @SubGrpLine3Header varchar (8000),
  @SubGrpLine3 varchar (8000),
  @SubGrpLine3Footer varchar (8000),
  @SubGrpLine4Header varchar (8000),
  @SubGrpLine4 varchar (8000),
  @SubGrpLine4Footer varchar (8000),
  @SubGrpLineFooter varchar (8000),
  @EntireLine varchar(8000),
  @Footer varchar (8000)
-- Set Master Server Name
select @MasterServerName = 'Insert your server/instance name here'
--
-- Generate XML for generic header
--
DECLARE ServerGroup_Cursor Cursor 
   FOR SELECT UPPER([Environment])
       FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
  -- Sample where clause     where Status = 1 
  group by [Environment]
  order by [Environment]

select @Header = '<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
  <identity>
    <name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
    <baseURI>http://documentcollection/</baseURI>
  </identity>
  <xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
      <document>
        <docinfo>
          <aliases>
            <alias>/system/schema/RegisteredServers</alias>
          </aliases>
          <sfc:version DomainVersion="1" />
        </docinfo>
        <data>
          <xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
            <xs:element name="ServerGroup">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name="RegisteredServer">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
              <instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:RegisteredServers>
                        <sfc:Collection>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</sml:Uri>
                          </sfc:Reference>
                        </sfc:Collection>
                      </RegisteredServers:RegisteredServers>
                      <RegisteredServers:ServerGroups>
                        <sfc:Collection>' 
print @Header
--
-- Generate XML to create 1st Section for SQL Server Groups
--
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
 select @SubGrpLine1 = '<sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</sml:Uri>
                          </sfc:Reference>'
    print @SubGrpLine1

 FETCH NEXT FROM ServerGroup_Cursor 
 INTO @Environment
END

CLOSE ServerGroup_Cursor 
DEALLOCATE ServerGroup_Cursor
select @SubGrpLineFooter = '</sfc:Collection>
                      </RegisteredServers:ServerGroups>
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
    <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</RegisteredServers:Name>
                      <RegisteredServers:Description type="string" />
                      <RegisteredServers:ServerName type="string">' + @MasterServerName + '</RegisteredServers:ServerName>
                      <RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
                      <RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                      <RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @MasterServerName + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
                      <RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
                    </RegisteredServers:RegisteredServer>
                  </data>
                </document>'
                
print @SubGrpLineFooter
--
-- Generate XML to create 2nd Section for SQL Server Groups
--
DECLARE ServerGroup_Cursor Cursor 
   FOR SELECT UPPER([Environment])
       FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
  -- Sample where clause     where Status = 1 
 group by [Environment]
 order by [Environment]

OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
 select @SubGrpLine2 = '<document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">' + @Environment + '</RegisteredServers:Name>
                      <RegisteredServers:Description type="string" />
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>' 
 print @SubGrpLine2

 FETCH NEXT FROM ServerGroup_Cursor 
 INTO @Environment
END
CLOSE ServerGroup_Cursor 
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML to create Section for SQL Server Servers within Groups
--
-- Section Header
select @SubGrpLine3Header = '<document>
              <docinfo>
                <aliases>
                </aliases>
                <sfc:version DomainVersion="1" />
              </docinfo>
              <data>
                <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                  <RegisteredServers:RegisteredServers>
                    <sfc:Collection>'
Print @SubGrpLine3Header
    
DECLARE ServerGroup_Cursor Cursor 
   FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
       FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
  -- Sample where clause     where Status = 1 
  order by [Environment], [Server_Instance_Name]

OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
 select @SubGrpLine3 = '<sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '/RegisteredServer/' + @Server + '</sml:Uri>
                          </sfc:Reference>'
    print @SubGrpLine3

 FETCH NEXT FROM ServerGroup_Cursor 
 INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor 
DEALLOCATE ServerGroup_Cursor
 select @SubGrpLine3Footer = '</sfc:Collection>
      </RegisteredServers:RegisteredServers>
                      </RegisteredServers:ServerGroup>
                  </data>
                </document>'
 print @SubGrpLine3Footer

--
-- Generate XML to create 2nd Section for SQL Server Servers within Groups
--
DECLARE ServerGroup_Cursor Cursor 
   FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
       FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
  -- Sample where clause     where Status = 1 
 order by [Environment], [Server_Instance_Name]

OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
 select @SubGrpLine4 = '<document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '/RegisteredServer/' + @Server + '</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">' + @Server + '</RegisteredServers:Name>
                      <RegisteredServers:Description type="string" />
                      <RegisteredServers:ServerName type="string">' + @Server + '</RegisteredServers:ServerName>
                      <RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
                      <RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                      <RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @Server + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
                      <RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
                    </RegisteredServers:RegisteredServer>
                  </data>
                </document>'
    print @SubGrpLine4

 FETCH NEXT FROM ServerGroup_Cursor 
 INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor 
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML for final footer
--
select @Footer = '     </instances>
            </RegisteredServers:bufferData>
          </xs:schema>
        </data>
      </document>
    </definitions>
  </xs:bufferSchema>
</model>'
print @Footer  
GO