Central Registrered Servers

With SQL Server 2008 came the feature "Central Management Servers". This is poorly documented by Microsoft, but the naming of the database objects are somewhat meaningful. In the system database [msdb] there are two tables for storing the server registrations. Only the tables in msdb at the Central Management Server are used. The two tables are

• msdb.dbo.sysmanagement_shared_registered_servers_internal
• msdb.dbo.sysmanagement_shared_server_groups_internal

There is no use in accessing these two tables as we have these two views for the data

• msdb.dbo.sysmanagement_shared_registered_servers
• msdb.dbo.sysmanagement_shared_server_groups

The first view sysmanagement_shared_registered_servers shows the registered servers by server_id : int server_group_id : int name : sysname server_name : sysname description : nvarchar(2048) server_type : int

The second view sysmanagement_shared_server_groups shows the registered server groups and their hieracy by server_group_in : int NOT NULL name : sysname NOT NULL description : nvarchar(2048) NOT NULL server_type : int NOT NULL parent_id : int NULL is_system_object : bit NULL num_server_group_children : int NULL num_registered_server_children : int NULL

I found the view sysmanagement_shared_registered_servers a little technical, but this query gives me a quick glance at the central registered servers

SELECT [server_id] ,[server_groups].[name] AS 'server_group_name' ,[server_name] ,[shared_servers].[description] AS 'server_description' ,( SELECT [name] FROM msdb.dbo.sysmanagement_shared_server_groups AS [shared_groups] WHERE shared_groups.is_system_object = 1 AND shared_servers.server_type = shared_groups.server_type) AS 'server_type_name' FROM msdb.dbo.sysmanagement_shared_registered_servers AS [shared_servers] INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS [server_groups] ON shared_servers.server_group_id = server_groups.server_group_id;

It looks like the data are handled by 11 stored procedures. I have tried them a little, and you can see my notes and examples in Github > SQLAdmin > Central Management Server.

• msdb.dbo.sp_sysmanagement_verify_shared_server_type @server_type : int
• msdb.dbo.sp_sysmanagement_add_shared_server_group @name : sysname @description nvarchar(2048) = N'' @parent_id : int @server_type : int @server_group_id : int OUTPUT
• msdb.dbo.sp_sysmanagement_add_shared_registered_server @name : sysname @server_group_id : int @server_name : sysname @description : nvarchar(2048) = N'' @server_type : int @server_id : int OUTPUT
• msdb.dbo.sp_sysmanagement_delete_shared_server_group @server_group_id : int
• msdb.dbo.sp_sysmanagement_delete_shared_registered_server @server_id : int
• msdb.dbo.sp_sysmanagement_move_shared_server_group @server_group_id : int @new_parent_id : int
• msdb.dbo.sp_sysmanagement_move_shared_registered_server @server_id : int @new_parent_id : int
• msdb.dbo.sp_sysmanagement_update_shared_server_group @server_group_id : int @description : nvarchar(2048) = NULL
• msdb.dbo.sp_sysmanagement_update_shared_registered_server @server_id : int @server_name : sysname = NULL @description : nvarchar(2048) = NULL
• msdb.dbo.sp_sysmanagement_rename_shared_server_group @server_group_id : int @new_name : sysname
• msdb.dbo.sp_sysmanagement_rename_shared_registered_server @server_id : int @new_name : sysname

Also it loks like Central Management Servers can be handled through SQL Server Management Objects (SMO). I found seven classes in the namespace Microsoft.SqlServer.Management.Smo.RegisteredServers by the .NET component Microsoft.SqlServer.SmoExtended implemented in the assembly Microsoft.SqlServer.SmoExtended.dll. The classes are

• RegisteredServer
• RegisteredServerCollection
• RegSvrCollectionBase
• RegSvrSmoObject
• ServerGroup
• ServerGroupBase
• ServerGroupCollection

Looking at the classes using Visual Studio Object Browser didn't give me that much - only I noticed that we once again have to fight with the .NET component Microsoft.SqlServer.SmoExtended :-(

About security suddenly Books Online is useful:

How to: Create a Central Management Server and Server Group

It looks like you don't have to be sysadmin to be able to use Central Management Servers. :-)

The above is the result of a quick browse in the system objects. When I have something usefull, it will surface here...

History

2021-11-04  Link to Github notes and examples added.

2009-06-16  Post created