Partager via

Réplication, suivi des modifications et capture de données modifiées - groupes de disponibilité Always On

S'applique à :SQL Server

SQL Server La réplication, la capture de données modifiées (CDC) et le suivi des modifications (CT) sont pris en charge sur Groupes de disponibilité Always On. Groupes de disponibilité Always On aide à fournir des fonctionnalités haute disponibilité et de récupération de base de données supplémentaires.

Vue d’ensemble de la réplication avec des groupes de disponibilité

Redirection de l’éditeur

Lorsqu'une base de données publiée repose sur la technologie Groupes de disponibilité Always On, le serveur de distribution qui offre un accès d'agent à la base de données de publication est configuré avec des entrées redirected_publishers. Ces entrées redirigent la combinaison serveur de publication/base de données initialement configurée, en se servant d'un nom d'écouteur de groupe de disponibilité pour se connecter au serveur de publication et à la base de données de publication. Les connexions établies via le nom de l’écouteur du groupe de disponibilité échouent lors du basculement. Lorsque l’agent de réplication redémarre après le basculement, la connexion est automatiquement redirigée vers le nouveau serveur principal.

Dans un groupe de disponibilité (AG), une base de données secondaire ne peut pas être un éditeur. La republication est prise en charge uniquement lorsque la réplication transactionnelle est associée à Groupes de disponibilité Always On.

Si une base de données publiée est membre d'un groupe de disponibilité et que le serveur de publication est redirigé, il doit être redirigé vers un nom d'écouteur de groupe de disponibilité associé au groupe de disponibilité. Il peut ne pas être redirigé vers un nœud explicite.

Notes

Après le basculement vers un réplica secondaire, le Moniteur de réplication ne peut pas ajuster le nom de l’instance de publication de SQL Server et continue d’afficher les informations de réplication sous le nom de l’instance principale d’origine de SQL Server. Après le basculement, un jeton de suivi ne peut pas être entré à l’aide du Moniteur de réplication, mais un jeton de suivi entré sur le nouvel éditeur à l’aide de Transact-SQL est visible dans le Moniteur de réplication.

Modifications générales apportées aux agents de réplication pour prendre en charge les groupes de disponibilité

Trois agents de réplication ont été modifiés pour prendre en charge Groupes de disponibilité Always On. L'agent de lecture du journal, ainsi que les agents d'instantané et de fusion ont été modifiés pour interroger la base de données de distribution pour le serveur de publication redirigé et pour utiliser le nom d'écouteur de groupe de disponibilité retourné, si un serveur de publication redirigé était déclaré, pour se connecter au serveur de publication de base de données.

Par défaut, lorsque les agents interrogent le serveur de distribution pour déterminer si l’éditeur d’origine a été redirigé, l’adéquation de la cible ou de la redirection actuelle est vérifiée avant de renvoyer l’hôte redirigé vers l’agent. Ce comportement est recommandé. Toutefois, si le démarrage de l’agent se produit fréquemment, la surcharge associée à la procédure stockée de validation peut être considérée comme trop coûteuse. Un nouveau commutateur de ligne de commande, BypassPublisherValidation, a été ajouté au lecteur de journaux, à la capture instantanée et aux agents de fusion. Lorsque le commutateur est utilisé, le serveur de publication redirigé est retourné immédiatement à l'agent et l'exécution de la procédure stockée de validation est ignorée.

Les échecs retournés de la procédure stockée de validation sont consignés dans les journaux d'historique de l'agent. Ces erreurs dont la gravité est supérieure ou égale à 16 entraînent l’arrêt des agents. Certaines fonctions de reprise ont été intégrées aux agents afin de gérer la déconnexion attendue d'une base de données publiée en cas de basculement vers un nouveau principal.

Modifications de l’agent de lecture du journal

