question

shaned505 avatar image
shaned505 asked

sqlregistration\central management server group empty

I have SQL Server 2012 SP1 with several servers registered under CMS and the CMS set to be the master. I can run queries across all of these server using SSMS. I can see these servers querying msdb. However when using powershell and cd'ing to the sqlregistration\central management server group I find it is empty. All the articles I have found on the interweb suggest that it should be populated (though the majority of these refer to 2008). Any ideas on this? I need to schedule a job to run on the cms and it appears powershell is the only way. Thanks. Shane
powershell
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What information tells you it can only be done via PowerShell?
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
From my own article '[Registered Servers and Central Management Server Stores][1]', I'd suggest Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers #now fetch the list of all our registered servers get-childitem 'SQLSERVER:\sqlregistration\Central Management Server Group\' -recurse You should see something being returned. If you want to get a unique sorted list of the servers that are in your central management Servers, try this... Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers #now fetch the list of all our registered servers get-childitem 'SQLSERVER:\sqlregistration\Central Management Server Group\' -recurse| where {$_.GetType() -notlike '*ServerGroup*'} | #exclude directories! % {$_.ServerName} | sort-object |get-unique [1]: https://www.simple-talk.com/sql/sql-tools/registered-servers-and--central-management-server-stores/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shaned505 avatar image
shaned505 answered
Hi Phil, I found your Powershell SMO: Just Write things once, but missed this. Unfortunately it doesn't work! Windows PowerShell Copyright (C) 2013 Microsoft Corporation. All rights reserved. PS C:\Users\sdavies> import-module sqlps -DisableNameChecking PS SQLSERVER:\> get-childitem 'SQLSERVER:\sqlregistration\Central Management Server Group\' -recurse PS SQLSERVER:\> Yet here is the listing FROM [msdb].[dbo]. [sysmanagement_shared_registered_servers_internal] server_id server_group_id name server_name description server_type 1 7 sim-sql-00 sim-sql-00 0 2 7 sim-5series-00 sim-5series-00 0 3 7 sje-sql-00 sje-sql-00 0 4 7 sje-5series-00 sje-5series-00 0 5 7 sgg-app-01 sgg-app-01 0 6 7 SCH-SQL-00 SCH-SQL-00 0 7 7 SCHG-SQL-01 SCHG-SQL-01 0 8 7 SCY-SQL-00 SCY-SQL-00 0 9 7 SIES-EXC-03 SIES-EXC-03 0 10 7 SIM-PUREFUND-01 SIM-PUREFUND-01 0 11 7 SIM-REPORTING-01 SIM-REPORTING-01 0 12 7 SIM-SQLELEMENTS-01 SIM-SQLELEMENTS-01 0 13 7 SIM-SUN-SQL-02 SIM-SUN-SQL-02 0 14 7 SJE-EFSQL-01 SJE-EFSQL-01 0 15 7 SJE-MI-SQL-01 SJE-MI-SQL-01 0 16 7 SJE-NAV1-SQL-01 SJE-NAV1-SQL-01 0 17 7 SJE-TRIDENT-01 SJE-TRIDENT-01 0 18 7 SLU-SQL-01 SLU-SQL-01 0 and FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] server_group_id name description server_type parent_id is_system_object 1 DatabaseEngineServerGroup Builtin group that contains the DatabaseEngine servers 0 NULL 1 2 AnalysisServicesServerGroup Builtin group that contains the AnalysisServices servers 1 NULL 1 3 ReportingServicesServerGroup Builtin group that contains the ReportingServices servers 2 NULL 1 4 IntegrationServicesServerGroup Builtin group that contains the IntegrationServices servers 3 NULL 1 5 SqlServerCompactEditionServerGroup Builtin group that contains the SqlServerCompactEdition servers 4 NULL 1 6 ALL ALL SERVERS 0 1 0 7 Production 0 6 0. If I open a new query against production I can run a script that runs on all the servers listed. Any ideas? Many thanks.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hmmm. Do you see your Database Engine server group when you execute this.... Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers #now fetch the list of all our registered servers get-childitem 'SQLSERVER:\sqlregistration\' -recurse
0 Likes 0 ·
shaned505 avatar image
shaned505 answered
Hi Phil, Your query returned: PS C:\Windows\system32> Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the re istered servers PS SQLSERVER:\> #now fetch the list of all our registered servers PS SQLSERVER:\> get-childitem 'SQLSERVER:\sqlregistration\' -recurse Directory: Microsoft.SqlServer.Management.PSProvider\SqlServer::SQLSERVER:\sqlregistration Mode Name ---- ---- d Central Management Server Group d Database Engine Server Group Directory: Microsoft.SqlServer.Management.PSProvider\SqlServer::SQLSERVER:\sqlregistration\Database Engine Server Group Mode Name ---- ---- - sim-sqlshane-01 PS SQLSERVER:\> Cheers. Shane
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shaned505 avatar image
shaned505 answered
Hi Phil, Panic over! I had been using SSMS on my machine to create the CMS. I deleted this and tried using another server, same result. Deleted the server group again. RDP'd to the server and using SSMA locally on the server created the server group and registered some servers. This time they appeared when querying via PS. A misunderstanding on my part. I thought the info was all held in the MSDB tables, obviously not! Thanks for all your help (and you invaluable articles). Cheers Shane
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.