Using T-SQL to populate a Central Management Server

6

November 17, 2014 by Kenneth Fisher

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

LoadCMS

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.

6 thoughts on “Using T-SQL to populate a Central Management Server

  1. David says:

    Where is the Instance table residing (CREATE TABLE Instance)? Is it in msdb or another database?

  2. […] I’ve used our instance table to write SSIS packages that loop through a list of instances, populate a CMS (Central Management Server) and generate an XML file for Idera’s Toolset to mention just a few […]

  3. […] to my CMS At work we use a Central Management Server to keep a shared list of all of our active instances. Obviously when I install a new version of SSMS I need to connect to […]

  4. Jeff says:

    Do you have the code for — Instances broken out by Version and — Instances broken out by SDLC swapped?

    • It’s just alphabetical so you could do something like this:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,682 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: