La Réplication Transactionnelle SQL Server (1)

LinkedIn 0
Twitter
Facebook 0
Google+ 0

Introduction :

L’architecture de la réplication SQL Server repose sur les différentes techniques qui permettent de copier et de distribuer les données des différents objets d’une base de données vers une autre.

SQL Server propose trois types de réplication :

  • La réplication transactionnelle (Transactional Replication).
  • La réplication de capture instantanée (Snapshot Replication).
  • La réplication par fusion (Merge Replication).

La réplication transactionnelle est généralement utilisée dans un environnement serveur à serveur pour répondre aux besoins suivants :

  • Redondance des données sur un ou plusieurs serveurs qui se trouvent sur le même site ou sur des sites différents.
  • Utilisation du serveur abonné comme un serveur de reporting et de lecture seule.
  • Consolidation des données sur un serveur central en provenance de plusieurs sites distants.
  • Besoin d’avoir un serveur abonné mis à jour en quasi temps réel avec le serveur de publication.
  • La volumétrie et l’activité de la base est très importante, dans ce cas la réplication par capture instantanée peut prendre longtemps et verrouille à chaque fois l’accès aux données sur la base de publication.
  • Réplication vers un serveur non-SQL Server, comme Oracle ou Sybase.

Principe de fonctionnement :

  • La réplication transactionnelle commence en général par une capture instantanée des objets et des données de la base à publier.
  • Une fois la première capture effectuée, toutes les modifications effectuées sur les schémas et les données de la base de publication sont transmis au fur et à mesure (presque en temps réel) aux différents abonnés.
  • Les changements et les transactions produites sur le serveur de publication sont appliqués dans le même ordre sur les abonnés, donc la cohérence des données est garantie par le mécanisme de la réplication.

Schéma d’architecture d’une réplication transactionnelle :

 URL : https://technet.microsoft.com/fr-fr/library/ms151706(v=sql.105).aspx

La réplication transactionnelle est effectuée par différents agents :

  • Agent de capture instantanée (snapshot)
  • Agent de lecture du journal de transaction
  • Agent de distribution.

L’agent de capture instantanée (snapshot) :

  • Génère un snapshot (ensemble de fichiers) contenant les schémas et les données des objets de la base à publier (tables, vues, fonctions, procédures, …).
  • Les fichiers générés sont déposés dans un répertoire partagé (répertoire snapshot) accessible par tous les serveurs qui participent à la réplication (serveur de publication, serveur de distribution et les différents abonnés).
  • Enregistre les informations de synchronisation dans la base de distribution qui se trouve sur le serveur de distribution ou sur le serveur de publication si celui-ci joue les deux rôles à la fois.
  • Bref, c’est lui qui génère la copie des données qui sera utilisée pour initialiser la base à mettre en réplication sur les différents abonnés.
  • Le job de l’agent est planifié en « one shot » car il est utilisé uniquement pour l’initialisation ou pour la réinitialisation des données.

L’agent de lecture du journal :

  • Surveille le journal des transactions de la base publiée (l’activité de la base sur le serveur de publication), et copie les transactions qui doivent être répliquées depuis le journal des transactions dans la base de distribution.
  • La base de distribution joue donc le rôle d’une file d’attente (stockage et distribution des transactions).
  • Le job de l’agent de lecture du journal tourne sans arrêt sur le serveur de publication. Si le job est arrêté, la réplication des données s’arrête.

L’agent de distribution :

  • Se charge de la copie des données de la capture instantanée (le snapshot d’initialisation) et les différentes transactions stockées au fur et à mesure dans la base de distribution vers les abonnés.
  • Le job de cet agent tourne sans arrêt comme l’agent de lecture du journal. Si le job est arrêté, la réplication s’arrête aussi.
  • Le job de l’agent peut être configuré sur le serveur de distribution (publication/distribution si le serveur de publication joue les deux rôles) ou sur l’abonné selon le mode de configuration de la réplication (push ou pull).

Mise en place d’une réplication transactionnelle :
Pré-requis :

  • Le module de réplication doit être installé sur tous les serveurs qui participent à la réplication (publication, distribution et les abonnés). SQL Server dispose d’une procédure qui permet de le vérifier facilement (sp_MS_replication_installed).
  • Deux comptes de service (Active Directory) : un compte pour le service SQL Server Agent et l’autre pour le fonctionnement de la réplication (il est déconseillé d’utiliser le même compte).
  • Création d’un partage pour la capture instantanée (snapshot).
  • Droits de lecture/écriture sur le partage et les droits NFS sur le dossier à partager pour le compte de service SQL Agent.
  • Le rôle serveur « sysadmin » pour le compte de service.

Création de la réplication avec T-SQL :
Déclaration des variables :

Initialisation des variables :

Installation et configuration de la distribution :

Configuration de la publication :

Configuration des objets de la base à répliquer :

  • Ajout de toutes les tables utilisateurs de la base. Vous pouvez utiliser le script suivant pour générer le script de toutes les tables utilisateurs de la base. Il suffit ensuite d’exécuter le script généré pour ajouter toutes les tables.

Remarque : Toutes les tables à répliquer doivent posséder une clé primaire (la réplication transactionnelle ne fonctionne pas avec les heap tables).

  • Ajout de toutes les vues utilisateurs. Même principe que le script pour les tables :

  • Ajout de toutes les procédures utilisateurs :

  • Ajout de toutes les fonctions :

Création de la subscription (l’abonné) en mode « pull » :

  • Déclaration de la subscription au niveau du serveur de publication (publisher) :

  • Création de la subscription sur le serveur abonné (subscriber) : Script à exécuter sur l’abonné.

La mise en place dans notre réplication est terminée.
==> La suite dans le prochain article.
 
 

LinkedIn 0
Twitter
Facebook 0
Google+ 0

2 réflexions sur “La Réplication Transactionnelle SQL Server (1)”

  1. Ping : Réplication Transactionnelle SQL Server (3) - ArKZoYd

  2. Ping : La Réplication Transactionnelle SQL Server (2) - ArKZoYd

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *