If you are a SQL Server database administrator (DBA) who also has need to administer servers via Remote Desktop, this script will be of tremendous help to you at least once, and may be something you run regularly.

This script will query your SQL Server Central Management Server (not using it yet - check out Easily Manage your SQL Server with CMS and PBM Webcast) and generate the contents of a a config file for Microsoft's Remote Desktop Connection Manager.  All free tools or built into your SQL Servers.


Install Remote Desktop Connection Manager from https://www.microsoft.com/en-us/download/details.aspx?id=44989 - version 2.7

Have populated a SQL Server Central Management Server (CMS) with at least one group and server

To use, run the script against the instance holding your CMS data.  This will return an XML value.  Copy and Paste this value into a new text file with the extension *.rdg (for example MyCMSServers.rdg) In Remote Desktop Connection Manager, File. Open.


All unique host names are placed under a group called '_All Servers'.  the 'Smart Lists' dynamically filter based on the groups in your SQL CMS.  This is done by placing a comment in the RDCMan node for each server.  The script itself used some nuanced XML PATH.

The groups are not hierarchical in RDCMan.  There are probably some edge cases that could cause issues, such as hosts that belong to more than one group


--construct the group node of all unique host names 
DECLARE @groupNode xml 
SET @groupNode =  
    ( --group node 
    SELECT '_All Servers' AS "properties/name",  
        ( --server nodes (distinct host names) 
                WHEN CHARINDEX('\',srs.name) > 0 THEN SUBSTRING(srs.name,1,CHARINDEX('\',srs.name)-1) 
                ELSE srs.name  
            END AS "properties/name"'CMS Group: ' + ssg.name AS "properties/comment" 
        FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internalsrs 
        INNER JOIN msdb.dbo.[sysmanagement_shared_server_groups_internalssg  ON srs.server_group_id = ssg.server_group_id 
        FOR XML PATH('server') 
        , type) 
    FOR XML PATH('group') 
--construct smart groups for each cms group 
DECLARE @smartGroupNodes xml 
SET @smartGroupNodes = ( 
    SELECT ssg.name AS "properties/name"'All' AS "ruleGroup/@operator", 
        'Comment' AS "ruleGroup/rule/property"'Matches' AS "ruleGroup/rule/operator", 
        'CMS Group: ' + ssg.name AS "ruleGroup/rule/value" 
    FROM msdb.dbo.[sysmanagement_shared_server_groups_internalssg 
    WHERE ssg.server_type = 0 
    FOR XML PATH('smartGroup') 
SELECT '2.7' AS "@programVersion"'3' AS "@schemaVersion", 
    (--file node 
    SELECT 'CMS' AS "properties/name", 
    FOR XML PATH ('file') 
    ,typeFOR XML PATH('RDCMan'