Introduction :

Dans mon premier article sur la réplication transactionnelle SQL Server,  j’ai abordé :

• Les différents types de la réplication qui sont proposés par SQL Server.
• Le principe de fonctionnement de la réplication transactionnelle.
• Comment mettre en place une base de données en réplication transactionnelle.

Le lien vers l’article : https://easyteam.fr/la-replication-transactionnelle-sql-server-1/?lang=fr
Dans ce deuxième article, je vais essayer d’expliquer le fonctionnement interne de la réplication transactionnelle.
La configuration ou la mise en place de la réplication transactionnelle se traduit au niveau de l’instance de publication/distribution par :

• La création de la base de distribution contenant un ensemble de tables système qui stockent les métadonnées de la publication, ainsi que des vues et des procédures nécessaires à son fonctionnement.
• La création d’une liste de logins pour accéder à la réplication (Publication Access List – PAL) au niveau de la base de publication.
• La création de deux linked servers : un vers le serveur abonné et un autre local pour le fonctionnement de la distribution même si le serveur de distribution est en local.
• Un ensemble de jobs utilisés pour le fonctionnement de la réplication (les différents agents et de tâches de surveillance et de purge).

Au niveau de l’abonné :

• Des tables système au niveau de la base de données répliquée.
• D’un job pour l’agent de distribution.

 

1- Le serveur de publication/distribution

1.1 – La base de données de distribution

La base de données de distribution stocke les métadonnées, les données d’historique pour tous les types de réplication, et les transactions s’il s’agit de la réplication transactionnelle.
On peut identifier le nom de la base de distribution par une requête SQL ou via l’interface du SQL Server Management Studio (SSMS).

Select name from master.sys.databases where is_distributor=1

 
 
 
 
 
On remarque via le Management studio que la base de distribution « distribution »  fait partie des bases système de l’instance :

Les tables systèmes
Parmi les tables systèmes qui stockent les métadonnées et l’historique de la réplication transactionnelle, on peut lister les tables suivantes :

• MSpublication_access :  Contient la liste des logins autorisés à se connecter à la publication.
• MSpublications : Contient des informations sur la publication.
• MSsubscriptions :  Contient des informations sur le ou les abonnés.
• MSarticles :  Contient la liste de tous les articles publiés.
• MSdistribution_history :  Contient l’historique des transactions distribuées (les dernières 24 heures).
• MSlogreader_agents :  Contient des informations sur l’agent de la réplication (Log Reader Agent).
• MSlogreader_history :  Contient l’historique de l’activité de l’agent de réplication (les dernières 48 heures).
• MSrepl_transactions :  Contient la liste des transactions répliquées. Le contenu de la table dépend de la rétention de la réplication (72 heures dans notre cas).
• MSsnapshot_agents :  Contient des informations sur l’agent snapshot.
• MSsnapshot_history :  Contient l’historique du snapshot agent.
• MSdistribution_agents :  Contient des informations sur l’agent de distribution.
• MSpublicationthresholds : Contient des informations sur le seuil des alertes.
• MSpublisher_databases :  Contient la liste des bases publiées et distribuées par le serveur de distribution local.
• MSRepl_commands : Contient la liste des commandes répliquées.
• MSreplication_monitordata :  Contient des informations utilisées par le monitor de réplication (Replication Monitor).

Pour plus d’informations sur le rôle des tables système, veuillez consulter le lien:  https://docs.microsoft.com/en-us/sql/relational-databases/system-tables
 

1.2 – Publication Access List (PAL)

Lors de la configuration de la réplication, SQL Server crée automatiquement une liste des comptes (logins) qui sont autorisés à accéder à la réplication. Cette liste s’appelle PAL (Publication Access List) :

• La liste PAL est stockée dans une vue de la base de données publiée (une vue cachée) et dans la table système de la base de distribution « MSpublication_access« .
• Quand un agent ou un utilisateur essaie de se connecter à la publication, SQL Server vérifie si le login utilisé par la connexion fait partie de la liste avant de lui autoriser l’accès.
• Tout les logins qui sont membre du rôle fixe du serveur « sysadmin » font partie de la liste, ainsi que le login utilisé pour la création de la publication.
• SQL Server crée automatiquement un compte supplémentaire qui s’appelle « distributor_admin » avec un mot de passe généré automatiquement (un random password) si la base de distribution se trouve sur le serveur de publication (en local).
  • Le compte est ajouté automatiquement aux membres du rôle fixe « sysadmin » du serveur de distribution.
  • Ce compte est utilisé par SQL Server pour exécuter les différentes procédures de la réplication quand elles se connectent à la base de distribution.
    
  • Le compte est utilisé aussi dans la configuration du linked server « repl_distributor« .
  • Il ne faut pas supprimer le compte ou modifier son mot de passe manuellement (utiliser plutôt la procédure sp_changedistributor_password).

Pour afficher la liste des logins PAL, il faut interroger la table système « distribution.dbo.MSpublication_access » ou exécuter la procédure suivante au niveau de la base de données de publication :

