Suppression d’une Réplication Transactionnelle SQL Server

Introduction

Dans cet article, je vais aborder les différentes étapes pour supprimer complètement une réplication transactionnelle d’une base de données SQL Server avec T-SQL.
Cet article s’applique à une réplication transactionnelle de type « Pull », et le serveur de publication « Publisher » joue le rôle aussi du distributeur « Distributor ».

 

Les étapes

Il est important de respecter l’ordre de ces étapes afin de ne pas se trouver avec des enregistrements orphelins qui risquent d’empêcher la reconfiguration de la réplication ou de provoquer certains incidents sur le serveur.

Déclaration des variables :

DECLARE @distributor sysname     -- Nom du serveur de distribution 
DECALRE @distributionDB sysname  -- Nom de la base de distribution "distribution" 
DECLARE @publisher sysname       -- Nom du serveur de publication
DECALRE @publicationName sysname -- Nom de la publication 
DECLARE @publicationDB sysname   -- Nom de la base de données publiée 
DECLARE @subscriber sysname      -- Nom du serveur abonné
DECLARE @command_sql varchar(max)  
DECLARE @command nvarchar(1024)
DECLARE @paramDef nvarchar(128)
DECLARE @shell varchar(max) 
DECLARE @val_xpcmd sql_variant
DECLARE @instance_name sysname 
DECLARE @machine_name sysname 
DECLARE @server_name sysname 
DECLARE @machineSub_name sysname
DECLARE @serverSub_name sysname 
DECLARE @help_proc varchar(max)
DECLARE @ReturnCode int
DECLARE @jobId varchar(128)

 

1 – Arrêter l’agent de lecture du journal sur le serveur de publication « Publisher »

PRINT '--1 STOP LOG READER AGENT ON PUBLISHER SERVER :'

        -- Récupérer le job_id de la publication
	SET @command='SELECT @jobIdOUT=job_id FROM '+QUOTENAME(@distributionDB)+'.dbo.MSlogreader_agents 
	WHERE publisher_db='''+@publicationDB+''';'
	SET @paramDef= '@jobIdOUT uniqueidentifier output'; 
	EXEC sp_executesql @command,  @paramDef,  @jobIdOUT=@jobId OUTPUT; 

	-- Arrêt du job. 
	SET @command_sql='EXEC msdb.dbo.sp_stop_job @job_id ='''+@jobId+''';' 
	EXEC (@command_sql) 	

 

2 – Arrêter l’agent de distribution sur le serveur abonné « Subscriber »

L’arrêt de l’agent se fait avec la procédure « sp_stoppullsubscription_agent » car il s’agit dans notre cas d’une réplication transactionnelle de type « Pull ».

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a4bb9d08-0ba0-4f0b-82bc-be07ae9148de/how-to-restart-distribution-agent-how-to-stop-and-then-start-the-distribution-agent?forum=sqlreplication

PRINT '--2 STOP THE PULL SUBSCRIPTION AGENT ON THE SUBSCRIBER SERVER : '
       SET @command_sql='EXEC '+QUOTENAME(@publicationDB)+'.sys.sp_stoppullsubscription_agent 
       @publisher = '''''+@publisher+''''', @publisher_db = '''''+@publicationDB+''''', @publication = '''''+@publicationName+''''';'

       SET @command_sql = REPLACE(@command_sql, char(10), ' ')
       SET @command_sql = REPLACE(@command_sql, char(13), ' ')
       SET @shell = 'master.dbo.xp_cmdshell ''sqlcmd.exe -S '+@subscriber+' -d master -E -s "|" -Q"SET NOCOUNT ON '+@command_sql+'" '''
	
       EXEC (@shell) 	

 

3 – Supprimer la subscription sur le serveur de publication « Publisher »

Suppression de l’abonnement à tous les articles de la base de publication, elle se fait avec la procédure « sp_dropsubscription« .

https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-dropsubscription-transact-sql?view=sql-server-2017

