J’ai eu à migrer dernièrement la configuration d’un serveur centralisé (via la fonctionnalité Central Management Server) avec l’ensemble des groupes et des serveurs inscrits d’une instance vers une autre. Il est bien sur possible de faire cela graphiquement mais c’est méthode n’est vraiment viable que lorsqu’on a peu d’instances SQL Server inscrits mais quand leur nombre augmente l’effort est beaucoup plus important et l’opération devient rapidement fastidieuse. Bien entendu il existe l’export de la configuration via le fichier XML qui reste la solution de loin la plus simple. Je vous propose cependant un script qui permet le transfert de la configuration d’un serveur centralisé vers un autre.

Suite à la remarque de Dje j’ai décidé de préciser un peu plus le contexte. Il est vrai qu’il aurait été plus simple d’utiliser l’export via fichier XML à priori mais en utilisant cette méthode une quelconque automatisation devient compliquée. Le script livré permet juste de migrer la configuration d’une instance SQL Server vers un autre. Pour faire une synchronisation des groupes et serveurs entre serveurs centrales, il faudra bien entendu ajouter ou adapter du code TSQL

En paramètre :

• Le nom de l’ancien serveur central (@mgt_server_old)
• Le nom que portera l’ancien serveur central sur le nouvel environnement en tant que serveur enregistré (@mgt_server_old_into_groups)
• Le nom du nouveau serveur central (@mgt_server)
• Le nom que portera le nouveau serveur central dans le nouvel environnement en tant que serveur enregistré (@mgt_server_into_groups)

Pourquoi avoir 2 noms différents pour un même serveur ? Pour ceux-ci qui utilisent CMS savent qu’il n’est pas possible d’enregistrer une instance SQL Server avec son nom original si ce dernier est utilisé en tant que serveur central. Une ruse consiste à changer ce nom par différents moyens : ajout du port d’écoute de l’instance si celui-ci est statique ([instance name], port), ajout d’un alias, utilisation du FQDN de l’instance, utilisation de l’adresse IP … bref vous l’aurez compris tant que le nom du serveur qui sera enregistré est différent du nom original il sera possible de l’enregistrer sans avoir l’erreur suivante :

Le script se décompose en 3 parties :

• La génération du script des groupes « racines » dans le central manager
• La génération du script des groupes enfants
• La génération du script des serveurs

Il suffit de copier le script généré et de l’exécuter sur le serveur central cible.

 

DECLARE @mgt_server_old VARCHAR(100) = ‘WKSDBN01′;
DECLARE @mgt_server_old_into_groups VARCHAR(100) = ‘WKSDBN01,1433′;
DECLARE @mgt_server VARCHAR(100) = ‘WKSDBN01\instance2′;
DECLARE @mgt_server_into_groups VARCHAR(100) = ‘WKSDBN01\instance2,3951′;

DECLARE @sql VARCHAR(MAX) = ‘/* Create root groups */’ + CHAR(13) + CHAR(10) +
                             ‘DECLARE @server_group_id INT;’  + CHAR(13) + CHAR(10) +
                             ‘DECLARE @SGID INT;’ + CHAR(13) + CHAR(10) +
                             ‘DECLARE @SGName SYSNAME;’ + CHAR(13) + CHAR(10) +
                             ‘DECLARE @server_id INT;’ + CHAR(13) + CHAR(10) +
                             ‘DECLARE @server_group_id_children INT;’ + CHAR(13) + CHAR(10) +  CHAR(13) + CHAR(10);
DECLARE @name_root SYSNAME;
DECLARE @description_root NVARCHAR(2048);   
DECLARE @server_group_id_root INT;  
DECLARE @server_group_id_children INT;
DECLARE @name_children SYSNAME;
DECLARE @description_children NVARCHAR(2048);   
DECLARE @parent_group_name SYSNAME;           

DECLARE C_GROUP_ROOT CURSOR FOR
SELECT
name,
[description]
FROM msdb.dbo.sysmanagement_shared_server_groups_internal
WHERE is_system_object = 0 –ignore the builtin groups.
AND parent_id = 1;