L’agent de lecture du journal a les modifications suivantes.

  • Cohérence de la base de données répliquée

    Lorsqu’une base de données publiée est membre d’un groupe de disponibilité, par défaut, le lecteur de journal ne traite pas les enregistrements de journal qui n’ont pas déjà été renforcés sur tous les réplicas secondaires du groupe de disponibilité. Cela permet de s’assurer que lors du basculement, toutes les lignes répliquées sur un abonné sont également présentes dans le nouveau principal.

    Lorsque le serveur de publication n’a que deux réplicas de disponibilité (un réplica principal et un secondaire) et qu’un basculement se produit, le réplica principal d’origine reste en panne, car le lecteur du journal ne progresse pas tant que toutes les bases de données secondaires ne sont pas renvoyées en ligne ou jusqu’à ce que les réplicas secondaires défaillants soient supprimés du groupe de disponibilité. Le lecteur de journal, maintenant exécuté sur la base de données secondaire, ne se poursuit pas, car le groupe de disponibilité ne peut pas renforcer les modifications apportées à une base de données secondaire. Pour permettre au lecteur de journal de poursuivre et d’avoir toujours la possibilité de reprise d’activité, supprimez le réplica principal d’origine du groupe de disponibilité en utilisant ALTER AVAILABITY GROUP <group_name> REMOVE REPLICA. Ajoutez ensuite un nouveau réplica secondaire au groupe de disponibilité.

  • Indicateur de trace 1448

    L'indicateur de trace 1448 permet au lecteur du journal de réplication de continuer à avancer même si les répliques secondaires asynchrones n'ont pas officiellement confirmé la réception d'une modification. Même avec cet indicateur de trace activé, le lecteur de journal attend toujours les réplicas secondaires synchrones (ils peuvent devenir en mode de validation asynchrone, afin que le lecteur de journal puisse avancer). Le lecteur du journal ne dépasse pas le nombre minimal de réplicas secondaires synchrones. Cet indicateur de trace s'applique à l'instance de SQL Server, et pas simplement à un groupe de disponibilité, à une base de données de disponibilité ou à une instance de l'agent de lecture de journal. Cet indicateur de trace doit être activé sur l’instance de l’éditeur. Elle prend effet immédiatement sans redémarrage. Il peut être activé d'avance ou lorsque le réplica secondaire asynchrone échoue.

Procédures stockées prenant en charge les groupes de disponibilité

  • sp_redirect_publisher

    La procédure stockée sp_redirect_publisher permet de spécifier un serveur de publication redirigé pour une combinaison existante serveur de publication/base de données. Si la base de données du serveur de publication appartient à un groupe de disponibilité, le serveur de publication redirigé correspond au nom d'écouteur de groupe de disponibilité.

  • Sp_get_redirected_publisher

    La procédure stockée sp_get_redirected_publisher permet aux agents de réplication d’interroger un serveur de distribution pour déterminer si une combinaison serveur de publication/base de données dispose d’un serveur de publication redirigé défini. Cette procédure stockée remplit deux objectifs. En premier lieu, elle permet à l'agent de déterminer si le serveur de publication d'origine a été redirigé. Ensuite, il peut également lancer une procédure stockée de validation exécutée sur le serveur de distribution (sp_validate_redirected_publisher) qui vérifie la pertinence du nœud cible de la redirection pour servir d’éditeur pour la base de données nommée.

    Pour exécuter cette procédure stockée, l’appelant doit être membre du rôle serveur sysadmin , du rôle de base de données db_owner de la base de données de distribution, ou être membre d’une liste d’accès à une publication pour une publication définie associée à la base de données du serveur de publication.

  • sp_validate_redirected_publisher

    Cette procédure stockée tente de valider que le serveur de publication actuel est capable d'héberger la base de données publiée. Elle peut être appelée à tout moment pour vérifier que l'hôte actuel de la base de données publiée est en mesure de prendre en charge la réplication.

  • sp_validate_replicate_hosts_as_publishers

    Bien qu’il soit utile pour les agents de s’assurer que le principal actuel peut fonctionner comme serveur de publication de réplication pour une base de données d’éditeur, une fonctionnalité de validation plus générale est nécessaire pour établir la validité d’une topologie de réplication entière sur une base de données de groupe de disponibilité. La procédure sp_validate_replica_hosts_as_publishers stockée est conçue pour remplir ce besoin.

    Cette procédure stockée est toujours exécutée manuellement. L'appelant doit être sysadmin sur le serveur de distribution, dbowner de la base de données de distribution ou un membre de la liste d'accès à la publication d'une publication de la base de données du serveur de publication. En outre, l'identifiant de connexion de l'appelant doit être un identifiant valide pour tous les hôtes de réplica de groupe de disponibilité, et disposer de privilèges de sélection sur la base de données de disponibilité associée à la base de données du serveur de publication.

