[SQL Server] Mini-FAQ sur la réplication

Ce billet offre une série de questions/réponses sur la réplication.

  • A partir de quelle version de SQL Server est supportée la réplication ?

A partir de la version 2000 de SQL Server.

  • Quelles sont les éditions de SQL Server qui supportent la réplication et leurs limitations ?
Express/Web Standard/Business Intelligence Workgroup Enterprise/Developer/Datacenter
Réplication de fusion Abonné seulement. Oui. Jusqu’à 25 abonnés. Oui.
Réplication transactionnelle/ Réplication de snapshot Abonné seulement. Non. Jusqu’à 25 abonnés. Oui.
Réplication peer-to-peer Non. Non. Non. Oui.
Oracle Publishing Non. Non. Non. Oui.

A noter que pour SQL Server 2000, seule l’édition Enterprise est supportée pleinement. De plus, les éditions Datacenter et Business Intelligence n’apparaissent respectivement qu’à partir des versions 2008 R2 et 2012 de SQL Server.
  • Puis-je répliquer des bases de données SQL Server de versions différentes ?

Oui, mais avec toutefois de nombreuses restrictions existent. Voir ici ou, pour encore plus de détails, le lien suivant: http://msdn.microsoft.com/en-us/library/ms143241.aspx.

A noter que le distributeur doit être de version supérieure ou égale à celle de l’éditeur.

  • Est-il nécessaire de créer, au préalable, la base abonnée avant de la répliquer ?

Non. En effet, durant la mise-en-place de la publication souhaitée, il est possible d’indiquer, à SQL Server, de créer automatiquement la base abonnée sur l’instance abonnée cible, durant l’étape d’ajout d’abonnés :

Il est recommandé, pour des raisons d’optimisation, de pré-allouer convenablement la taille minimale de la future base à créer à la même taille que celle de la future base de publication.

  • Durant la phase de snapshot, les tables à répliquer sont-elles verrouillées ?

Tout dépend du type de réplication :

  • Réplication de snapshot : un verrou partagé est posé sur l’ensemble des tables durant toute la durée du snapshot.
  • Réplication transactionnelle : un verrou partagé est brièvement posé sur l’ensemble des tables le temps de l’écriture d’un marqueur avant libération.
  • Réplication de fusion : aucun verrou n’est posé.
  • Dans quel contexte réel les modes de récupération push et pull sont les mieux adaptés ?

Le mode push est à utiliser si vous souhaitez que l’éditeur se charge lui-même de « pousser » les données vers ses abonnés. Le mode pull doit être utilisé si vos abonnés sont susceptibles d’être fréquemment offline (cas des applications mobiles, par exemple).

  • Y’a-t-il un mode de récupération spécifique pour la réplication ?

Non. Par contre, en mode SIMPLE ou BULK-LOGGED, le journal des transactions d’une base de publication peut journaliser entièrement certaines opérations, notamment lors de la face de snapshot de la réplication transactionnelle, par exemple.

  • Est-il possible d’utiliser des publications (éditeurs) multiples et différents types de publications pour une même base de distribution ?

Oui, tout à fait. Il n’y a pas de nombre limite de publications par base de distribution, par contre, un détail doit être pris en compte : toutes les publications d’une même instance doivent utiliser la même base de distribution.

  • Toute activité doit-elle être obligatoirement stoppée sur une base de données quand celle-ci est publiée ?

Non. Mais soulignons toutefois que l’utilisation d’un snapshot (qui est utilisé pour n’importe quel type de réplication) est potentiellement bloquant selon le type de réplication. Autrement dit : il est recommandé de ne lancer un snapshot qu’en période de faible activité/de peu d’utilisation.

  • Quand un abonné est-il disponible ?

Un abonné n’est disponible qu’après l’application du snapshot sur lui. Même si elle peut être accessible avant le lancement du snapshot, il est recommandé de ne pas l’utiliser durant l’exécution de cette opération. Utilisez le Replication Monitor pour connaître l’état du snapshot.

  • Qu’advient-il si un snapshot se déroule en même temps qu’un agent de synchronisation (Distribution Agent ou Merge Agent) ?

Aucune erreur ne sera levée, mais toutefois :

  • Si les agents de synchronisation sont utilisés périodiquement ou à la demande, leur travail sera annulé si aucun snapshot n’est (encore) disponible.
  • Si les agents de synchronisation sont configurés pour tourner en continu, leur travail ne sera validé qu’après la fin du snapshot.
  • Pourquoi la réplication de fusion ajoute une colonne supplémentaire pour chaque table répliquée ?

La réplication de fusion utilise, pour chaque table répliquée, une colonne de type Uniqueidentifier afin de pouvoir mieux capturer tout changement. Sachant qu’une telle colonne est appelée rowguid et possède une propriété ROWGUIDCOL activée (indiquant que la colonne est utilisée), si la table associée est supprimée, la colonne rowguid sera désactivée, ce qui conduira à sa suppression.

