How to Populate Central Management Server Registration from a List

central-management-serversql server

I need to delete users from all the instances in my domain.

In order to do that, I want to create a CMS group and execute queries on it.
Problem is I have more than 150 servers to add. How can I bulk add servers to the group and not do it manually?

Best Answer

I would probably recommend Powershell for this task, but I did find a post that allowed me to automatically generate CMS entries and it worked for me - Using T-SQL to populate a Central Management Server

Excerpting from that post:

Create an instance table with some information about the instances

CREATE TABLE Instance (Instance varchar(255), SQLVersion varchar(10), 
    SDLC varchar(20));

INSERT INTO Instance VALUES
    ('(local)\sql2014cs','2014','Production'),
    ('(local)\sql2014ci','2014','Test'),
    ('(local)\sql2012','2012','Development'),
    ('(local)\SQLEXPRESS2012','2012','Production'),
    ('(local)\sql2008R2','2008 R2','Development');

Create CMS grouping

-- Version category
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal] 
    VALUES ('Version','Instance Version',0,1,0);

-- Version subcategories
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal] 
SELECT DISTINCT Instance.SQLVersion, Instance.SQLVersion, 0,
    Parent.server_group_id, 0
FROM Instance
CROSS JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
WHERE Parent.name = 'Version';

-- SDLC category
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal] 
    VALUES ('SDLC','Instance SDLC level',0,1,0);

-- SDLC subcategories
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal] 
SELECT DISTINCT Instance.SDLC, Instance.SDLC, 0,
    Parent.server_group_id, 0
FROM Instance
CROSS JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
WHERE Parent.name = 'SDLC';
GO

Add the actual instances

-- Instances broken out by Version
INSERT INTO msdb.dbo.[sysmanagement_shared_registered_servers_internal] 
SELECT Parent.server_group_id, Instance.Instance, Instance.Instance,
    Instance.Instance + ' - ' + Instance.SDLC, 0
FROM Instance
JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
    ON Instance.SQLVersion = Parent.name

-- Instances broken out by SDLC
INSERT INTO msdb.dbo.[sysmanagement_shared_registered_servers_internal] 
SELECT Parent.server_group_id, Instance.Instance, Instance.Instance,
    Instance.Instance + ' - ' + Instance.SQLVersion, 0
FROM Instance
JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
    ON Instance.SDLC = Parent.name

enter image description here

Related Question