Capture des changements de données

Les bases de données activées pour la capture de données modifiées (CDC) peuvent utiliser Groupes de disponibilité Always On afin d’assurer que non seulement la base de données reste disponible en cas de défaillance, mais également que les modifications apportées aux tables de base de données continuent d’être surveillées et déposées dans les tables de modifications de capture de données modifiées. L’ordre dans lequel les groupes de disponibilité CDC et Always On sont configurés n’est pas important. Les bases de données activées par capture de données modifiées peuvent être ajoutées à des groupes de disponibilité Always On, et les bases de données membres d’un groupe de disponibilité peuvent être activées pour la capture de données modifiées. Dans les deux cas, toutefois, la configuration de la capture de données modifiées est toujours effectuée sur le réplica principal actuel ou prévu. La capture de données modifiées utilise l’agent de lecture du journal et présente les mêmes limitations que celles décrites dans la section modifications de l’agent de lecture du journal plus haut dans cet article.

  • Collecte des modifications pour la capture de données modifiées sans réplication

    Si la capture modifiées est activée pour une base de données, mais que la réplication ne l’est pas, le processus de capture utilisé pour collecter les modifications du journal et les déposer dans les tables de modification modifiées de capture de données s’exécute sur l’hôte CDC en tant que son propre travail SQL Agent.

    Pour reprendre la récolte des modifications après un basculement, la procédure stockée sp_cdc_add_job doit être exécutée au niveau du nouveau principal pour créer le travail de capture local.

    L'exemple suivant crée le travail de capture.

    EXECUTE sys.sp_cdc_add_job @job_type = 'capture';

  • Collecte des modifications pour la capture de données modifiées avec la réplication

    Si la capture de données modifiées et la réplication sont activées pour une base de données, le lecteur de journal gère le flux des tables de modifications de capture de données modifiées. Dans ce cas, les techniques utilisées par la réplication pour utiliser des groupes de disponibilité Always On garantissent que les modifications continuent d’être collectées à partir du journal et déposées dans les tables de modification cdc après le basculement. Aucune opération supplémentaire ne doit être effectuée pour la capture de données modifiées dans cette configuration pour que les tables de modifications soient remplies.

  • Nettoyage de la capture de données modifiées

    Pour s’assurer que le nettoyage approprié se produit au niveau de la nouvelle base de données principale, un travail local de nettoyage doit toujours être créé. L'exemple suivant crée le travail de nettoyage.

    EXECUTE sys.sp_cdc_add_job @job_type = 'cleanup';

    Notes

    Vous devez créer les travaux au niveau du nouveau réplica principal après le basculement. Les travaux de capture des changements de données s'exécutant au niveau de l'ancienne base de données principale doivent également être désactivés lorsque la base de données locale devient une base de données secondaire. Si le réplica d’origine redevient principal, vous devez réenabler les travaux de capture de données modifiées sur le réplica de ce réplica. Pour désactiver et activer des travaux, utilisez l’option @enabled de sp_update_job. Pour plus d’informations sur la création de travaux CDC, consultez sys.sp_cdc_add_job.

  • Ajout de rôles CDC à un réplica de base de données principal

    Lorsqu’une table est activée pour la capture de données modifiées, il est possible d’associer un rôle de base de données à l’instance de capture. Si un rôle est spécifié, l'utilisateur souhaitant utiliser les fonctions table de capture de données modifiées pour accéder à des modifications pour la table doit non seulement avoir un accès choisi aux colonnes de table faisant l'objet d'un suivi, mais doit également être membre du rôle nommé. Si le rôle spécifié n’existe pas encore, le rôle est créé. Lorsque les rôles de base de données sont automatiquement ajoutés à une base de données primaire dans un groupe de disponibilité, les rôles sont également propagés aux bases de données secondaires du groupe de disponibilité.

  • Applications clientes accédant aux données modifiées modifiées et aux groupes de disponibilité

    Les applications clientes qui utilisent les fonctions table (TVF) ou des serveurs liés pour accéder aux données de la table de modifications ont également besoin de pouvoir localiser un hôte de capture de données modifiées approprié après le basculement. Le nom d'écouteur de groupe de disponibilité correspond au mécanisme fourni par Groupes de disponibilité Always On pour permettre à une connexion, en toute transparence, d'être redirigée vers un hôte différent. Une fois qu’un nom d’écouteur de groupe de disponibilité est associé à un groupe de disponibilité, il est disponible pour être utilisé dans les chaînes de connexion TCP. Deux scénarios de connexion différents sont pris en charge par le biais du nom d'écouteur de groupe de disponibilité.

    • L’un garantit que les demandes de connexion sont toujours dirigées vers le réplica principal actuel.
    • L’autre garantit que les demandes de connexion sont dirigées vers un réplica secondaire en lecture seule.

    Utilisée pour rechercher un réplica secondaire en lecture seule, une liste de routage en lecture seule doit également être définie pour le groupe de disponibilité. Pour plus d’informations sur le routage de l’accès aux secondaires lisibles, consultez Configurer le routage en lecture seule pour un groupe de disponibilité Always On.

    Notes

    Il existe un certain délai de propagation associé à la création d’un nom d’écouteur de groupe de disponibilité et à son utilisation par les applications clientes pour accéder à un réplica de base de données de groupe de disponibilité.

    Utilisez la requête suivante pour déterminer si un nom d'écouteur de groupe de disponibilité a été défini pour le groupe de disponibilité qui héberge une base de données CDC. La requête retourne le nom de l’écouteur du groupe de disponibilité s’il a été créé.

    SELECT dns_name
