As im working on automating my CMS with the results from MAPT (more on that to come) i wanted to check it was possible to add to a CMS server via TSQL. I started off by just running a trace while using the GUI which gave me the names of the Stored Procedures that are used to create a New Group and add a New Server. with a bit more digging around and finding Phil Factors excellent blog (https://www.simple-talk.com/sql/sql-tools/registered-servers-and–central-management-server-stores/), i got the rest of the information i needed. Blow is my new “Cheat Sheet for CMS” which covers all the basics for querying and working with CMS.

/*

Central Management Server Statements

-- View
	-- View all Instances and there Groups via TSQL
-- Groups
	-- View all Groups via TSQL
	-- Add a Group via TSQL
	-- Add a Group via Stored Procedure
-- Servers
	-- View all Servers Via TSQL
	-- Add a Server via TSQL
	-- Add a Server via Stored Procedure

-- Links
https://www.simple-talk.com/sql/sql-tools/registered-servers-and--central-management-server-stores/

*/

/*
-------------------------------------------------------------------------
View
-------------------------------------------------------------------------
*/

-- View all Instances and there Groups via TSQL
SELECT  g.name AS [Group] ,
        s.name AS [Server Name] ,
        s.server_name AS [Server] ,
        s.description AS [Description]
FROM    msdb.dbo.sysmanagement_shared_server_groups_internal AS g
        LEFT JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal AS s ON g.server_group_id = s.server_group_id
WHERE   g.is_system_object = 0
ORDER BY [Group] ,
        [Server Name];
GO

/*
-------------------------------------------------------------------------
Groups
-------------------------------------------------------------------------
*/
-- View all Groups via TSQL
SELECT  *
FROM    msdb.dbo.sysmanagement_shared_server_groups_internal;

-- Add a Group via TSQL
IF NOT EXISTS ( SELECT  *
                FROM    msdb.dbo.sysmanagement_shared_server_groups_internal
                WHERE   name LIKE 'Production' )
    INSERT  INTO msdb.dbo.sysmanagement_shared_server_groups_internal
            ( name ,
              Description ,
              Server_Type ,
              parent_ID ,
              is_system_object
            )
    VALUES  ( 'Production' ,
              'Production Group - No chances can be completed with a complementing RFC' ,
              0 ,
              1 ,
              0
            );

-- Add a Group via Stored Procedure
DECLARE @server_group_id INT
EXEC msdb.dbo.sp_sysmanagement_add_shared_server_group
	@parent_id = 1,
    @name = N'Production',
	@description = N'Production Group - No chances can be completed with a complementing RFC',
    @server_type = 0,
	@server_group_id = @server_group_id;

/*
-------------------------------------------------------------------------
Servers
-------------------------------------------------------------------------
*/
-- View all Servers via TSQL
SELECT  *
FROM    msdb.dbo.sysmanagement_shared_registered_servers_internal;

-- Add a Group via TSQL
-- Check Server_Group_Id in msdb.dbo.sysmanagement_shared_server_groups_internal
-- For me the Development Group = 7
INSERT  INTO msdb.dbo.sysmanagement_shared_registered_servers_internal
        ( Server_Group_ID ,
          name ,
          Server_name ,
          description ,
          Server_Type
        )
VALUES  ( 7 ,
          'DEV-SQL01' ,
          'DEV-SQL01' ,
          'Default Development Server for Application X' ,
          0
        );

-- Add a Group via Stored Procedure
DECLARE @server_id INT
EXEC msdb.dbo.sp_sysmanagement_add_shared_registered_server
	@server_group_id = 7,
    @name = N'DEV-SQL01',
	@server_name = N'DEV-SQL01',
    @description = N'Default Development Server for Application X',
	@server_type = 0,
	@server_id = @server_id;
Advertisements