[SQL Server] Réplication : mise en place de la réplication transactionnelle avec SQL Server 2008 (R2)

Ce billet présente comment mettre-en-place la réplication transactionnelle avec SQL Server 2008 (R2) – globalement le même mode opératoire que pour les versions 2012 et 2014 -, via SQL Server Management Studio (SSMS). Pour comprendre les différents concepts associés à la réplication transactionnelle, vous pouvez aller ici, et pour les prérequis, vous pouvez aller là.
A noter qu’avant de commencer, il faudra configurer un distributeur comme vu ici.

Contexte de nos besoins

Pour nos exemples de mise-en-place, nous allons configurer un mécanisme de réplication transactionnelle :

• Réplication entre une base MaBase1 et une autre base MaBase2 en prenant en compte l’ensemble des tables (juste leurs enregistrements dont la colonne MaTable1_Desc n’est pas nulle), vues et procédures stockées de ces 2 bases.
  
• Exemple de besoin dans le monde réel : rafraîchissement toutes les X heures d’une base de données (utilisée pour du load balancing)
  à partir d’une partie d’une autre afin de « soulager » cette dernière.
  
• Planification des agents de réplication souhaitée pour notre exemple :
  
  • Snapshot Agent : tous les jours, à 1h du matin.
    
  • Log Reader Agent : planification par défaut (i.e., au démarrage du SQL Agent). Il n’est pas nécessaire de le planifier périodiquement : à partir du moment où il se lance une fois (par défaut, au moment du démarrage du SQL Agent), il travaille en arrière-plan en continu via un programme externe appelé LogReader.exe. En fait, le job idoine est en perpétuelle exécution durant toute la durée de fonctionnement du SQL Agent.
    
  • Distribution Agent : chaque jour, toutes les heures.
    

Le choix des planifications des agents des différents types de réplication sont, ici, bien sûr des exemples. En temps normal, il faudra les adapter en fonction de divers facteurs : les besoins en termes de synchronisation, la charge sur les serveurs, etc…

Du côté des structures de données :

• MaBase1 possède 4 tables MaTable1, MaTable2, MaTable3 et MaTable4, 3 vues MaVue1, MaVue2 et MaVue3, 3 procédures stockées usp_SP1, usp_SP2 et usp_SP3.
  
• MaBase2 possède 2 tables MaTable1 et MaTable2, une vue MaVue2 et une procédure stockée usp_SP2.
  

Du côté de la localisation des bases de données :

• MaBase1 est placée sur l’instance SRV-SQL01 qui fera figure d’éditeur (et de distributeur).
  
• MaBase2 est sur SRV-SQL02.
  

Notons que : Le mode de récupération choisi pour la réplication transactionnelle sera push (les agents de réplication seront placés sur le distributeur de façon à ce que les répliques soient « poussées » vers l’abonné). Le distributeur de chaque réplication sera placé sur la même instance que l’éditeur concerné. Et supposons que : Les bases MaBase1 et MaBase2 ont déjà été créées (même s’il n’est pas obligatoire de le faire pour la base abonnée, l’Assistant de configuration pouvant très bien s’en charger), et la base MaBase1 remplie. Les réplications seront unidirectionnelles (de l’éditeur vers l’abonné).

Modus operandi de mise-en-œuvre de la réplication transactionnelle

Comme pour tout type de réplication, l’étape préliminaire, après la configuration du distributeur, est de créer un snapshot. De ce fait, pour la mise-en-place de la réplication transactionnelle entre MaBase1 (SRV-SQL01) et MaBase2 (SRV-SQL02), suivez les étapes suivantes :

• Au sein de l’explorateur d’objets de SSMS, faites un clic-droit sur Réplication, puis sélectionnez Publication… au sein du menu contextuel Nouveau ou alors, faites directement un clic-droit sur Publications locales et sélectionnez Nouvelle publication…
  
• Lancement de l’assistant de création de la publication :
  

  
  

Une fenêtre de bienvenue s’ouvrira :

• Sélection de la base MaBase1 comme base de publication :
  

  
  

• Choix du type de réplication : réplication transactionnelle :
  

  
  

• Choix des articles suivant les besoins et définition de leurs propriétés :
  

  
  

  Rappelons qu’à l’instar de ce qui a été évoqué dans le cas de la réplication de snapshot, il est possible d’éventuellement définir les propriétés de chaque article (ou d’un ensemble d’articles de même type). Exemple pour l’ensemble des vues (il suffit de mettre en surbrillance n’importe quelle vue sélectionnée) :
  

  
  

  Un clic sur Définir les propriétés de tous les articles de Vue ouvre la boîte modale suivante :
  

  
  

  On choisit de copier les triggers et propriétés étendues, tout en laissant les autres propriétés intactes :
  

  
  

  Et pour les procédures stockées, les propriétés étendues tout en laissant les autres propriétés intactes.
  

  Si, par ailleurs, vous accédez aux propriétés de l’ensemble des tables à répliquer, vous obtiendrez cet exemple de fenêtre modale :
  

  
  

  Choisissez de copier les indexes non-cluster, les clés étrangères, les spécifications de valeurs par défaut, les triggers et contraintes de validation.
  

  
  

  Notons toutefois qu’en règle générale, les propriétés par défaut sont largement suffisantes dans de très nombreux cas de réplication. D’autant plus que chaque copie nécessitant un snapshot, cela reviendrait comme à recréer manuellement les structures à copier dans les bases abonnées.
  

En clair : cela ne signifie en aucun cas que la réplication transactionnelle copiera les indexes, les contraintes,… : c’est le snapshot qui le fera (s’il est lancé manuellement ou automatiquement).

Une bonne pratique générale consiste à créer manuellement ces structures de données (via génération de scripts sous SSMS sur l’éditeur, par exemple) sur les bases abonnées concernées de façon à ne pas avoir à lancer un nouveau snapshot si celui-ci s’avère long et coûteux.

On peut également copier les autres types d’indexes (filtrés, XML, spatiaux,…) et éventuellement (mais ce n’est pas le cas dans notre exemple) les autorisations d’accès aux objets (assurez-vous, dans ce cas, que les bons logins et utilisateurs existent sur les abonnés concernés).

• Lisez l’avertissement de l’assistant qui parle du référencement des articles :
  

  
  

  Ce qu’il faut savoir c’est que s’il existe un article référençant un objet (table, vue, index…) non sélectionné dans la liste des articles à répliquer, alors ledit objet doit obligatoirement être également sélectionné en tant qu’article (ce qui nécessitera un retour à l’étape précédente). Dans notre exemple précis, seule notre vue référence d’autres objets.
  

• Filtre des lignes des tables à répliquer suivant les besoins :
  

  
  

  En cliquant sur Ajouter…, on accède à la fenêtre modale suivante :
  

  
  

  L’objectif étant de ne répliquer que les enregistrements avec description non-nulle des tables MaTable1 et MaTable2 de la future base de publication MaBase1, il faut donc, dans la zone dédiée à l’instruction de filtrage, rédiger la ligne de commande T-SQL suivante :
  

SELECT <published_columns> FROM [dbo].[MaTable1] WHERE [MaTable1_Desc] IS NOT NULL

Ou pour plus de contexte :

Soulignons que la partie <published_columns> est non-modifiable, et que seule la clause WHERE est paramétrable. Voici le résultat après clic sur le bouton OK :

Par la suite, faites de même pour MaTable2 (dont la structure est arbitrairement identique).

• Planification du travail du Snapshot Agent :
  

  
  

  Par défaut, quand la planification est activée, le Snapshot Agent est lancé toutes les heures. Supposons que l’on veuille le lancer une fois par jour, à 1h du matin, il suffira d’aller dans Modifier, puis d’effectuer la planification comme suit :
  

  
  

  Après clic sur OK :
  

  
  

  On choisit bien sûr de lancer un snapshot à la fin de la configuration, à moins que l’on préfère attendre la prochaine exécution du job associé.
  

On rappelle que la planification périodique du Snapshot Agent est souvent peu utile dans le cas d’une réplication transactionnelle, cette dernière travaillant de façon incrémentielle. Il est, de ce fait, possible de n’utiliser le job d’exécution du Snapshot Agent que si nécessaire (et manuellement).

• Configuration de la sécurité du Snapshot Agent et du Log Reader Agent :
  

  
  

  On choisit de laisser le Snapshot Agent s’exécuter avec le compte d’exécution du SQL Agent. En outre, on spécifie que le login SQL toto (préalablement créé sur l’instance hébergeant la base de publication) sera utilisé pour se connecter à l’éditeur (ou instance de la base de publication).
  

  Pour ce qui est du Log Reader Agent, on le laisse s’exécuter dans le même contexte d’exécution que le Snapshot Agent :
  

  
  

• Choix du comportement de l’assistant post-configuration :
  

  
  

  On n’oublie pas de choisir de générer le script de configuration de l’éditeur après configuration par l’assistant. Un tel script pourra être utile pour toute reconfiguration de l’éditeur en cas de sinistre ou réutilisation ailleurs.
  

• Spécification du chemin d’accès du fichier de configuration de l’éditeur :
  

  
  

  Le chemin d’accès choisi et le nom du script sont, bien sûr, arbitraires. Il faudra conventionnellement placer le fichier de configuration de la publication dans un dossier dédié.
  

• Spécification du nom de la publication, validation et lancement :
  

  
  

  Appelons notre publication MaBase1_Transactional.
  

• Confirmation du bon déroulement de la configuration :
  

Au sein de l’explorateur d’objets de SSMS, on peut noter la présence de la publication fraîchement configurée pour l’instance SRV-SQL01 dans la liste des publications locales :

En plaçant la souris sur la publication, on peut lire sa description.

Du côté des jobs, on y trouve le job d’exécution du Log Reader Agent (SRV-SQL01-MaBase1-1) et celui du Snapshot Agent (SRV-SQL01-MaBase1-MaBase1_Transactional-3) de la réplication transactionnelle que l’on vient de créer :

Il reste désormais à créer l’abonné MaBase4. Pour ce faire, toujours au sein de l’instance de publication SRV-SQL01 :

• Lancement de l’assistant de configuration de nouveaux abonnements :
  

  
  

Une fenêtre de bienvenue s’ouvrira :

• Sélection de la publication MaBase1_Transactional préalablement créée sur le serveur de publication :
  

  
  

• Choix du mode de récupération :
  

  
  

  On opte pour le mode push.
  

• Sélection de l’abonné (serveur de souscription et base abonnée MaBase4) :
  

  
  

  Après ajout de l’abonné (instance SRV-SQL02) et sélection de la bonne base MaBase2 :
  

  
  

• Configuration de la sécurité du Distribution Agent :
  

On choisit de l’exécuter dans le contexte du compte d’exécution du SQL Agent de l’instance où la base de distribution est hébergée (SRV-SQL01). En outre, on spécifie un login SQL de connexion vers l’instance où se trouve l’abonné MaBase2 (SRV-SQL02).

La configuration de la sécurité du Distribution Agent donne l’aperçu suivant :

• Planification du lancement du Distribution Agent :
  

  
  

  On choisit de définir la planification de l’agent. On opte pour un lancement toutes les heures, sachant que le Snapshot Agent défini plus tôt sera lancé une fois par jour, à 1h du matin :
  

  
  

  On valide les modifications :
  

  
  

• Initialisation de l’abonnement :
  

  
  

  On peut choisir d’initialiser l’abonnement immédiatement ou lors de la prochaine synchronisation (i.e., lancement du Distribution Agent). On rappelle qu’un snapshot a été configuré plus haut.
  

• Choix du comportement de l’assistant post-configuration de l’abonné :
  

  
  

  On n’oublie pas de préciser à l’assistant de générer le script de configuration de l’abonné. Un tel script pourra être utile pour toute reconfiguration de l’abonné en cas de sinistre ou réutilisation ailleurs.
  

• Spécification du chemin d’accès et du nom du fichier de configuration de l’abonné :
  

  
  

• Validation de la configuration et lancement de la mise-en-place :
  

  
  

• Confirmation du bon déroulement de la configuration :
  

  
  

Au terme de nos configurations, au sein de SSMS, on peut souligner :

• Sur l’instance SRV-SQL01 (éditeur) la présence de jobs dédiés au Snapshot Agent, au Log Reader Agent et, aussi, au Distribution Agent :
  

  
  

  Le job SRV-SQL01-MaBase1-MaBase1_Transactional-SRV-SQL02-12 a été automatiquement créé. Pour résumer :
  

  • SRV-SQL01-MaBase1-MaBase1_Transactional-1 effectue un snapshot pour l’ensemble des abonnés de la publication MaBase1_Snapshot. Il s’agit du job d’exécution du Snapshot Agent.
    
  • SRV-SQL01-MaBase1-1 effectue un analyse du journal des transactions de la base de publication MaBase1 afin de déterminer les changements à répliquer pour ensuite les placer dans la base de distribution. Il s’agit du job d’exécution du Log Reader Agent. On rappelle qu’il n’est pas nécessaire de le planifier : à partir du moment où il se lance une fois (par défaut, au moment du démarrage du SQL Agent), il travaille en arrière-plan en continu. La preuve :
    

Le job idoine tourne durant toute la durée de fonctionnement du SQL Agent :

• SRV-SQL01-MaBase1-MaBase1_Transactional-SRV-SQL02-12 effectue une distribution des données vers l’abonné MaBase2 de l’instance SRV-SQL02 qui est membre de la publication MaBase1_Transactional. Il s’agit du job d’exécution du Distribution Agent.
  

• Sur l’instance SRV-SQL02 (abonné), la présence de notre abonné :
  

    Ou alors, sur l’éditeur (SRV-SQL01), en dépilant la publication idoine :

Pour aller plus loin…

Nous avons pu voir que la mise-en-place de la réplication transactionnelle avec SQL Server 2008 (R2) n’était pas insurmontable. Nous aborderons, très prochainement, quelques éléments d’audit et opérations avancées sur la réplication.