FROM sys.availability_group_listeners AS l
     INNER JOIN sys.availability_databases_cluster AS d
         ON l.group_id = d.group_id
WHERE d.database_name = N'MyCDCDB';

  • Redirection du chargement de la requête vers un réplica secondaire lisible

    Bien que dans de nombreux cas, une application cliente souhaite toujours se connecter au réplica principal actuel, ce n’est pas la seule façon d’utiliser des groupes de disponibilité Always On. Si un groupe de disponibilité est configuré pour prendre en charge des réplicas secondaires accessibles en lecture, les données modifiées peuvent également être collectées à partir des nœuds secondaires.

    Lorsqu'un groupe de disponibilité est configuré, l'attribut ALLOW_CONNECTIONS associé à SECONDARY_ROLE est utilisé pour spécifier le type d'accès secondaire pris en charge. S’il est configuré comme ALL, toutes les connexions à la base de données secondaire sont autorisées, mais seules celles nécessitant un accès en lecture seule réussissent. S’il est configuré comme READ_ONLY, il est nécessaire de spécifier l’intention en lecture seule lors de la connexion à la base de données secondaire afin que la connexion réussisse. Pour plus d’informations, consultez Configurer l’accès en lecture seule à un réplica secondaire d’un groupe de disponibilité Always On.

    La requête suivante peut être utilisée pour déterminer si l'intention en lecture seule est nécessaire pour se connecter à un réplica secondaire accessible en lecture.

    SELECT g.name AS AG,
          replica_server_name,
          secondary_role_allow_connections_desc
FROM sys.availability_replicas AS r
     INNER JOIN sys.availability_groups AS g
          ON r.group_id = g.group_id
