[SQL Server] Réplication : fonctionnement de la réplication transactionnelle

Ce petit billet offre un aperçu du fonctionnement de la réplication transactionnelle. Pour une présentation générale de la réplication, vous pouvez aller ici.

Présentation rapide de la réplication de snapshot et scénarii d’utilisation

La réplication transactionnelle, solution la plus souvent utilisée, est plutôt appropriée dans chacun des cas usuels suivants :

  • Propagation des mises-à-jour incrémentielles vers les abonnés, au fur et à mesure qu’elles s’exécutent.
  • La réplication nécessite l’accès aux états intermédiaires des données. Par exemple, si une ligne d’une vue (ou d’une table) change un certain nombre de fois, la réplication transactionnelle permet à une application de répondre à chaque modification (par exemple activer un déclencheur), et pas simplement au résultat final des mises-à-jour de la ligne.
  • L’éditeur a un volume très élevé d’activités de mises-à-jour.
  • La réplication entre instances OLTP et OLAP (ou de reporting SSRS) est souhaitée[1]. Exemple simpliste : une partie d’une base de données OLTP est répliquée vers une autre base OLTP utilisée pour des traitements OLAP, et une autre partie vers une autre base OLTP utilisée pour des traitements SSRS.
  • La réplication entre une instance SQL Server et une instance hétérogène comme Oracle est souhaitée[2].

Comment SQL Server gère la réplication transactionnelle ?

Pour mieux comprendre comment le Snapshot Agent travaille exactement, allez ici.

SQL Server traite, via un ensemble de mécanismes appelés SQL Server Replication Agents, la réplication transactionnelle[3].

Dans le cas de la réplication transactionnelle, il existe principalement les agents suivants : Snapshot Agent, Log Reader Agent et Distribution Agent[4]. Le Snapshot Agent ne pose pas de verrou sur l’ensemble des articles de l’éditeur. Ce qui signifie que l’accès à la base éditrice se fait via une approche concurrentielle.

La garantie de la consistance transactionnelle se fait comme suit :

  1. Quand le processus de snapshot débute, un verrou est posé sur tous les articles (ou plus exactement, les tables concernées) de la base de publication, puis un « marqueur » indiquant le début du snapshot est écrit dans le journal des transactions de la base de publication. Puis sitôt le marqueur écrit, les articles sont déverrouillés, et les fichiers BCP sont ensuite générés sans verrou en place (hormis éventuellement quelques « courts » verrous sur les pages et lignes traitées).
  2. A la fin du snapshot, un autre marqueur est écrit dans le journal des transactions afin d’indiquer la fin du traitement. Puis le Log Reader Agent va analyser le journal des transactions de la base de publication de façon à ce que tous les changements transactionnels validés ayant eu lieu durant le snapshot au sein des articles traités et qui ont été archivés dans ledit journal entre les 2 marqueurs soient identifiés. Puis l’identification faite, le Log Reader Agent va ensuite les copier vers la base de distribution.
  3. Au moment de l’application du snapshot vers l’abonné, les tables concernées de l’abonné sont supprimées puis recrées à partir du script DDL[5] généré durant le snapshot de la base de publication et placé dans le dossier de snapshot. Puis les données issues des fichiers BCP (toujours dans le dossier de snapshot) sont appliquées aux articles concernés : les tables répliquées, en l’occurrence. Et durant tout le processus celles-ci seront verrouillées du côté de l’abonné.
  4. Puis sitôt le snapshot via BCP fini, le Distribution Agent va analyser les marqueurs fournis par le Log Reader Agent afin de vérifier si les changements « marqués » ont été pris en compte par le Snapshot Agent durant le snapshot. Si ce n’est pas le cas, il applique les modifications associées aux abonnés. On parle alors de réplication incrémentielle ou transactionnelle.

Par la suite, chaque fois que des données devront être répliquées, SQL Server utilisera le point 4 sans avoir à refaire le snapshot.

Voici un schéma simple de son fonctionnement global :

Les flèches roses indiquent les accès en écriture tandis que les flèches vertes représentent les accès en lecture. On notera que le Distribution Agent peut être placé soit sur une instance dédiée, soit sur la même instance que l’éditeur (si la base de distribution est placée sur la même instance que l’éditeur et que le mode de récupération est push), soit sur la même instance que l’abonné (si le mode de récupération est pull).

Notons que dans le cas de la réplication transactionnelle pour Oracle (Oracle Publishing)
nativement intégré dans l’outil de réplication de SQL Server (version minimale 2005 ; édition Enterprise), des tables sont créées dans la base Oracle de publication pour chaque table publiée, ainsi que des triggers configurés pour récupérer tout changement effectué et les stocker dans lesdites tables. Par la suite, le Log Reader Agent récupère les changements en question pour les incorporer dans la base de distribution SQL Server pour qu’ensuite le Distributor Agent utilise lesdits changements pour les appliquer aux abonnés.

Pour aller plus loin…

Dans un prochain billet, nous traiterons de la mise-en-place de la réplication transactionnelle.


[2]Il est possible d’utiliser le mécanisme de réplication adapté pour Oracle (Oracle Publishing). Cela est possible depuis la version 2005 de SQL Server : http://msdn.microsoft.com/en-us/library/ms151229.aspx. Mais toutefois, on rappelle que cette fonctionnalité est sur le point de devenir obsolète, et que Microsoft recommande l’usage de packages SSIS à la place.

[3]Remarquons que ce n’est pas le moteur OLTP de SQL Server qui gère la réplication nativement. En effet, la réplication est un outil externe. Ce choix s’explique par la facilité à pouvoir faire intervenir plusieurs systèmes de bases de données en réplication.

[4]Dans le cas d’une réplication bidirectionnelle s’ajoute le Queue Reader Agent qui est utilisé pour appliquer les changements effectués au sein d’un abonné vers l’éditeur.

[5]Data Definition Language : requête(s) SQL de création/modification de structure de données.

Publicités

Un commentaire sur “[SQL Server] Réplication : fonctionnement de la réplication transactionnelle

  1. mehdoini Abdallah dit :

    thanks

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s