Changer la connexion locale ou le propriétaire d’un plan de maintenance après sa migration

our ceux qui ont déjà tenté l’expérience, la migration d’un plan de maintenance sous SQL Server réserve quelques surprises. En effet, la connexion locale associée au package n’est pas modifiable et il faut à posteriori  créer une nouvelle connexion et modifier les tâches du plan en conséquence pour qu’elles prennent en compte la nouvelle connexion. Il va sans dire que cela n’est pas forcément pratique et bien souvent on préfère recréer le plan de maintenance sur le nouveau serveur soit "from scratch" soit avec des copier-coller. De la même manière si on veut changer le propriétaire d’un job lié à un plan de maintenance on se retrouve rapidement confronté à quelques difficultés. Le fait de changer le propriétaire d’un job est plutôt facile en soi mais si on modifie et revalide le plan de maintenance par la suite celle-ci écrase notre changement de propriétaire. L’astuce consiste à changer le propriétaire du package SSIS associé au plan de maintenance concerné. Voici une procédure qui vous permettra de changer rapidement ces 2 paramètres après migration.

Ce script a été testé sur SQL Server 2008 / 2008 R2 et 2012. Pour la version 2005, il faudra remplacer la table msdb.dbo.sysssispackages par msdb.dbo.sysdtspackages90. /!\ A vérifier sur cette version d’environ /!\ Si quelqu’un a le temps de tester et me donner un feedback je suis preneur Sourire

Les paramètres d’entrées sont les suivants :

  • @change_type : Contrôle processus de modification du package SSIS associé au plan de maintenance. Les valeurs possibles sont OWNER (changement de propriétaire), CONNECTION  (changement du nom de serveur dans la chaine de connexion) ou ALL (changement des 2 paramètres)
  • @maintenance_plan : Nom du plan de maintenance concerné
  • @oldersername : Nom du l’ancien serveur qui hébergeait le plan de maintenance
  • @newservername : Nom du nouveau serveur qui héberge le plan de maintenance
  • @newowner : Nom du nouveau propriétaire du package SSIS associé au plan de maintenance

 

[sourcecode language='sql'  padlinenumbers='true']
SET NOCOUNT ON;

DECLARE @change_type VARCHAR(10) = 'CONNECTION';            -- Type of change : CONNECTION / OWNER / ALL 
DECLARE @maintenance_plan SYSNAME = 'MaintenancePlan';      -- Name of the maintenance plan to apply changes 
DECLARE @oldservername SYSNAME = 'OLDERSERVERNAME';                -- Name of the old server that hosted the maintenance plan 
DECLARE @newservername SYSNAME = @@SERVERNAME;              -- Name of the new server that hosts the maintenance plan 
DECLARE @newowner SYSNAME = 'LANGROUP\dbn';                           -- Name of the new owner for the maintenance plan (ssis)

DECLARE @oldowner SYSNAME; 
DECLARE @debug BIT = 0; -- ACTIVE DEBUG (=1) 
DECLARE @xml NVARCHAR(MAX);

-- Verification of existing maintenance plan 
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysssispackages WHERE name = @maintenance_plan) 
BEGIN 
RAISERROR('The maintenance plan %s doesnt exist', 16, 1, @maintenance_plan); 
RETURN; 
END

-- Verification of the type of change 
IF @change_type NOT IN ('CONNECTION', 'OWNER', 'ALL') 
BEGIN 
RAISERROR('The @change_type parameter must equal to CONNECTION, OWNER OR ALL', 16, 1); 
RETURN; 
END

-- Changing of the connection string value 
IF @change_type IN ('ALL', 'CONNECTION') 
BEGIN 
PRINT 'REPLACE OLD SERVER NAME ' + @oldservername + ' BY THE NEW SERVER NAME : ' + @newservername + ' INTO THE  MAINTENANCE PLAN';

