[SQL Server] Réplication : présentation générale

par Mohamed A. Cherif

Ce billet présente la réplication sous SQL Server et différents concepts généraux qui lui sont associés.

Qu’est-ce que la réplication ?

La réplication est une technologie permettant de copier et de distribuer des données et objets d’un serveur à l’autre, puis de synchroniser ces bases de données afin d’assurer la cohérence. En d’autres termes, cela permet d’augmenter la disponibilité, de permettre une distribution des requêtes clients, ou de mettre à disposition une base de données à des clients distants en limitant les risques réseau par une décentralisation des données.

Apparue depuis SQL Server 2000, la réplication est à la fois un mécanisme de haute-disponibilité et de l’informatique distribuée qui fait intervenir 3 types de bases de données (dont les objets répliqués sont appelés « articles ») :

  • Un éditeur, qui est une base de données de publication.
  • Un ou plusieurs abonnés liés à l’éditeur, et qui sont des bases de données de souscription.
  • Une base de distribution, qui est utilisée pour la récupération et l’application de changements d’une base à l’autre.
Notons qu’une base de distribution peut être placée sur une instance dédiée ou bien l’instance de l’éditeur.

Pour finir, sous SQL Server, il existe deux modes de récupération :

  • La récupération en push : le serveur de publication (éditeur) pousse (« push ») les données vers les abonnés. Ce mode est intéressant principalement pour les serveurs ou clients fixes avec des connexions permanentes, et donc non-sujets à des déconnexions intempestives.
  • La récupération en pull : les abonnés demandent à l’éditeur les données. Ce mode est à utiliser pour des serveurs distants ayant des connexions non fixes (RNIS par exemple) ou pour les clients nomades (les portables) susceptibles d’être régulièrement déconnectés.

Les différents types de réplication

SQL Server offre principalement 3 types de réplication, correspondant, chacun, à des besoins différents :

  • Réplication de capture instantanée (ou de snapshot), généralement utilisée en guise de pré-requis pour les autres types de réplication. est plus appropriée quand les modifications de données sont significatives mais occasionnelles.
  • Réplication de fusion (ou merge), usuellement appropriée dans les cas suivants :
    • Plusieurs abonnés (bases de données liées à une base de données SQL Server locale) peuvent mettre à jour les mêmes données à différents moments et propager ces modifications à l’éditeur et à d’autres abonnés.
    • Des abonnés doivent recevoir des données, apporter des modifications en étant offline et synchroniser ultérieurement ces modifications avec l’éditeur et d’autres abonnés partageant la même session de réplication.
    • Chaque abonné requiert une partition de données différentes (dans le cas d’un load balancing, par exemple).
    • Il y a un risque potentiel de conflits de mises-à-jour ou alors, qu’on doit avoir la possibilité de les détecter et de les résoudre de manière proactive par fusion des opérations de mises-à-jour.
    • La réplication nécessite le résultat des modifications des données au lieu de devoir accéder aux états intermédiaires des données répliquées. Par exemple, si une ligne change un certain nombre de fois sur un abonné avant qu’il se synchronise avec l’éditeur, la ligne ne change qu’une seule fois sur le serveur de publication pour refléter le résultat final des modifications (c’est-à-dire prise en compte du dernier changement).
  • Réplication transactionnelle, solution la plus souvent utilisée, et qui 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].

A noter qu’un 4ème type de réplication existe : la réplication peer-to-peer où chaque base de données fait à la fois office d’éditeur et d’abonné.

Lumière sur…

… les éditions de SQL Server supportant la réplication

A partir de SQL Server 2005, les éditions Express et Workgroup peuvent supporter la réplication… Mais toutefois, les limitations sont les suivantes :

  • Pour l’Express : seulement en tant qu’abonné.
  • Pour la Workgroup : possibilité d’agir en tant qu’éditeur, mais avec seulement un nombre limité d’abonnés. Peut agir en tant qu’abonné.

Les éditions Business Intelligence (à partir de SQL Server 2012), Standard et Enterprise supportent toutes les possibilités de la réplication. La seule différence entre les 2 éditions est que l’Enterprise a un argument en plus : le support de la réplication native d’une base de données Oracle (qui joue le rôle d’éditeur pour des abonnés SQL Server) appelée Orace Publishing. Argument qui, néanmoins, est moins probant à l’heure actuelle, l’Oracle Publishing étant sur le point de devenir obsolète après SQL Server 2012[3] à l’instar de la réplication dite « hétérogène »[4].

Vous pouvez avoir un aperçu de chaque édition ici : http://msdn.microsoft.com/fr-fr/library/cc645993.aspx#Replication.

… sur les versions multiples de SQL Server dans une topologie de réplication

Au sein d’une topologie de réplication, plusieurs versions de SQL Server (2000 et plus) peuvent intervenir, peu importe leur architecture (32 bits ou 64 bits). Toutefois la version du distributeur ne doit pas être inférieure à celle de l’éditeur.

