We have been using a CMS (Central Management Server) at my office for the last few years. Even beyond the uses for managing multiple servers (PBM etc) we use it primarily as a shared list of registered servers. Given that our current list is a bit over 150 instances we have found it much easier of the years to have one list we share and maintain between us than trying to maintain our own separate lists.

Our company has been reorganizing and consolidating some of the technology groups over the last couple of years so our list has been changing and increasing quite a bit. In fact in the next few months we expect to be adding another 450+ instances to our shared responsibility. Trying to add that number of instances to a CMS would be time consuming at best. This lead to the question “Can we load the CMS auto-magically?”

Turns out, yes, we can! This lead to some cool ideas. Within CMS you can organize your instances by groupings. By creating multiple different types of groupings we can make searching for just the right instance much easier. This was something we would have had a hard time doing if we had to register each instance manually.

First, I should let you know that we have an “instance” table with a list of available instances and some information about them.

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');

My test table has just the instances on my local machine so there aren’t all that many and the SDLC information is completely made up, but it will work for an example.

In my example here I’m going to split the instances up by Version and by SDLC level. Division and support groups are potentially some other useful ideas.

First step is to create the groupings.

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

Now we add in 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

You can see that we now have the instances listed in a couple of different ways. When there are dozens or hundreds of instances grouping them like this can make finding the one you want considerably easier. Particularly when you aren’t all that familiar with their names. By adding them programmatically we saved a lot of time and energy not to mention avoiding potential mistakes. And of course if you have a single maintained list like mine (maybe one kept for auditing or monitoring purposes, or even another CMS) you could easily create a scheduled job to update your CMS on a regular basis.

Filed under: Central Management Server, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: Central Management Server, CMS, code language, language sql, microsoft sql server, sql statements, SSMS, T-SQL