- 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 NULLI 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
Posts