sp_help_publication_access [ @publication = ] 'publication'

 

1.3 – Les Linked Servers

Pour assurer la communication entre le publicateur et le distributeur (entre la publication et la base de distribution), SQL Server configure automatiquement un serveur lié (linked server ou remote server) qui s’appelle « repl_distributor » même si la base de distribution est configurée en local (sur le même serveur). SQL Server crée aussi un serveur lié pour chaque abonné.

Le compte « distributor_admin » est utilisé dans la configuration du linked server « repl_distributor » .

 

1.4 – La base de publication et MSDB

Pour identifier si une base de données est mise en publication, il suffit de vérifier la propriété « IsPublished » de la base. La requête suivante retourne la valeur « 1 »  si c’est vrai.

SELECT DATABASEPROPERTYEX(db_name(), 'IsPublished');

La base de publication contient un ensemble de vues système qui nous fournissent des informations sur la configuration de la publication :

•  [dbo].[syspublications]  : Contient une ligne pour chaque publication de la base.
•  [dbo].[syssubscriptions] : Contient une ligne pour chaque subscription (abonné) à la base.
•  [dbo].[sysarticles] : Contient tout les articles qui sont définis pour la publication dans la base.
• sp_help_publication_access @publication=’publication_name’ : retourne la liste des logins (PAL).

La base MSDB
La vue la plus importante au niveau de la base MSDB, c’est la vue qui gère les alertes de la réplication : [msdb].[dbo].[sysreplicationalerts]
 

1.5 – Les Jobs

L’exécution des différentes tâches de la réplication est assurée par un ensemble de jobs SQL Server Agent. On peut identifier la liste des jobs de la réplication par la requête SQL suivante ou par l’interface SSMS.

SELECT sj.name, sj.description, sc.name as category_name
FROM msdb.dbo.sysjobs as sj INNER JOIN msdb.dbo.syscategories as sc on sj.category_id=sc.category_id
WHERE sc.category_id >= 10
ORDER BY sc.category_id

On peut classer les jobs dans trois catégories : Les jobs des agents de la réplication, les jobs de nettoyage (Clean up jobs) et les jobs de surveillance (validation failure).

a- Les agents de la réplication

      Le Snapshot Agent (REPL-Snapshot)

• Le nom du job prend la forme : <Publisher>-<PublicationDatabase>-<Publication>-<Integer>.
  
• Le job génère un snapshot de tous les objets de base à répliquer pour initialiser la base de données sur le ou les abonnés. Il s’agit de la première étape à exécuter pour activer la réplication.
• Le job s’exécute en « one time » automatiquement à l’initialisation/réinitialisation de la réplication, ou manuellement en cas de besoin.
• Le snapshot généré par le job est déposé dans le partage configuré pour le fonctionnement de la réplication, puis récupéré par le ou les abonnés pour initialiser leur base de données en local.
• Le snapshot agent prend trois étapes pour exécuter son job :
  • Message de démarrage de l’agent (snapshot agent startup message) : sp_MSadd_snapshot_history @perfmon_increment = 0,  @agent_id = 1, @runstatus = 1,   @comments = N’Démarrage de Agent.’
  • Démarrage de l’agent (Run agent) :  -Publisher [publisher_serverName] -PublisherDB [publication_databaseName] -Distributor [distributor_serverName] -Publication [publication_name] -DistributorSecurityMode 
  • Détection de l’arrêt d’un agent non enregistré (Detect nonlogged agent shutdown). sp_MSdetect_nonlogged_shutdown @subsystem = ‘Snapshot’, @agent_id = 1

    Le Log Reader Agent (REPL-LogReader)

• Le job prend le nom de la forme : <Publisher>-<PublicationDatabase>-<Integer>
• Le job exécute l’agent de la réplication (C’est le job qui assure le fonctionnement de la réplication transactionnelle).
• Le job s’exécute sans arrêt (automatiquement au démarrage de SQL Server Agent ou manuellement après le snapshot agent). L’arrêt de ce job signifie l’arrêt de la réplication transactionnelle.
• Le Log Reader agent prend trois étapes pour exécuter son job :
  • Démarrage de l’agent de lecture du journal (log reader) :             sp_MSadd_logreader_history @perfmon_increment = 0, @agent_id = 1, @runstatus = 1,    @comments = N’Démarrage de Agent.’
  • Démarrage de l’agent (Run agent) :  -Publisher [publisher_serverName] -PublisherDB [publication_databaseName] -Distributor [distributor_serverName] -DistributorSecurityMode 1 -Continuous
  • Detect nonlogged agent shutdown : sp_MSdetect_nonlogged_shutdown @subsystem = ‘LogReader’, @agent_id = 1

b- Les jobs de nettoyage (Clean up jobs)

     Le job REPL-Distribustion CleanUp (Nettoyage de la distribution: distribution) : C’est le job qui nettoie les transactions répliquées de la base de distribution. Le job exécute la procédure sp_MSdistribution_cleanup @min_distretention=’valeur_min’ , @max_distretention=’valeur_max’