Notons que quand une réplication est effectuée entre plusieurs bases de versions différentes, la limitation principale se situe au niveau de la plus ancienne version utilisée. Par exemple, si un distributeur est de version 2008, un éditeur de version 2005 et un abonné de version 2000, alors la réplication sera limitée aux fonctionnalités générales de SQL Server 2000 pour des raisons de compatibilité. C’est donc pour cette raison qu’il est recommandé d’utiliser une version suffisamment récente pour chaque base à faire intervenir dans une topologie de réplication.
Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms143241.aspx.
Prérequis et considérations essentiels sur la réplication

La réplication nécessite le respect de nombreux pré-requis. Il s’agit tout particulièrement des contraintes suivantes :

  • Tout ce qui a été dit dans la section précédente sur les éditions et versions.
  • Pour rebondir sur le point précédent, rappelons que le distributeur ne doit pas être de version inférieure à l’éditeur.
  • Être membre du rôle sysadmin afin d’être en mesure de mettre-en-place la réplication.
  • Créer un login SQL[5] sur chaque instance concernée par la réplication (en tant qu’éditeur ou abonné) avec le rôle db_owner sur chaque base de données à répliquer. Il est toujours possible d’utiliser un login SQL membre du rôle sysadmin, mais cela n’est pas recommandé pour des raisons évidentes de sécurité.
  • S’assurer, d’ailleurs, que le login SQL soit membre de la liste d’accès à la publication, si jamais vous comptez utiliser une réplication avec le mode de récupération pull (les abonnés accèdent à l’éditeur, et non l’inverse). Pour ce faire, dans l’explorateur d’objets de SSMS, sous le menu Réplication,faites un clic-droit sur votre publication, puis allez dans ses propriétés. Et de là, allez dans la section Liste d’accès à la publication et ajoutez votre login.
  • Chaque table ou vue à répliquer doit également être accompagnée de tout autre objet qu’elle référence (i.e., cas des tables liées via clés étrangères à des tables répliquées, cas des vues,…).
  • La bande-passante doit être suffisamment grande pour supporter l’envoi des données à répliquer d’un serveur à l’autre.
  • La partition des bases de données concernées doit être suffisamment volumineuse. Et cela est tout aussi important pour la base de distribution qui est destinée à stocker les transactions marquées à répliquer (cas de la réplication transactionnelle) ou les changements récupérés par des triggers de réplication de fusion.
  • Le dossier de snapshot doit être accessible en lecture/écriture par le compte d’exécution du SQL Agent de chaque instance concernée. On pourrait l’appeler SQL Replication et le placer soit sur une partition dédiée (si, par exemple, la quantité des fichiers générés par le Snapshot Agent est très ou trop volumineuse), soit avec d’autres fichiers, du moment qu’il y a assez d’espace disque.
  • Chaque instance concernée par la réplication doit être membre d’une même forêt Active Directory.
  • Si un trop grand nombre d’éditeurs sont configurés pour utiliser le même distributeur (topologie avec distribution centralisée), des goulots d’étranglements peuvent intervenir. Il serait, à ce moment-là, recommandé de dédier chaque distributeur à un seul éditeur.
  • Ne répliquer que les articles nécessaires.
  • Dans le cas de la réplication de fusion, notez que SQL Server crée automatiquement, sur la base de publication concernée, une colonne de type Uniqueidentifier pour chaque table à répliquer. Cela impacte le fonctionnement des requêtes ne sélectionnant pas explicitement les colonnes à traiter (cas des requêtes « étoilées »). Cela signifie que quelques adaptations et tests, niveau développement, seront nécessaires avant toute mise-en-production d’une réplication de fusion. Soulignons, néanmoins, qu’en cas de suppression d’une publication, les colonnes Uniqueidentifier sont automatiquement supprimées, sauf si elles ont été créées manuellement.
  • Les opérations de masse de type bcp ou BULK-INSERT sur les tables publiées ne sont automatiquement pas prises en compte par la réplication de fusion, dans la mesure où elles ne font pas « réagir » les merge triggers (utiles pour la détection de tout changement à répertorier au distributeur). La solution, dans ce cas, serait de :
Pour aller plus loin…

Dans de prochains billets, nous aborderons les différents types de réplication et modes opératoires de mise-en-place.

Références

[1]Plus de détails ici : http://msdn.microsoft.com/fr-fr/library/ms151784.aspx.

[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]Microsoft recommande l’utilisation de packages SSIS à la place, pour la réplication Oracle/SQL Server : http://msdn.microsoft.com/fr-fr/library/ms143550.aspx.

[4]Il s’agit de la configuration d’abonnés non-SQL Server (principalement Oracle et IBM DB2). Cette fonctionnalité sera obsolète après SQL Server 2012, et à remplacer par l’usage de packages SSIS.

[5]Il serait plus commode d’utiliser un login SQL plutôt qu’un login Windows NT pour permettre à un agent de réplication d’accéder d’une instance à l’autre, selon la topologie de la réplication concernée.

Laisser un commentaire