OPEN C_GROUP_ROOT;
FETCH NEXT FROM C_GROUP_ROOT INTO @name_root, @description_root;

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @sql = @sql +
  ‘EXEC msdb.dbo.sp_sysmanagement_add_shared_server_group @parent_id=1, @name=N »’ + @name_root
  +  »’, @description=N »’ + @description_root +  »’, @server_type=0, @server_group_id = @server_group_id_children OUTPUT;’
  + CHAR(13) + CHAR(10);
DECLARE C_GROUP_CHILDREN CURSOR FOR
WITH CTE
AS
(
  SELECT
   RGI.name,
   RGI.server_group_id,
   RGI.description,
   CAST( » AS SYSNAME) AS parent_group_name,
   CAST( » AS NVARCHAR(2048)) AS parent_group_description
  FROM msdb.dbo.sysmanagement_shared_server_groups_internal AS RGI
  WHERE name = @name_root
  UNION ALL
  SELECT
   RGI.name,
   RGI.server_group_id,
   RGI.description,
   RGI2.name,
   RGI2.description
  FROM msdb.dbo.sysmanagement_shared_server_groups_internal AS RGI
  INNER JOIN CTE AS RGI2 
   ON RGI2.server_group_id = RGI.parent_id
)
SELECT name, server_group_id, description, parent_group_name
FROM CTE;
OPEN C_GROUP_CHILDREN;
FETCH NEXT FROM C_GROUP_CHILDREN INTO @name_children, @server_group_id_children, @description_children, @parent_group_name;
WHILE @@FETCH_STATUS = 0
BEGIN

  IF @name_children <> @name_root
  BEGIN
   SELECT @sql = @sql + CHAR(13) + CHAR(10) +
    ‘SELECT @server_group_id = MAX(server_group_id) ‘ +
    ‘FROM msdb.dbo.sysmanagement_shared_server_groups_internal ‘ +
    ‘WHERE name =  »’ + @parent_group_name +  »';’ + CHAR(13) + CHAR(10) +
    ‘EXEC msdb.dbo.sp_sysmanagement_add_shared_server_group @parent_id=@server_group_id, @name = N »’ + @name_children +  »’, @description=N »’ + ISNULL(@name_children,  ») +  »’, @server_type=0, @server_group_id=@server_group_id_children OUTPUT;’
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
  END;
  SELECT
  @sql = @sql +
‘EXEC msdb.dbo.sp_sysmanagement_add_shared_registered_server @server_group_id=@server_group_id_children, @name=N »’ +
CASE
   WHEN server_name = @mgt_server_old_into_groups THEN @mgt_server_old
   WHEN server_name = @mgt_server THEN @mgt_server_into_groups
   ELSE server_name
  END +  »’, @server_name=N »’ +
  CASE
   WHEN server_name = @mgt_server_old_into_groups THEN @mgt_server_old
   WHEN server_name = @mgt_server THEN @mgt_server_into_groups
   ELSE server_name
  END +  »’, @description=N »’ + description +  »’, @server_type=0, @server_id=@server_id OUTPUT;’
  + CHAR(13) + CHAR(10)
  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
  WHERE server_group_id = @server_group_id_children
  FETCH NEXT FROM C_GROUP_CHILDREN INTO @name_children, @server_group_id_children, @description_children, @parent_group_name;
END
CLOSE C_GROUP_CHILDREN;
DEALLOCATE C_GROUP_CHILDREN;

FETCH NEXT FROM C_GROUP_ROOT INTO @name_root, @description_root;
END;

CLOSE C_GROUP_ROOT;
DEALLOCATE C_GROUP_ROOT;

SELECT @sql FRO FOR XML PATH;

Une astuce ici est d’ajouter la clause FOR XML PATH pour afficher le résultat afin d’éviter la limitation d’affichage des caractères connue de SSMS. Cependant il est vrai que que le script est assez complexe. L’exercice est plaisant mais Ptit_Dje a montré ici que faire la même chose en PowerShell était beaucoup plus simple qu’en pure SQL !!

Bon transfert !!

David BARBARIN (Mikedavem)
MVP SQL Server