SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate Your Central Management Server Registrations


Automate Your Central Management Server Registrations

Author
Message
S. Kusen
S. Kusen
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1117
Comments posted to this topic are about the item Automate Your Central Management Server Registrations
liam.gavin
liam.gavin
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 515
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.
CDA
CDA
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 536
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.



S. Kusen
S. Kusen
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1117
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.
S. Kusen
S. Kusen
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1117
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!
jeffrey yao
jeffrey yao
Mr or Mrs. 500
Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)

Group: General Forum Members
Points: 533 Visits: 882
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



i1888
i1888
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 295
Hello, thanks very much for your article,

I am wondering how do you get over crossing domain? as we have different domains and I would like to manage SQL Servers in one place.
S. Kusen
S. Kusen
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1592 Visits: 1117
i1888 (6/9/2015)
Hello, thanks very much for your article,

I am wondering how do you get over crossing domain? as we have different domains and I would like to manage SQL Servers in one place.


Without trusts set up between your windows account and the other domains, I don't know that it is possible since SQL authentication is not possible. See this link for reference.

Item 3 on that page:
In the New Server Registration dialog box, select the instance of SQL Server that you want to become the central management server from the drop-down list of servers. You must use Windows Authentication for the central management server.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search