Blog

le 21/09/2018
Thomas Bordet
Données / Sécurité, SQL Server

Réplication Transactionnelle SQL Server (3)

Introduction :

Dans ce dernier article sur la réplication transactionnelle SQL Server, je vais aborder l’aspect monitoring de la réplication. Nous pouvons citer trois méthodes pour monitorer la réplication SQL Server :

L’utilisation de « Replication Monitor » via le Management Studio (SSMS)
L’utilisation des procédures stockées (T-SQL) ou des Dynamic Management Views (DMV)
L’utilisation des Alertes de réplication.

Pour consulter mes deux premiers articles sur la réplication transactionnelle, veuillez consulter les deux liens suivants :

https://easyteam.fr/la-replication-transactionnelle-sql-server-1/

https://easyteam.fr/replication-transactionnelle-sql-server-2/

1- Le Replication Monitor :

Le Replication Monitor est un outil du composant de la réplication, intégré dans le Management Studio (SSMS), qui nous permet d’avoir des informations détaillées en temps réel sur l’état de santé de chaque élément de la réplication, notamment :

L’état de fonctionnement des différents agents de la réplication (Snapshot Agent, Log Reader Agent, …)
Détecter l’origine du retard ou d’autres problèmes éventuels de la réplication,
Réaliser des actions d’administration et de gestion de la réplication (stop/start d’un agent, réinitialisation des abonnés, …).

Pour lancer le Replication Monitor, connectez-vous à l’instance SQL Server concernée par la réplication via le Management Studio, faites un clic-droit sur le dossier Réplication (ou sur n’importe quel sous-dossier) au sein de l’explorateur d’objets du Management Studio, puis cliquez sur Lancer le moniteur de réplication:

Une nouvelle fenêtre s’ouvrira :

A partir de cette fenêtre, on peut avoir un aperçu sur :

Toutes les publications d’un serveur.
Des informations relatives à chaque abonné à partir de « La liste des abonnés » (Subscription Wtach List), avec la possibilité de filtrer par type de réplication (Transactionnelle dans notre cas) et par type d’information :

Des informations relatives aux différents agents de chaque publication :

Pour afficher toutes les informations relatives à une réplication en particulier, cliquez sur le nom de la réplication dans la colonne gauche du Replication Monitor :

L’accès aux informations détaillées sur l’activité d’un abonné se fait depuis l’onglet All subscriptions, puis double-clic (ou clic-droit, puis afficher les détails). Une fenêtre multi-onglets s’affichera :

On peut y lire le détail des dernières informations relatives aux actions de synchronisation avec la possibilité de le filtrer :

Historique de synchronisation entre l’éditeur (publisher) et le distributeur (onglet Publisher To Distributor History) ==> activité liée au Snapshot Agent et au Log Reader Agent.
Historique de synchronisation entre le distributeur et l’abonné (onglet Distributor To Subscriber History) ==> activité liée au Distribution Agent.

Les commandes non distribuées (à cause d’un retard de réplication ou autres) et le temps estimé pour les appliqués.

L’onglet Agents affiche des informations relatives à l’activité des deux principaux agents de la réplication transactionnelle (Snapshot Agent et Log Reader Agent ), notamment le statut en cours des agents, la date de la dernière exécution, la durée d’exécution et la dernière action effectuée.

Pour afficher plus de détails sur l’activité de chaque agent, double-clic sur l’agent concerné. Une nouvelle fenêtre s’affichera :

Snapshot Agent :

Log Reader Agent :

L’onglet Jetons de suivi (Tracer Tokens):

Un jeton de suivi est utilisé pour mesurer les temps de latence de la livraison des transactions entre l’éditeur et le distributeur et ainsi entre le distributeur et chaque abonné. Un jeton de suivi est un marqueur (une petite quantité de données) inséré dans le journal des transactions de la base publiée, et qui est considéré comme une transaction standard à répliquer par l’agent de la réplication (Log Reader Agent). Cette action n’a aucun effet sur les données utilisateurs.

Pour insérer un jeton de suivi, cliquez sur Inserer un suivi , puis observer le résultat :

L’onglet Avertissements (Warnings) nous fournit des informations relatives sur les alertes et les avertissements de notre réplication :

Pour la réplication transactionnelle, nous avons deux alertes par défaut :

Une alerte si un abonné expire avant le seuil défini : valeur par défaut (80%).
Une alerte si la latence dépasse le seuil défini : valeur par défaut 30 secondes. Mais nous avons la possibilité de modifier ce seuil selon notre besoin en minutes ou en heures.

Normalement, ces deux alertes sont suffisantes pour surveiller la réplication, mais nous avons la possibilité d’en rajouter d’autres. Cliquez sur Configure Alerts pour afficher la liste des alertes disponibles :

Nous allons donner plus de détails sur les alertes de la réplication dans la troisième partie de cet article.

Remarque :

Nous pouvons aussi consulter l’état de fonctionnement des agents de la réplication transactionnelle directement via le Management Studio sans passer par le « Replication Monitor »:

Pour afficher l’état du Snapshot Agent : Dérouler le dossier Replication ==> Local Publications ==> Clic-droit sur la publication concernée ==> View Snapshot Agent Status

Pour afficher l’état du Log Reader Agent : Dérouler le dossier Replication ==> Local Publications ==> Clic-droit sur la publication concernée ==> View Log Reader Agent Status

