How to populate central management server registration from a list

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?

1 Answer
1

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

instance

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


Thanks for contributing an answer to Database Administrators Stack Exchange!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.