Notons toutefois que s’il s’agit d’une colonne créée manuellement, elle ne sera pas supprimée par la réplication de fusion.

  • Un objet (table, vue, procédure stockée,…) peut-il être publié dans différentes publications ?

Oui, cela est tout à fait possible. Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms152559.aspx. Les restrictions s’appliquent généralement aux publications transactionnelles dont la base de publication est modifiable par les abonnés, aux publications transactionnelles peer-to-peer et aux publications de fusion.

  • La réplication chiffre-t-elle les données ?

Non. De plus, la réplication ne prend pas en compte les objets chiffrés (i.e., procédures stockées encryptées, master keys…), ce qui signifie qu’il faudra donc le faire manuellement.

Pour en savoir plus sur la sécurité par rapport à la réplication, référez-vous au lien suivant : http://msdn.microsoft.com/en-us/library/ms151228.aspx.

  • Les logins et mots-de-passe sont-ils répliqués ?

Non. Il faudra les créer manuellement, ou bien utiliser un package SSIS pour les transférer d’un éditeur à un abonné.

  • Qu’adviendrait-il si l’on créé un login ou des permissions sur une base abonné qui n’existent pas sur une base de publication et que l’on lance ensuite un snapshot ?

Toutes les modifications sur l’abonné non présentes sur l’éditeur seront perdues. Mais toutefois, il est possible de faire suivre le snapshot par un script rejouant les modifications perdues sur l’abonné. Par exemple : accédez aux propriétés de la publication, puis allez dans la section Instantané pour ajouter votre script supplémentaire.

  • Puis-je tronquer une table via TRUNCATE TABLE ?

Non. En effet, TRUNCATE TABLE est une opération non-journalisée (pour la réplication transactionnelle) et ne peut pas pousser des triggers à se lancer (pour la réplication de fusion). De ce fait, une telle opération est interdite car ne peut pas être capturée par les agents de réplication.

  • La réplication peut-elle impacter la taille des journaux de transactions ?

La réplication transactionnelle le peut, les autres types de réplication non.

  • Puis-je renommer ou déplacer des fichiers de bases de données concernées par la réplication ?