WHERE g.name = N'MY_AG_NAME';

    Le nom d'écouteur de groupe de disponibilité ou le nom du nœud explicite peut être utilisé pour rechercher le réplica secondaire. Si le nom de l’écouteur du groupe de disponibilité est utilisé, l’accès est dirigé vers n’importe quel réplica secondaire approprié.

    Lorsqu’il sp_addlinkedserver est utilisé pour créer un serveur lié pour accéder au serveur secondaire, le paramètre @datasrc est utilisé pour le nom de l’écouteur du groupe de disponibilité ou le nom de serveur explicite, et le paramètre @provstr est utilisé pour spécifier l’intention en lecture seule.

    EXECUTE sp_addlinkedserver
    @server = N'linked_svr',
    @srvproduct = N'SqlServer',
    @provider = N'MSOLEDBSQL',
    @datasrc = N'AG_Listener_Name',
    @provstr = N'ApplicationIntent=ReadOnly',
    @catalog = N'MY_DB_NAME';

  • Accès client aux données modifiées modifiées et aux connexions de domaine

    En général, vous devez utiliser des connexions de domaine pour l’accès client aux données modifiées résidant dans des bases de données membres de groupes de disponibilité. Pour garantir un accès continu aux données modifiées après le basculement, l’utilisateur du domaine a besoin de privilèges d’accès sur tous les hôtes prenant en charge les réplicas de groupe de disponibilité. Si un utilisateur de base de données est ajouté à une base de données dans un réplica principal et que l'utilisateur est associé à une connexion de domaine, l'utilisateur de base de données est propagé aux bases de données secondaires et continue d'être associé à la connexion de domaine spécifiée. Si le nouvel utilisateur de base de données est associé à une connexion d’authentification SQL Server, l’utilisateur sur les bases de données secondaires est propagé sans connexion. Lorsque la connexion d'authentification SQL Server associée peut être utilisée pour accéder aux données modifiées au niveau du serveur principal où l'utilisateur de la base de données a été défini à l'origine, ce nœud est le seul où l'accès est possible. La connexion d’authentification SQL Server ne peut pas accéder aux données d’une base de données secondaire ni à partir de nouvelles bases de données primaires autres que la base de données d’origine où l’utilisateur de la base de données a été défini.

  • Désactivation de la capture de données modifiées

    Si vous devez désactiver la capture de données modifiées (CDC) sur une base de données qui fait partie d’un groupe de disponibilité et que vous êtes sur SQL Server 2016 SP2 ou version ultérieure, vous n’avez pas besoin d’effectuer des étapes supplémentaires pour la troncation automatique des journaux. Si vous utilisez une version antérieure à SQL Server 2016 SP2 et que vous désactivez la capture de données modifiées sur une base de données qui fait partie d’un groupe de disponibilité, vous devez implémenter l’une des étapes suivantes pour empêcher le blocage de la troncation des journaux après la désactivation de la capture de données modifiées :

    • Redémarrez le service SQL Server sur chaque instance de réplica secondaire.

    • Supprimez la base de données de toutes les instances de réplica secondaire du groupe de disponibilité et ajoutez-les à chaque instance de réplica du groupe de disponibilité par amorçage automatique ou manuel.

Suivi des modifications

Une base de données activée pour le suivi des modifications (CT) peut faire partie d’un groupe de disponibilité. Aucune autre configuration n’est nécessaire. Les applications clientes de suivi des modifications qui utilisent les fonctions table CDC (TVFs) pour accéder aux données modifiées ont besoin de la possibilité de localiser le réplica principal après le basculement. Si l’application cliente se connecte via le nom de l’écouteur du groupe de disponibilité, les demandes de connexion sont toujours dirigées de manière appropriée vers le réplica principal actuel.

Les données de suivi des modifications doivent toujours être obtenues à partir du réplica principal. Une tentative d’accès aux données modifiées à partir d’un réplica secondaire entraîne l’erreur suivante :

Msg 22117, Level 16, State 1, Line 1

Pour les bases de données membres d’un réplica secondaire (autrement dit, pour les bases de données secondaires), le suivi des modifications n’est pas pris en charge. Comme alternative à l’exécution de requêtes de suivi des modifications sur le réplica principal, vous pouvez créer un instantané de base de données d’une base de données de groupe de disponibilité à partir du réplica secondaire, puis l’utiliser pour interroger les données modifiées. Un instantané de base de données est une vue statique en lecture seule d’une base de données SQL Server (la base de données source), de sorte que les données de suivi des modifications dans l’instantané de base de données sont du moment où l’instantané a été pris sur la base de données du groupe de disponibilité à partir du réplica secondaire.

Notes

Lorsqu’un basculement se produit sur une base de données avec le suivi des modifications activé, le temps de récupération sur le nouveau réplica principal peut prendre plus de temps que d’habitude, car le suivi des modifications nécessite un redémarrage complet de la base de données.

Conditions préalables, restrictions et considérations relatives à l’utilisation de la réplication

Cette section décrit les considérations à prendre en compte pour déployer la réplication avec Groupes de disponibilité Always On, y compris les conditions préalables requises, les restrictions et les recommandations.

