Using T-SQL to populate a Central Management Server6
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
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.
Category: Central Management Server, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL | Tags: Central Management Server, CMS, code language, language sql, microsoft sql server, sql statements, SSMS, T-SQL
6 thoughts on “Using T-SQL to populate a Central Management Server”
Leave a Reply to Kenneth Fisher Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Where is the Instance table residing (CREATE TABLE Instance)? Is it in msdb or another database?
It doesn’t really matter. We keep ours in a database called DBA but it could really go anywhere.
[…] 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 […]
[…] 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 […]
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: