SQL Feather and Quill

Here’s a script I came up with to move my central management server’s Groups and Server from one server to another.  I know it doesn’t make a ton of sense to do this, but I started testing the CMS setup on a test server and it’s worked so well, we wanted to put it on a production server.  There are two scripts that are run on the source server that create scripts to run on the destination server.

/*
--Verify you've got the correct groups.
Select * from dbo.sysmanagement_shared_server_groups_internal
where is_system_object = 0 --ignore the builtin groups.
*/
Select '
Declare @server_group_id int
EXEC msdb.dbo.sp_sysmanagement_add_shared_server_group @parent_id=1, @name=N''' + name
+ ''', @description=N''' + description + ''', @server_type=0, @server_group_id=@server_group_id OUTPUT'
+ CHAR(13) + CHAR(10)
+ 'Go'
from dbo.sysmanagement_shared_server_groups_internal
where is_system_object = 0 --ignore the builtin groups.

Again, run the output of this on the destination server.

Here’s the script to move the servers with their groups – note the group id is dynamic, so even if the destination server has more groups, it will figure out the correct one.

Select
'Declare @SGID int, @SGName nvarchar(255)
Set @SGName = ''' + SGI.name + '''
Select @SGID = server_group_id from dbo.sysmanagement_shared_server_groups_internal
where name = @SGName
Declare @server_id int
EXEC msdb.dbo.sp_sysmanagement_add_shared_registered_server @server_group_id=@SGID, @name=N''' +
RSI.name + ''', @server_name=N''' + RSI.server_name + ''', @description=N''' + RSI.description + ''', @server_type=0, @server_id=@server_id OUTPUT'
+ CHAR(13) + CHAR(10)
+ 'Go'
from dbo.sysmanagement_shared_registered_servers_internal RSI
Join dbo.sysmanagement_shared_server_groups_internal SGI on RSI.server_group_id = SGI.server_group_id

Finally, run the output of this on the destination server.

Here’s a couple of resources I used to figure this stuff out:

• SQLSkills and Kimberly Tripp
• SQLServerPedia and Brent Ozar