2009-09-21

Central Registrered Servers and groups

Adding a new database group to a Central Management Server con be done by a T-SQL statement like this:
DECLARE @new_server_group_id AS int;
DECLARE @parent_server_group AS int;
SELECT @parent_server_group = server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups
WHERE [name]='DatabaseEngineServerGroup';
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_server_group
  @name = N'Development'
  ,@description = N'Development SQL Server database instances.
  ,@parent_id = @parent_server_group
  ,@server_type = 0
  ,@server_group_id = @new_server_group_id OUTPUT;


The result can be viewed through msdb.dbo.sysmanagement_shared_server_groups.

After the database group is created, some database instances can be registrered in the group by a T-SQL statement like this:
DECLARE @server_group AS int;
SELECT @server_group = server_group_id FROM msdb.dbo.sysmanagement_shared_server_groups WHERE [name]='Development';
DECLARE @new_server_id AS int;
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_registered_server
  @name = N'Frodo\SSDB01'
  ,@server_group_id = @server_group
  ,@server_name = N'Frodo.sqladmin.lan\SSDB01'
  ,@description = N'First database instance on Frodo.sqladmin.lan.'
  ,@server_type = 0
  ,@server_id = @new_server_id OUTPUT;
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_registered_server
  @name = N'Frodo\SSDB02'
  ,@server_group_id = @server_group
  ,@server_name = N'Frodo.sqladmin.lan\SSDB02'
  ,@description = N'Second database instance on Frodo.sqladmin.lan.'
  ,@server_type = 0
  ,@server_id = @new_server_id OUTPUT;


The result can be viewed through msdb.dbo.sysmanagement_shared_registered_servers.

There is a unique constraint on the name, but the procedure is not „intelligent“ enough to handle instert or update of a server registration.

When a group is deleted, the registrered servers in the group is also deleted
DECLARE @del_server_grp AS int;
SELECT @del_server_grp = server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups
WHERE [name] = N'Development';
EXECUTE msdb.dbo.sp_sysmanagement_delete_shared_server_group @server_group_id = @del_server_grp;


In this example the two previously registered servers are deleted from the Central Management Server - without warning or additional messages.

A automated population, like from System Center Operations Manager (SCOM) will need some coding of a wrapper to handle insert or update.
Also some work is needed for handling database mirror and log shipping sets.

PowerShell

Inspired by the blog entry &bdqou;Managing Registered servers with SQL Powershell“ by Arco Scheepen I looked into using the PowerShell SQL Server provider for accessing the Management Server registrations with Set-Location and New-Item on SQLSERVER:\sqlregistration\Database Engine Server Group.
More details on this later...

This was some quickies - comments are most welcome.

History

2009-09-21 Initial blog entry
2016-10-25 Section on PowerShell added

No comments: