2009-06-16

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 haven't tried them enough to put it in writing, but here are the procedures

  • 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_it : 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...