Prérequis

  • Lors de l’utilisation de la réplication transactionnelle, si la base de données de publication se trouve dans un groupe de disponibilité, le serveur de publication et le serveur de distribution doivent exécuter au moins SQL Server 2012 (11.x). L'abonné peut utiliser un niveau inférieur de SQL Server.

  • Lors de l'utilisation de la réplication de fusion, si la base de données de publication se trouve dans un groupe de disponibilité :

    • Abonnement par émission de données : le serveur de publication et le serveur de distribution doivent exécuter au moins SQL Server 2012 (11.x).

    • Abonnement par extraction : le serveur de publication, le serveur de distribution et les bases de données de l’abonné doivent s’exécuter sur au moins SQL Server 2012 (11.x). Cela est dû au fait que l'Agent de fusion sur l'abonné doit comprendre la façon dont un groupe de disponibilité peut basculer sur son serveur secondaire.

  • Les instances de serveur de publication répondent à toutes les conditions préalables requises pour participer à un groupe de disponibilité. Pour plus d’informations, consultez Conditions préalables, restrictions et recommandations pour les groupes de disponibilité Always On.

Restrictions

Combinaisons de réplication prises en charge sur Groupes de disponibilité Always On:

Réplication Serveur de publication Serveur de distribution 1 Abonné
Transactionnelle Oui

Remarque : n’inclut pas la prise en charge de la réplication transactionnelle bidirectionnelle et réciproque.		 Oui Oui
Égal à égal2 Oui Oui 3 Oui
Fusionner Oui No No
Instantané Oui No Oui
Abonnements pouvant être mis à jour : pour la réplication transactionnelle No No No

1 La base de données du serveur de distribution n’est pas prise en charge pour une utilisation avec la mise en miroir de bases de données.

2 Nécessite SQL Server 2019 CU 13 ou version ultérieure.

3 Nécessite SQL Server 2019 CU 17 ou version ultérieure.

Considérations

  • La base de données de distribution n’est pas prise en charge pour une utilisation avec la mise en miroir de bases de données, mais elle est prise en charge avec des groupes de disponibilité Always On soumis à certaines limitations. Pour plus d’informations, consultez Configurer le groupe de disponibilité de distribution. La configuration de la réplication est couplée à l’instance SQL Server où le serveur de distribution est configuré ; Par conséquent, la base de données de distribution ne peut pas être mise en miroir ni répliquée. Il est également possible de fournir une haute disponibilité pour le serveur de distribution à l’aide d’un cluster de basculement SQL Server. Pour plus d’informations, consultez instances de cluster de basculement Always On (SQL Server).

  • Le basculement d’abonné vers une base de données secondaire, s’il est pris en charge, est une procédure manuelle pour les abonnés de réplication de fusion. La procédure est, pour l'essentiel, identique à la méthode utilisée pour le basculement vers une base de données de l'abonné en miroir. Les abonnés de réplication transactionnelle n’ont pas besoin d’une gestion spéciale lors de la participation à des groupes de disponibilité Always On. Les abonnés doivent exécuter SQL Server 2012 (11.x) ou une version ultérieure pour pouvoir participer à un groupe de disponibilité. Pour plus d’informations, consultez Les abonnés de réplication et les groupes de disponibilité Always On (SQL Server)

  • Les métadonnées et les objets qui existent en dehors de la base de données ne sont pas propagés aux réplicas secondaires, notamment les connexions, les travaux, les serveurs liés. Si vous voulez faire figurer les métadonnées et les objets dans la nouvelle base de données principale après le basculement, vous devez les copier manuellement. Pour plus d’informations, consultez Gérer les connexions pour les travaux à l’aide de bases de données dans un groupe de disponibilité Always On.

Groupes de disponibilité distribués

L’éditeur ou la base de données de distribution dans un groupe de disponibilité ne peut pas être configuré dans le cadre d’un groupe de disponibilité distribué. La base de données du serveur de publication dans un groupe de disponibilité et la base de données de distribution dans un groupe de disponibilité nécessitent toutes deux un point de terminaison d’écouteur pour une configuration et une utilisation appropriées. Toutefois, il n’est pas possible de configurer un point de terminaison d’écouteur pour un groupe de disponibilité distribué.

Réplication

Capture des changements de données

Suivi des modifications

Contenu connexe

  • Last updated on