Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate Your Central Management Server Registrations Expand / Collapse
Author
Message
Posted Monday, August 18, 2014 12:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 968, Visits: 973
Comments posted to this topic are about the item Automate Your Central Management Server Registrations
Post #1604272
Posted Monday, August 18, 2014 1:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 10, 2014 8:27 AM
Points: 21, Visits: 453
Good article, CMS is a very useful tool. Administration can be even simpler when you see that there are only actually two tables that define whole of CMS and you can insert\update\delete to these tables directly. You can also be a bit tricksy and put a value in group description that you can use as a LIKE with your standard server naming description to put similar servers into their own groups e.g. if all Europe production servers have PRODEU in the name, create a 'Europe - Prod' group with '%PRODEU%' in the group description column, running query below puts them into this group and you can run this at a later date to add any new ones that appear in your inventory system.

insert into msdb.dbo.sysmanagement_shared_registered_servers_internal 
(server_group_id, name, server_name, description, server_type)
select
g.server_group_id
, upper([ServerName] + case when [InstanceName] <> 'MSSQLSERVER' then '\' + [InstanceName] else '' end)
, upper([ServerName] + case when [InstanceName] <> 'MSSQLSERVER' then '\' + [InstanceName] else '' end)
, ''
, 0
from
YOUR_INVENTORY_SYSTEM s
join
msdb.dbo.sysmanagement_shared_server_groups_internal g
on
upper([ServerName]) like g.description
left join
msdb.dbo.sysmanagement_shared_registered_servers_internal i
on
i.server_name = s.[ServerName] + case when s.[InstanceName] <> 'MSSQLSERVER' then '\' + s.[InstanceName] else '' end
and g.server_group_id = i.server_group_id
where
i.server_name is null


It is also worth noting that sysmanagement_shared_registered_servers_internal name column is purely descriptive so you might want to put a more user friendly name (or the DB name if servers only host one DB) so your merged results show this name.
Post #1604300
Posted Monday, August 18, 2014 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:39 PM
Points: 128, Visits: 450
What are the current limitations / gotchas around CMS?
Back in 2008, there was some guidance from Microsoft that CMS shouldn't group > 200 servers together. Does anyone know if this has increased in 2k12 or 2k14?
Having said this, I'm suddenly unable to find any supporting documentation of this limitation - looking deeper.



Post #1604510
Posted Monday, August 18, 2014 9:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 968, Visits: 973
CDA (8/18/2014)
What are the current limitations / gotchas around CMS?
Back in 2008, there was some guidance from Microsoft that CMS shouldn't group > 200 servers together. Does anyone know if this has increased in 2k12 or 2k14?
Having said this, I'm suddenly unable to find any supporting documentation of this limitation - looking deeper.


I'm not personally aware of any limitations. We currently have 800+ instances across the various groups that I have defined. I regularly open up new queries against 350-400 at a time. Aside from a few that don't successfully connect, it has been pretty stable for me.

I'm also using SQL 2012 Enterprise for my CMS server, if that matters.
Post #1604528
Posted Monday, August 18, 2014 9:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 968, Visits: 973
liam.gavin - good info to be aware of. I opted to use the stored procedures after tracing what the GUI does. In the situation I'm in, we have a separate inventory tool that we use to map systems to applications, but i agree this could be taken to another level by adding in the descriptions. Take care!
Post #1604530
Posted Monday, August 18, 2014 12:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:25 PM
Points: 229, Visits: 719
Nice to use stored procedure to add registered servers.

But if you want to use PowerShell, you can check my article at: http://www.mssqltips.com/sqlservertip/3252/automate-registering-and-maintaining-servers-in-sql-server-management-studio-ssms/
It can be a good complementary reading to the pure t-sql way described in this article.

It will not only handle "Central Management Servers", it can also handle "Local Server Groups", however, I just use configure file for simplicity reason.

Kind regards,
Jeff_yao




Post #1604636
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse