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 ».
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« .
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« .
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« .
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.
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« .
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.
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.
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.
PRINT '--10 REMOVE THE LOCAL SERVER AS DISTRIBUTOR :' EXEC master.sys.sp_dropdistributor;
J’espère que cet article vous sera utile.