• @min_distretension : Correspond à la durée minimum des transactions à conserver dans la base de distribution une fois les transactions répliquées. La valeur 0 signifie que l’on autorise la suppression de toutes transactions répliquées, mais comme le job s’exécute chaque 10 minutes, on peut dire que les transactions sont conservées entre 0 et 10 minutes.
• @max_distretension : Correspond à la durée maximum des transactions à conserver dans la base de distribution si elles ne sont pas encore récupérées par le ou les abonnés. La valeur @max est de 72 heures dans notre configuration.
• Pour modifier ces durées de rétention, il suffit de modifier les paramètres de la procédure exécutée par le job (voir les propriétés du job).
• Attention à la durée maximum de rétention, car cela risque de faire grossir la taille de la base de distribution

   Le job REPL-History Cleanup (Nettoyage de l’historique de lagent:distribution) : C’est le job qui nettoie l’historique de l’agent de réplication.

• Le job exécute la procédure dbo.sp_Mshistory_cleanup @history_retention=’retention_value’. 
• Le job s’exécute chaque 10 minutes.
• La durée de rétention par défaut est de 48 heures.

   Le job REPL-Subscription Cleanup (Nettoyage de l’abonnement expiré) : Le job vérifie le statut de tout les abonnés de chaque publication et supprime ceux qui sont expirés.

• Un abonné est considéré comme expiré si aucune synchronisation n’a été effectuée entre lui et la publication pendant la période maximum de rétention (72 heures dans notre configuration). Dans ce cas, l’abonné est considéré comme expiré et toutes les traces de l’abonné sont supprimées au niveau de la publication.
• Le job s’exécute chaque 24 heures et exécute la procédure sys.sp_expired_subscription_cleanup

   Le job REPL-Alerte Response (Réinitialiser les abonnements présentant des erreurs de validation des données)

• Il exécute la procédure : sys.sp_MSreinit_failed_subscriptions @failure_level = 1
• Le job vérifie pour chaque publication la présence d’une erreur dans la table [msdb].[dbo].[sysreplicationalerts]. Si une erreur de type 20574 (Subscriber ‘%s’ subscription to article ‘%s’ in publication ‘%s’ failed data validation ) est présente dans la colonne alerte_error_code est détectée, une réinitialisation de la réplication se déclenche.
  

c- Les jobs de vérification et d’alerte (Validation failure)

     Le job REPL-Checkup (Vérification des agents de réplication) : C’est le job qui vérifie le bon fonctionnement de l’agent de réplication.

• Le job exécute la procédure sys.sp_replication_agent_checkup @heartbeat_interval = 10
• Si l’agent n’enregistre pas d’historique dans la base de distribution pendant l’intervalle spécifié ( @heartbeat_interval,  10 minutes par défaut), une erreur est générée et l’agent de réplication est considéré comme suspect.
• Le job s’exécute chaque 10 minutes.

     Le job REPL-Alerte Response (Actualisateur d’analyse de réplication pour distribution ou Replication Monitoring Refresher for Distribution).

• Le job actualise (refreshes) les requêtes mises en cache qui sont utilisées par le moniteur de la réplication.
• Il exécute la procédure Exec distribution.dbo.sp_replmonitorrefreshjob @iterations=0
  
• Le job est désactivé par défaut, mais il s’exécute sans arrêt si le paramètre @iterations est positionné à 0.

 

2- Le serveur de subscrition (abonné)

Les tables systèmes
On peut lister les tables suivantes au niveau de la base répliquée :

• [dbo].[MSreplication_objects] : Contient des informations sur tout les articles de la base répliquée.
• [dbo].[MSreplication_subscriptions] : Contient des informations sur la configuration de la subscription (l’abonné).
• [dbo].[MSsubscription_properties]  : Contient des informations sur les propriétés de la subscription (l’abonné).

Les Jobs
Sur le serveur abonné, on trouve un seul job qui correspond à l’agent de distribution. Le job prend le nom de la forme : <Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<SubscriptionDatabase>-<GUID>
Le job exécute un script T-SQL de la forme :
-Publisher ‘publisher_serverName’  -PublisherDB [replication_databaseName] -Publication [replication_Name] -Distributor [distributor_serverName] -SubscriptionType 1 -Subscriber [subscriber_serverName] -SubscriberSecurityMode 1 -SubscriberDB [subscriber_databaseName]    -Continuous
Le job s’exécute sans arrêt. Si le job est arrêté, la distribution s’arrête et la base de donnée de l’abonné n’est plus synchronisée avec la base de données source.
Si l’arrêt de l’agent dépasse la durée de 72 heures (dans notre configuration), l’abonné est considéré comme expiré, et il risque d’être nettoyé par le job de la réplication (REPL-Subscription Cleanup) qui s’exécute sur le serveur de publication.
Dans le prochain article, je vais essayer d’ aborder comment monitorer la réplication transactionnelle, rajouter/supprimer un article de la réplication et comment supprimer complètement une réplication.