BEGIN TRY 
  SELECT  
   @xml = cast(cast(cast(packagedata as varbinary(max)) as xml) as nvarchar(max)) 
  FROM msdb.dbo.sysssispackages 
  WHERE name = @maintenance_plan;

  -- DEBUG 
  IF @DEBUG = 1 SELECT CAST(@xml AS XML) AS before_change; 
  
  -- Case when connection string use server parameter 
  IF (@xml LIKE '%server=''' + @oldservername + '%') 
  BEGIN 
   PRINT '--> Maintenance Plan with server parameter'; 
   SET @xml= replace(@xml,'server=''' + @oldservername + '','server=''' + @newservername + ''); 
  END 
    
  -- Case when connection string use Data source parameter 
  IF (@xml LIKE '%Data Source=''' + @oldservername + '%') 
  BEGIN 
   PRINT '--> Maintenance Plan with Data Source parameter'; 
   SET @xml= replace(@xml,'Data Source=''' + @oldservername + '','Data Source=''' + @newservername + ''); 
  END 
  
  PRINT 'Update maintenance plan done …'; 
  
  -- DEBUG 
  IF @DEBUG = 1 SELECT CAST(@xml AS XML) AS after_change;

  -- Validation of the xml document after replacing the connection string 
  SELECT CAST(@xml AS XML);

  -- Update ssis package with new connection string 
  UPDATE msdb.dbo.sysssispackages 
   SET packagedata = CAST(@xml AS VARBINARY(MAX)) 
  WHERE name = @maintenance_plan; 
  
END TRY 
BEGIN CATCH 
  PRINT CHAR(13) + 'An error occured during the update of the maintenance plan : ' + CHAR(13) + 
        'Error : ' + CAST(ERROR_NUMBER() AS VARCHAR(4)) + ' Severity : ' +  CAST(ERROR_SEVERITY() AS VARCHAR(4)) + ' State : ' +  CAST(ERROR_STATE() AS VARCHAR(4)) + CHAR(13) + ERROR_MESSAGE() 
END CATCH; 
END;

-- Changing of the owner of the maintenance plan 
IF @change_type IN ('ALL', 'OWNER') 
BEGIN 
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @newowner) 
BEGIN 
  RAISERROR('The new owner %s of the maintenance plan doesnt exist on the SQL Server instance.', 16, 1, @newowner); 
  RETURN; 
END

SELECT @oldowner = sp.name FROM sys.server_principals AS sp 
INNER JOIN msdb.dbo.sysssispackages AS ssis 
  ON sp.sid = ssis.ownersid 
WHERE ssis.name = @maintenance_plan; 
  
PRINT 'REPLACE OLD OWNER ' + COALESCE(@oldowner, 'UNKNOW ') + ' BY THE NEW OWNER : ' + @newowner; 
  
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @newowner) 
BEGIN 
  RAISERROR('The new owner %s for the maintenance plan %s doesnt exist on the SQL Server instance.', 16, 1, @maintenance_plan, @newowner); 
  RETURN; 
END 
  
-- Update owner of the SSIS package 
UPDATE msdb.dbo.sysssispackages 
  SET ownersid = (SELECT [sid] FROM sys.server_principals WHERE name = @newowner) 
WHERE name = @maintenance_plan; 
  
PRINT 'Update owner of the maintenance plan done …'; 
  
-- Update owner of the corresponding SQL job 
WITH cte_owner_job 
AS 
( 
  SELECT 
   ssis.name, 
   j.owner_sid 
FROM msdb.dbo.sysssispackages AS ssis 
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp 
  ON ssis.id = sp.plan_id 
INNER JOIN msdb.dbo.sysjobs AS j 
  ON j.job_id = sp.job_id 
where ssis.name = @maintenance_plan 
) 
UPDATE cte_owner_job 
SET owner_sid = (select [sid] from sys.server_principals WHERE name = @newowner); 
  
PRINT 'Update owner of the corresponding SQL Server jobs …'; 
  
END; 
[/sourcecode]

 

Bonne migration de plan de maintenance !!

David BARBARIN (Mikedavem)

MVP SQL Server

2 réflexions au sujet de « Changer la connexion locale ou le propriétaire d’un plan de maintenance après sa migration »

  1. Merci David pour ce script très utile.
    Juste une remarque dans mon cas pour un plan de maintenance créé avec SQL 2012, il n’y a pas de ‘ (apostrophe) autour du nom du serveur aussi j’ai du ajouter le code suivant:
    — Case when connection string use Data source parameter without ‘
    IF (@xml LIKE ‘%Data Source=’ + @oldservername + ‘%’)
    BEGIN
    PRINT ‘–> Maintenance Plan with Data Source parameter';
    SET @xml= replace(@xml,’Data Source=’ + @oldservername +  »,’Data Source=’ + @newservername +  »);
    END
    Encore merci!

Laisser un commentaire