PRINT '--3 DROP SUBSCRIPTION THE PUBLISHER SERVER : '
       SET @command_sql='EXEC '+QUOTENAME(@publicationDB)+'.sys.sp_dropsubscription @publication = '''+@publicationName+''', 
       @article = N''all'', @subscriber = '''+@subscriber+''';'
       EXEC (@command_sql) 

 

4 – Supprimer la subscription « Pull »  à la base de donnée sur le serveur abonné « Subscriber »

Suppression de l’abonnement à la base de publication sur le serveur abonné, elle se fait avec la procédure « sp_droppullsubscription« .

https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-droppullsubscription-transact-sql?view=sql-server-2017

PRINT '--4 DROP THE PULL SUBSCRIPTION ON THE SUBSCRIBER SERVER : '
      SET @command_sql='EXEC '+QUOTENAME(@publicationDB)+'.sys.sp_droppullsubscription @publisher = '''''+@publisher+''''', 
      @publisher_db = '''''+@publicationDB+''''', @publication = '''''+@publicationName+''''';'

      SET @command_sql = REPLACE(@command_sql, char(10), ' ')
      SET @command_sql = REPLACE(@command_sql, char(13), ' ')
      SET @shell = 'master.dbo.xp_cmdshell ''sqlcmd.exe -S '+@subscriber+' -d master -E -s "|" -Q"SET NOCOUNT ON '+@command_sql+'" '''

      EXEC (@shell) 

 

5 – Nettoyer la subscription sur le serveur abonné « Subscriber »

La suppression des métadonnées de l’abonné avec la procédure « sp_subscription_cleanup« .

https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-subscription-cleanup-transact-sql?view=sql-server-2017

PRINT '--5 CLEANUP SUBSCRIPTION ON THE SUBSCRIBER SERVER :'
       SET @command_sql='EXEC '+QUOTENAME(@publicationDB)+'.sys.sp_subscription_cleanup 
       @publisher = '''''+@publisher+''''', @publisher_db = '''''+@publicationDB+''''', @publication = '''''+@publicationName+''''';'

       SET @command_sql = REPLACE(@command_sql, char(10), ' ')
       SET @command_sql = REPLACE(@command_sql, char(13), ' ')
       SET @shell = 'master.dbo.xp_cmdshell ''sqlcmd.exe -S '+@subscriber+' -d master -E -s "|" -Q"SET NOCOUNT ON '+@command_sql+'" '''

       EXEC (@shell) 

 

6 – Supprimer la publication de la base sur le serveur de publication « Publisher »

Suppression de la publication, le snapshot agent et tous les articles de la publication avec la procédure « sp_droppublication« . Tous les abonnés doivent être supprimés avant.

https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-droppublication-transact-sql?view=sql-server-2017

PRINT '--6 DROP THE PUBLICATION ON PUBLISHER SERVER : '
      SET @command_sql = 'EXEC '+QUOTENAME(@publicationDB)+'.sys.sp_droppublication @publication = '''+@publicationName+''';'
      EXEC (@command_sql)	

 

7 – Supprimer tous les objets de la base de réplication sur le serveur de publication « Publisher »

Suppression de tous les objets de la base de réplication avec la procédure « sp_removedbreplication« .

https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-removedbreplication-transact-sql?view=sql-server-2017

PRINT '--7 DROP ALL REPLICATION OBJECTS ON THE PUBLISHER DATABASE :'
	SET @command_sql = 'EXEC '+QUOTENAME(@publicationDB)+'.sys.sp_removedbreplication '''+@publicationDB+''';'
	EXEC (@command_sql)	

 

8 – Supprimer le serveur de publication du serveur de distribution

Dans notre cas, le serveur de publication joue le rôle de distribution aussi, donc la procédure de suppression « sp_dropdistpublisher » est à exécuter sur le serveur de publication.

https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-dropdistpublisher-transact-sql?view=sql-server-2017

PRINT '--8 DROP A DISTRIBUTION PUBLISHER :'
       SET @command_sql='EXEC master.sys.sp_dropdistpublisher '''+@publisher+''',@no_checks = 1, @ignore_distributor = 1;'
       EXEC (@command_sql)	

 

9 – Supprimer la base de distribution « distribution »

Même remarque que l’étape précédente, la base de distribution se trouve sur le serveur de publication.

https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-dropdistributiondb-transact-sql?view=sql-server-2017

PRINT '--9 DROP DISTRIBUTION DATABASE :'
       SET @command_sql='EXEC master.sys.sp_dropdistributiondb '''+@distributionDB+''';'
       EXEC (@command_sql)

 

10 – Supprimer le serveur de distribution

Désinstallation du serveur de distribution avec la procédure « sp_dropdistributor« . Dans notre cas, elle se fait sur le serveur de publication.

https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-dropdistributor-transact-sql?view=sql-server-2017

PRINT '--10 REMOVE THE LOCAL SERVER AS DISTRIBUTOR :'
	EXEC  master.sys.sp_dropdistributor;

 

J’espère que cet article vous sera utile.