如何从列表中填充中央管理服务器注册

我可能会推荐 Powershell 来完成这项任务，但我确实找到了一个帖子，它允许我自动生成 CMS 条目并且它对我有用 - Using T-SQL to populate a Central Management Server

摘自该帖子：

创建一个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');

创建 CMS 分组

-- 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

添加实际实例

-- 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