Avant MSSQL 2005, un detach/attach était nécessaire. Depuis MSSQL 2005, cela est possible, mais il est recommandé d’utiliser la procédure stockée système sp_replicationdboption (cf : http://msdn.microsoft.com/en-us/library/ms188769.aspx).

  • Les modifications DDL (Data Definition Language)
    d’objets d’une base de publication peuvent-elles être répliquées vers un abonné ?

Oui, d’autant plus que l’option de réplication de changements de schémas est activée par défaut (voir propriétés de publication concernée). Mais toutefois, cela n’est possible que pour principalement les objets suivants (ajout, suppression et mise-à-jour) : tables, vues, procédures stockées, fonctions, triggers (mais seulement dans le cas de modifications de type DML (Data Manipulation Languages). Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms151870.aspx.

Il est recommandé de faire vos modifications en T-SQL plutôt que via l’interface graphique d’EM ou de SSMS. De plus, il est recommandé de faire vos modifications sur la base de publication plutôt que sur la base abonnée.

Et par ailleurs, les contraintes doivent être explicitement nommées sous peine de voir MSSQL générer des noms de contraintes différents entre l’éditeur et l’abonné.

  • Comment puis-je ajouter, modifier ou supprimer une colonne d’une table répliquée ?

Utilisez une approche T-SQL : ALTER TABLE … ADD/ALTER/DROP COLUMN sur l’éditeur. Et dans le cas des versions inférieures à MSSQL 2005, utilisez sp_repladdcolumn (cf : http://msdn.microsoft.com/en-us/library/ms189464.aspx) et sp_repldropcolumn (cf : http://msdn.microsoft.com/en-us/library/ms190489.aspx).

Mais quoiqu’il advienne, il est recommandé de faire vos modifications via T-SQL et non l’interface graphique d’EM ou SSMS, cette dernière ayant la fâcheuse tentation à recréer l’objet ce qui peut poser problème pour la réplication.

Notons toutefois que si vous souhaitez renommer une colonne répliquée, il vous faudra d’abord supprimer la colonne concernée de la réplication, faire votre renommage, puis la rajouter à la réplication.

  • Comment puis-je supprimer un objet répliqué ?

Sur l’éditeur, supprimez l’article de la base de publication correspondant à l’objet ciblé, en allant dans les propriétés de la base de publication (section Articles) ou via T-SQL en utilisant sp_droparticle (cf : http://msdn.microsoft.com/en-us/library/ms173832.aspx ) ou sp_dropmergearticle (cf : http://msdn.microsoft.com/en-us/library/ms179926.aspx)

  • Puis-je répliquer un index ajouté ou modifié sur une base de publication vers un abonné ?

Non. En fait, les indexes peuvent être créés et/ou modifiés sur un éditeur ou un abonné sans que cela n’ait un impact spécial sur la réplication. Toutefois, leur création ou modification n’est pas prise en compte par les agents de synchronisation, ce qui signifie qu’il faudra :

  • Soit s’y prendre manuellement (sur l’abonné).
  • Soit lancer un nouveau snapshot pour qu’il réinitialise les abonnés ciblés.

Notons que par défaut, la création d’une clé primaire créé implicitement un index en cluster, chose supportée par la réplication.

  • Puis-je répliquer les changements de clés primaires ?

A l’instar des indexes, cela n’est pas possible (même avec un ALTER TABLE). Il faudra le faire soit manuellement du côté de l’éditeur et de l’abonné après avoir supprimé (puis rajouté) l’article concerné (ce qui nécessitera, par la suite, un nouveau snapshot), soit via un nouveau snapshot (i.e., modification seulement du côté de l’éditeur, puis lancement d’un snapshot). Par contre, il est possible de répliquer la création de clés primaires via ALTER TABLE… ADD CONSTRAINT.

  • Puis-je répliquer la création et/ou les changements de clés étrangères ?

Oui. D’ailleurs, si l’option de réplication de clés étrangères n’est pas activée (voir propriétés des articles) mais qu’un ajout ou modification de clés étrangères est orchestré via un ALTER TABLE, lesdits changements sont répliqués.

  • Puis-je réindexer un index utilisé dans une table répliquée ?

Oui, bien sûr. Mais faites de même sur l’abonné, les changements d’indexes n’étant pas répliqués.

  • Comment puis-je savoir si les données des abonnés sont synchronisées avec celles de l’éditeur ?

Via la validation de données (voir ici).

  • Comment savoir le nombre de transactions en attente de réplication ?

Utilisez l’onglet Commandes non-distribuées du Replication Monitor ou alors la procédure stockée sp_replmonitorsubscriptionpendingcmds.

  • Quels sont les changements de propriétés d’articles ou de publication qui nécessitent un nouveau snapshot ?

Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms152745.aspx.

  • La réplication peut-elle être combinée avec le log shipping et/ou le database mirroring ?

Oui. Plus de détails ici :

  • La réplication peut-elle être utilisée avec le failover clustering ?

Oui, sans problème.

  • Puis-je sauvegarder/restaurer une base de données concernée par une réplication ?

Oui. Mais toutefois, si le but est de restaurer une base répliquée sur une autre instance, la publication ou abonnement concernée par cette base doit être recréée sur la nouvelle instance ciblée. Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms151152.aspx.

  • Les données de l’éditeur ne sont pas répliquées vers l’abonné. Quelles sont les raisons possibles ?

En vrac :

  • Soit le snapshot est obsolète ou n’a jamais été appliqué.
  • Soit un des agents de réplication est en erreur (voir le Replication Monitor ou le SQL Agent Monitor ou l’état de la synchronisation ou du snapshot).
  • Les données sont supprimées par un utilisateur tiers ou un trigger.

D’autres réponses peuvent exister, comme le fait que la table concernée est filtrée (i.e., les changements à appliquer sont exclus par le filtre).

  • Comment puis-je auditer le temps de latence de la réplication transactionnelle en détail ?

Utilisez un Token Tracer via le Replication Monitor (version de SQL Server supérieure ou égale à 2005). Voir ici.

  • Puis-je répliquer des données de SQL Server vers des bases hétérogènes, supposons Oracle ?

Oui. Par ailleurs, pour Oracle, il est possible d’utiliser directement l’Oracle Publisher (version Enterprise de SQL Server 2005 minimum, seulement).

Notons toutefois que Microsoft recommande de ne plus utiliser la réplication hétérogène à partir de SQL Server 2012, mais de plutôt privilégier l’usage de packages SSIS (cf : http://msdn.microsoft.com/fr-fr/library/ms143550.aspx).

Publicités

5 commentaires sur “[SQL Server] Mini-FAQ sur la réplication

  1. Thomas dit :

    Bonjour,
    J’apprécie beaucoup les tutos de ce blog étant un fan inconditionnel de Microsoft; je voudrais savoir s’il est possible de faire de la réplication de base de données entre Oracle et SQL Server; mon soucis actuel est de récupération des données provenant d’oracle dans ma base SQL Server 2005 Enterprise et aussi dans le sens inverse de publier des données vers Oracle (11G); quelle est la meilleure manière de procéder?
    Merci d’avance et meilleures salutations

    • Bonsoir Thomas,

      Il est, bien sûr, possible d’effectuer une réplication entre Oracle et SQL Server.

      Pour ce faire, tu peux utiliser Oracle Publishing (édition Enterprise au minimum), qui fonctionne sur le même principe que la réplication transactionnelle, mais, en plus du Log Reader Agent, avec des triggers de capture de changements sur chaque table Oracle, ou simplement la réplication transactionnelle. Mais je recommanderais plutôt SSIS, qui te permettra de créer des data flows qui te chargeront les données Oracle vers SQL Server (et/ou vice-versa), avec des composants comme OLE DB Source, OLE DB Destination,… Le lancement du package SSIS créé pourra se planifier via le SQL Agent.

      Quand j’aurai un peu de temps, j’aborderai le cas de la réplication de données Oracle avec SSIS (ADDENDUM: voir ici ou ).

      @+!

      M.

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