Pour afficher l’état de synchronisation de l’abonné, se connecter via le Management Studio sur le serveur abonné(Subscriber), dérouler le dossier Replication==> Local Subscriptions ==> clic-droit sur l’abonné concerné ==> View Synchronization Status :

2- Les Procédures stockées :

Nous pouvons aussi surveiller l’état de santé d’une réplication avec l’utilisation des procédures stockées nommées sp_replmonitorhelp* qui s’exécutent sur le serveur de distribution, telles que :

sp_replmonitorhelppublication : Renvoie des informations sur l’état actuel d’une ou plusieurs publications d’un serveur de publication.
sp_replmonitorhelppublisher : Retourne les informations sur l’état actuel d’un ou plusieurs serveurs de publication associés à un serveur de distribution.
sp_replmonitorhelpsubscription : Renvoie des informations sur l’état actuel des abonnements appartenant à une ou plusieurs publications du serveur de publication, et retourne une ligne pour chaque abonnement retourné.

Pour plus d’information sur l’utilisation de ces procédures, veuillez consulter le lien suivant : https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sp-replmonitorhelppublisher-transact-sql?view=sql-server-2017

L’utilisation de la dernière procédure (sp_replmonitorhelpsubscription) est suffisante pour surveiller une réplication transactionnelle.

Syntaxe :

sp_replmonitorhelpsubscription [ @publisher = ] 'publisher'  
    [ , [ @publisher_db = ] 'publisher_db' ]  
    [ , [ @publication = ] 'publication' ]  
    [ , [ @publication_type = ] publication_type ]   
    [ , [ @mode = ] mode ]  
    [ , [ @topnum = ] topnum ]   
    [ , [ @exclude_anonymous = ] exclude_anonymous ]   
    [ , [ @refreshpolicy = ] refreshpolicy ]

Cas d’utilisation :

@publication_type = 0 // Réplication transactionnelle

@mode = 3 // Retourne uniquement les abonnements qui ont des erreurs ou ayant généré des avertissements de mesure de seuil.

Dans notre cas, le serveur de distribution et de publication est le même, il suffit donc d’exécuter la procédure de cette façon sur le serveur de publication :

EXEC distribution.sys.sp_replmonitorhelpsubscription @@SERVERNAME, @publication_type=0, @mode=3

Mais nous pouvons très bien faire appel à cette procédure depuis une autre procédure ou un script T-SQL afin de renvoyer ‘0’ si la procédure ne retourne aucune ligne, et ‘2’ si la procédure renvoie une ou plusieurs lignes. C’est plus facile si nous souhaitons faire appel à la requête depuis une sonde de supervision comme Nagios par exemple.

USE [msdb]  
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'usp_monitor_reptrans')
BEGIN 
	DROP PROCEDURE usp_monitor_reptrans
END 
GO 
CREATE PROCEDURE usp_monitor_reptrans 		  		 
WITH ENCRYPTION 
AS 
BEGIN 
	SET NOCOUNT ON 
	DECLARE @tmp_table TABLE (value varchar(1024))
	DECLARE @shell nvarchar(4000)
	DECLARE @check int 
	-- Activation de l'option xp_cmdshell si l'option n'est pas activée 
	DECLARE @val_xpcmd sql_variant
	SELECT @val_xpcmd=value FROM master.sys.configurations WHERE name ='xp_cmdshell'
	IF (@val_xpcmd !=1)
	BEGIN 
		Exec master.dbo.sp_configure 'show advanced options', 1
		RECONFIGURE
		Exec master.dbo.sp_configure 'xp_cmdshell', 1
		RECONFIGURE
	END 
	
	SET @shell='sqlcmd.exe -S Nom_Serveur -E -h -1 -W -Q"EXEC distribution.sys.sp_replmonitorhelpsubscription @@SERVERNAME, @publication_type=0, @mode=3"'
	--EXEC  xp_cmdshell @shell
	INSERT INTO @tmp_table EXEC  xp_cmdshell @shell
	DELETE FROM @tmp_table WHERE VALUE IS NULL  
	SELECT @check=COUNT(*) FROM @tmp_table
	IF @check > 0 
		SET @check=2 
	ELSE 
	SET @check=0
	select @check 
END 
GO

3- Les Alertes :

Lors de la mise en place d’une réplication, SQL Server crée automatiquement un ensemble d’alertes prédéfinies. Ces alertes sont visibles depuis le Replication Monitor comme déjà évoqué dans la première partie de cet article, mais nous pouvons les visualiser facilement depuis la section « Alertes » de « SQL Server Agent » :

Par défaut, c’est uniquement les alertes de type Avertissement (Warning) qui sont activées. Pour activer une alerte en particulier, il suffit de faire un clic-droit sur l’alerte concernée, puis sélectionner Activer.

Pour configurer une alerte afin de répondre à un événement particulier de la réplication (notification par mail ou déclencher un job en cas d’erreur par exemple), il faut aller dans les propriétés de l’alerte concernée (clic-droit, puis properties ou en double-cliquant dessus) :

Dans l’onglet General nous trouvons la définition de l’alerte (Nom, type d’alerte et l’événement).
Dans l’onglet Response nous avons la possibilité de configurer un travail complémentaire. Exécuter un job ou notifier un opérateur (envoie de mail par exemple)