Décharger une charge de travail en lecture seule vers un réplica secondaire d’un groupe de disponibilité Always On

S'applique à :SQL Server

Les fonctions secondaires actives de Groupes de disponibilité Always On incluent la prise en charge de l’accès en lecture seule à un ou plusieurs réplicas secondaires (réplicas secondaires accessibles en lecture). Une réplique secondaire lisible peut être soit en mode de disponibilité avec validation synchrone, soit en mode de disponibilité avec validation asynchrone. Une réplique secondaire lisible permet un accès en lecture seule à toutes ses bases de données secondaires. Toutefois, les bases de données secondaires accessibles en lecture ne sont pas configurées en lecture seule. Elles sont dynamiques. Chaque base de données secondaire change à mesure que les modifications apportées à la base de données primaire sont répercutées sur elle. Pour une réplique secondaire typique, les données des bases de données secondaires, y compris les tables durables optimisées en mémoire, sont disponibles presque en temps réel. De plus, les index de recherche en texte intégral sont synchronisés avec les bases de données secondaires. En général, la latence des données entre une base de données primaire et la base de données secondaire correspondante n'est que de quelques secondes.

Les paramètres de sécurité définis pour les bases de données primaires sont également appliqués dans les bases de données secondaires. Cela inclut les utilisateurs, les rôles de base de données et les rôles d'applications avec leurs autorisations respectives, ainsi que le chiffrement transparent des données s'il est activé sur la base de données primaire.

Les groupes de disponibilité Always On prennent également en charge la redirection des demandes de connexion avec intention de lecture vers un réplica secondaire lisible (routage en lecture seule). Pour plus d’informations sur le routage en lecture seule, consultez Utilisation d’un écouteur pour se connecter à un réplica secondaire en lecture seule (routage en lecture seule).

Avantages

L’acheminement des connexions en lecture seule vers des réplicas secondaires lisibles offre les avantages suivants :

• Décharge vos charges de travail en lecture seule secondaires de votre réplica principal, qui conserve ses ressources pour vos charges de travail critiques. Si vous avez une charge de travail de lecture critique ou une charge de travail qui ne peut tolérer la latence, vous devez l’exécuter sur l’instance principale.

• Améliore votre retour sur investissement pour les systèmes qui hébergent des réplicas secondaires accessibles en lecture.

En outre, les réplicas secondaires accessibles en lecture assurent une prise en charge fiable des opérations en lecture seule, comme suit :

• Les statistiques temporaires automatiques sur la base de données secondaire accessible en lecture optimisent les requêtes en lecture seule sur les tables sur disque. Pour les tables optimisées en mémoire, les statistiques manquantes sont créées automatiquement. Cependant, aucune mise à jour automatique des statistiques obsolètes n'a lieu. Vous devrez mettre manuellement à jour les statistiques sur la réplique principale. Pour plus d’informations, consultez Statistiques des bases de données d’accès en lecture seule, plus loin dans cette rubrique.

• Les charges de travail en lecture seule pour les tables basées sur disque utilisent le versionnement des lignes pour éliminer les blocages dus à la contention sur les bases de données secondaires. Toutes les requêtes exécutées sur les bases de données secondaires sont automatiquement associées au niveau d’isolation des transactions d’instantané, même lorsque d’autres niveaux d’isolation des transactions sont explicitement définis. De plus, tous les indicateurs de verrouillage sont ignorés. Cela élimine les conflits entre lecteurs et rédacteurs.

• Les charges de travail en lecture seule pour les tables durables optimisées en mémoire accèdent aux données de la même manière que sur la base de données primaire, à l’aide des procédures stockées natives ou de l’interopérabilité SQL présentant les mêmes limitations de niveau d’isolation des transactions (consultez Niveaux d’isolation du moteur de base de données). La charge de travail de création de rapports ou les requêtes en lecture seule en cours d'exécution sur le réplica principal peuvent être exécutées sur le réplica secondaire sans procéder à la moindre modification. De la même manière, une charge de travail de création de rapports ou des requêtes en lecture seule en cours d'exécution sur un réplica secondaire peuvent être exécutées sur le réplica principal sans procéder à la moindre modification. Comme pour les tables sur disque, toutes les requêtes exécutées sur les bases de données secondaires sont automatiquement associées au niveau d’isolation des transactions d’instantané, même lorsque d’autres niveaux d’isolation des transactions sont explicitement définis.

• Les opérations DML sont autorisées sur les variables de table sur la réplique secondaire, qu’elles soient basées sur le disque ou optimisées en mémoire.

Conditions préalables requises pour le groupe de disponibilité

• Réplicas secondaires lisibles (requis)

  L'administrateur de base de données doit configurer un ou plusieurs réplicas de sorte qu'en cas d'exécution sous le rôle secondaire, ces derniers autorisent toutes les connexions (uniquement pour l'accès en lecture seule) ou uniquement les connexions de tentative de lecture.

  Remarque

  Éventuellement, l'administrateur de base de données peut configurer un réplica de disponibilité pour exclure les connexions en lecture seule en cas d'exécution sous le rôle principal.

  Pour plus d’informations, consultez À propos de l’accès de la connexion client aux réplicas de disponibilité (SQL Server).

  Avertissement

  Seuls les réplicas qui utilisent la même build majeure de SQL Server seront lisibles. Pour plus d’informations, consultez Principes de base de la mise à niveau progressive.

• Écouteur de groupe de disponibilité

  Pour prendre en charge le routage en lecture seule, un groupe de disponibilité doit disposer d’un écouteur du groupe de disponibilité. Le client en lecture seule doit diriger ses demandes de connexion à cet écouteur, et la chaîne de connexion du client doit spécifier l'intention d'application « en lecture seule ». C’est-à-dire qu’elle doit être des requêtes de connexion « intention de lecture ».

• Routage en lecture seule

  Routage en lecture seule désigne la capacité de SQL Server à acheminer les demandes de connexion en lecture seule entrantes, dirigées vers un écouteur de groupe de disponibilité, vers un réplica secondaire lisible disponible. Les conditions préalables requises pour le routage en lecture seule sont les suivantes :

  • Pour prendre en charge le routage en lecture seule, une réplique secondaire lisible nécessite une URL de routage en lecture seule. Cette URL est effective uniquement lorsque le réplica local s'exécute sous le rôle secondaire. L’URL de routage en lecture seule doit être spécifiée pour chaque réplica, si nécessaire. Chaque URL de routage en lecture seule est utilisée pour router les demandes de connexion avec intention de lecture vers une réplique secondaire lisible spécifique. En général, chaque réplica secondaire lisible se voit attribuer une URL de routage en lecture seule.

  • Chaque réplica de disponibilité qui doit prendre en charge le routage en lecture seule lorsqu’il est le réplica principal nécessite une liste de routage en lecture seule. Une liste de routage en lecture seule donnée est effective uniquement lorsque le réplica local s'exécute sous le rôle principal. Cette liste doit être définie au cas par cas, pour chaque réplica, si nécessaire. En général, chaque liste de routage en lecture seule contient toutes les URL de routage en lecture seule, avec l'URL du réplica local à la fin de la liste.

    Remarque

    Les demandes de connexion d’intention de lecture peuvent faire l’objet d’un équilibrage de charge entre les réplicas. Pour plus d’informations, consultez Configurer l’équilibrage de charge entre des réplicas en lecture seule.

  Pour plus d’informations, consultez Configurer le routage en lecture seule pour un groupe de disponibilité (SQL Server).

Limitations et restrictions

Certaines opérations ne sont pas entièrement prises en charge, comme expliqué ci-après :

• Dès qu’un réplica lisible est activé pour la lecture, il peut commencer à accepter des connexions vers ses bases de données secondaires. Toutefois, si des transactions sont actives sur une base de données primaire, les versions de ligne ne sont pas entièrement disponibles sur la base de données secondaire correspondante. Toutes les transactions actives qui existaient sur le réplica principal lorsque le réplica secondaire a été configuré doivent être validées ou annulées. Tant que ce processus n'est pas terminé, le mappage du niveau d'isolement de la transaction sur la base de données secondaire est incomplet et les requêtes sont temporairement bloquées.

  Avertissement

  L’exécution de transactions longues a un impact sur le nombre de lignes versionnées conservées, tant pour les tables basées sur disque que pour les tables optimisées en mémoire.

• Dans une base de données secondaire comportant des tables optimisées en mémoire, bien que les versions de ligne soient toujours générées pour les tables optimisées en mémoire, les requêtes sont bloquées jusqu’à la fin de toutes les transactions actives qui existaient dans le réplica principal au moment où le réplica secondaire a été activé pour un accès en lecture. Cela garantit que les tables sur disque et optimisées en mémoire sont disponibles en même temps pour la charge de travail de création de rapports et les requêtes en lecture seule.

• Le suivi des modifications et la capture des données modifiées ne sont pas pris en charge sur les bases de données secondaires qui appartiennent à une réplique secondaire accessible en lecture :

  • Le suivi des modifications est explicitement désactivé sur les bases de données secondaires.

  • La fonctionnalité Change Data Capture ne peut pas être activée uniquement sur une base de données de réplica secondaire. La fonctionnalité Change Data Capture (CDC) peut être activée sur la base de données du réplica principal, et les modifications peuvent être lues à partir des tables CDC via les fonctions de la base de données du réplica secondaire.

• Étant donné que les opérations de lecture sont mappées sur le niveau d'isolation des transactions Snapshot, le nettoyage des enregistrements fantômes sur le réplica principal peut être bloqué par des transactions exécutées sur un ou plusieurs réplicas secondaires. La tâche de nettoyage des enregistrements fantômes supprimera automatiquement les enregistrements fantômes des tables sur disque dans le réplica principal lorsqu’ils ne seront plus nécessaires à aucun réplica secondaire. Cela s'apparente aux opérations réalisées lorsque vous exécutez des transactions sur le réplica principal. Dans les cas extrêmes sur la base de données secondaire, vous devrez éventuellement abandonner une longue requête de lecture qui bloque le nettoyage des enregistrements fantômes. Notez que le nettoyage des enregistrements fantômes peut être bloqué si le réplica secondaire se déconnecte ou lorsque le mouvement des données est suspendu sur la base de données secondaire. Les enregistrements fantômes utilisent de l’espace physique dans un fichier de données, ce qui peut entraîner des problèmes de réutilisation de l’espace. Pour plus d’informations, consultez Nettoyage des éléments fantômes. Cet état empêche également la troncation du journal ; par conséquent, si cet état persiste, nous vous recommandons de supprimer cette base de données secondaire du groupe de disponibilité. Il n’y a pas de problème de nettoyage des enregistrements fantômes avec les tables optimisées en mémoire, car les versions de ligne sont conservées en mémoire et sont indépendantes des versions de ligne sur la réplique principale.

• L'opération DBCC SHRINKFILE sur les fichiers contenant des tables sur disque peut échouer sur le réplica principal si le fichier contient des enregistrements fantômes qui sont toujours requis par un réplica secondaire.

• À partir de SQL Server 2014 (12.x), les réplicas secondaires accessibles en lecture peuvent rester en ligne même lorsque le réplica principal est hors ligne en raison d’une action de l’utilisateur ou d’une défaillance ; par exemple, lorsque la synchronisation a été suspendue à la suite d’une commande utilisateur ou d’une défaillance, ou lorsqu’un réplica est à l’état de résolution parce que le WSFC est hors ligne. Toutefois, le routage en lecture seule ne fonctionne pas dans ce cas, car l'écouteur du groupe de disponibilité est également hors connexion. Les clients doivent se connecter directement aux réplicas secondaires en lecture seule pour les charges de travail en lecture seule.

Considérations relatives aux performances

Cette section présente plusieurs considérations relatives aux performances des bases de données secondaires accessibles en lecture

Dans cette section :

• Latence des données

• Impact d'une charge de travail en lecture seule

• Indexation

• Statistiques des bases de données d’accès en lecture seule

Latence des données

L'implémentation de l'accès en lecture seule aux réplicas secondaires est utile si vos charges de travail en lecture seule peuvent tolérer une certaine latence des données. Lorsque la latence des données n’est pas acceptable, envisagez d’exécuter des charges de travail en lecture seule sur la réplique principale.

La réplique principale envoie aux répliques secondaires les enregistrements de journal des modifications apportées à la base de données principale. Sur chaque base de données secondaire, un thread REDO dédié applique les enregistrements du journal. Sur une base de données secondaire accessible en lecture, une modification de données n'apparaît pas dans les résultats de la requête tant que l'enregistrement du journal qui contient la modification n'a pas été appliqué à la base de données secondaire et que la transaction n'a pas été validée sur la base de données primaire.

Cela signifie qu'il y a une certaine latence, en général de quelques secondes, entre les réplicas principal et secondaire. Dans des cas exceptionnels, toutefois, par exemple si des problèmes réseau réduisent le débit, la latence peut devenir importante. La latence augmente en cas de survenue de goulots d'étranglement d'E/S et lorsque le déplacement des données est suspendu. Pour surveiller les déplacements de données suspendus, vous pouvez utiliser le tableau de bord Always On ou la vue de gestion dynamique sys.dm_hadr_database_replica_states.

Latence des données sur des bases de données avec des tables optimisées en mémoire

Dans SQL Server 2014 (12.x), la latence des données sur les secondaires actifs faisait l’objet de considérations spéciales. Consultez SQL Server 2014 (12.x)Secondaires actifs : réplicas secondaires accessibles en lecture. À partir de SQL Server 2016 (13.x) , la latence des données ne fait plus l’objet de considérations spéciales pour les tables optimisées en mémoire. La latence des données attendue pour les tables optimisées en mémoire est comparable à celle des tables sur disque.

Impact d'une charge de travail en lecture seule

Lorsque vous configurez une réplique secondaire pour un accès en lecture seule, vos charges de travail en lecture seule sur les bases de données secondaires consomment des ressources système, telles que le processeur et les E/S (pour les tables sur disque), utilisées par les threads de réexécution, surtout si les charges de travail en lecture seule sur les tables sur disque sont très gourmandes en E/S. Il n’y a aucun impact sur les E/S lors de l’accès à des tables optimisées en mémoire, car toutes les lignes résident en mémoire.

En outre, les charges de travail en lecture seule sur les réplicas secondaires peuvent bloquer les modifications du langage de définition de données (DDL) qui sont appliquées par les enregistrements du journal.

• Même si les opérations de lecture ne prennent pas de verrous partagés en raison du contrôle de version de ligne, ces opérations acceptent les verrous de stabilité de schéma (Sch-S), qui peuvent bloquer les opérations de restauration qui appliquent des modifications DDL. Les opérations DDL incluent les opérations ALTER/DROP sur les tables et les vues, mais pas les opérations DROP ou ALTER sur les procédures stockées. Par exemple, si vous supprimez une table basée sur disque ou optimisée en mémoire sur la base principale. Lorsque le thread REDO traite l'enregistrement de journal pour supprimer la table, il doit acquérir un verrou SCH_M sur la table et peut être bloqué par une requête en cours d'exécution qui accède à la table. Ce comportement est le même sur le réplica principal, sauf que la suppression de la table s'effectue dans le cadre d'une session utilisateur et non d'un thread REDO.

• D'autres formes de blocage peuvent avoir lieu avec les tables optimisées en mémoire. La suppression d'une procédure stockée native peut provoquer le blocage du thread REDO s'il existe une exécution simultanée de la procédure stockée native sur le réplica secondaire. Ce comportement est le même sur le réplica principal, sauf que la suppression de la procédure stockée s'effectue dans le cadre d'une session utilisateur et non d'un thread REDO.

Tenez compte des meilleures pratiques pour créer des requêtes, et utilisez ces meilleures pratiques dans les bases de données secondaires. Par exemple, planifiez les requêtes de longue durée, telles que les agrégations de données, pendant les périodes de faible activité.

Indexation

Pour optimiser les charges de travail en lecture seule sur les réplicas secondaires lisibles, vous souhaiterez peut-être créer des index sur les tables des bases de données secondaires. Étant donné que vous ne pouvez pas modifier le schéma ou les données des bases de données secondaires, créez des index dans les bases de données primaires et autorisez le transfert des modifications sur la base de données secondaire par le biais du processus de restauration par progression.

Pour surveiller l’utilisation des index sur un réplica secondaire, interrogez les colonnes user_seeks, user_scanset user_lookups de la vue de gestion dynamique sys.dm_db_index_usage_stats .

Statistiques des bases de données d’accès en lecture seule

Les statistiques sur les colonnes des tables et des vues indexées permettent d'optimiser les plans de requête. Pour les groupes de disponibilité, des statistiques créées et conservées sur les bases de données primaires sont automatiquement rendues persistantes sur les bases de données secondaires dans le cadre de l'application des enregistrements du journal des transactions. Toutefois, la charge de travail en lecture seule sur les bases de données secondaires peut avoir besoin de statistiques différentes de celles créées sur les bases de données primaires. En outre, étant donné que les bases de données secondaires sont limitées à l'accès en lecture seule, les statistiques ne peuvent pas être créées sur les bases de données secondaires.

Pour résoudre ce problème, le réplica secondaire crée et maintient des statistiques temporaires pour les bases de données secondaires dans tempdb. Le suffixe _readonly_database_statistic est ajouté au nom des statistiques temporaires pour les différencier des statistiques permanentes qui sont conservées à partir de la base de données primaire.

Seul SQL Server peut créer et mettre à jour les statistiques temporaires. Toutefois, vous pouvez supprimer des statistiques temporaires et analyser leurs propriétés en utilisant les mêmes outils que ceux que vous utilisez pour les statistiques permanentes :

• Supprimez des statistiques temporaires à l’aide de l’instruction DROP STATISTICS Transact-SQL.

• Analysez les statistiques à l’aide des affichages catalogue sys.stats et sys.stats_columns . L’affichagesys_stats inclut la colonne is_temporarypour distinguer les statistiques permanentes des statistiques temporaires.

La mise à jour automatique des statistiques n’est pas prise en charge pour les tables optimisées en mémoire sur la réplique principale ou secondaire. Vous devez surveiller les performances des requêtes et les plans sur le réplica secondaire et mettre manuellement à jour les statistiques sur le réplica principal quand cela est nécessaire. Toutefois, les statistiques manquantes sont créées automatiquement sur la réplique principale comme sur la réplique secondaire.

Pour plus d’informations sur les statistiques SQL Server, consultez Statistiques.

Dans cette section :

• Statistiques permanentes obsolètes sur les bases de données secondaires

• Limitations et restrictions

Statistiques permanentes obsolètes sur les bases de données secondaires

SQL Server détecte si les statistiques permanentes sur une base de données secondaire sont obsolètes. Or, il n'est pas possible d'apporter des modifications aux statistiques permanentes, sauf en modifiant la base de données primaire. Pour l'optimisation des requêtes, SQL Server crée des statistiques temporaires pour les tables sur disque sur la base de données secondaire et les utilise en remplacement des statistiques permanentes obsolètes.

Lorsque les statistiques permanentes sont mises à jour sur la base de données primaire, elles sont automatiquement conservées dans la base de données secondaire. Ensuite, SQL Server utilise les statistiques permanentes mises à jour, qui sont plus récentes que les statistiques temporaires.

Si le groupe de disponibilité bascule, les statistiques temporaires sont supprimées sur tous les réplicas secondaires.

Limitations et restrictions

• Étant donné que les statistiques temporaires sont stockées dans tempdb, un redémarrage du service SQL Server provoque la disparition de toutes les statistiques temporaires.

• Le suffixe _readonly_database_statistic est réservé aux statistiques générées par SQL Server. Vous ne pouvez pas utiliser ce suffixe lorsque vous créez des statistiques sur une base de données primaire. Pour plus d’informations, consultez Statistiques.

Accès aux tables optimisées en mémoire sur une réplique secondaire

Les niveaux d’isolation des transactions qui peuvent être utilisés avec des tables optimisées en mémoire sur un réplica secondaire sont les mêmes que sur le réplica principal. Il est recommandé de définir le niveau d’isolation au niveau de la session sur READ COMMITTED et de définir l’option au niveau de la base de données MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT sur ON. Par exemple :

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO  
  

Considérations relatives à la planification des capacités

• Dans le cas de tables sur disque, les réplicas secondaires accessibles en lecture peuvent nécessiter de l’espace dans tempdb pour deux raisons :

  • Le niveau d'isolation d'instantané copie les versions de ligne dans tempdb.

  • Les statistiques temporaires des bases de données secondaires sont créées et conservées dans tempdb. Les statistiques temporaires peuvent provoquer une légère augmentation de la taille de tempdb. Pour plus d’informations, consultez Statistiques des bases de données d’accès en lecture seule, plus loin dans cette section.

• Lorsque vous configurez l’accès en lecture pour un ou plusieurs réplicas secondaires, les bases de données primaires ajoutent 14 octets de surcharge aux lignes de données supprimées, modifiées ou insérées afin de stocker des pointeurs vers les versions des lignes dans les bases de données secondaires, pour les tables sur disque. Cette surcharge de 14 octets est reportée aux bases de données secondaires. La surcharge de 14 octets étant ajoutée aux lignes de données, des fractionnements de page peuvent se produire.

  Les données de version de ligne ne sont pas générées par les bases de données primaires. Au lieu de cela, les bases de données secondaires génèrent les versions de ligne. Toutefois, le contrôle de version de ligne augmente le stockage des données dans les bases de données primaires et secondaires.

  L’ajout des données de contrôle de version de ligne dépend du paramètre du niveau d’isolation des instantanés ou du niveau d’isolation des instantanés en lecture validée (RCSI) sur la base de données principale. Le tableau ci-dessous décrit le comportement du contrôle de version sur une base de données secondaire accessible en lecture avec différents paramètres pour les tables sur disque.

  Réplica secondaire accessible en lecture ?	Niveau d’isolation d’instantané ou niveau RCSI activé ?	Base de données primaire	Base de données secondaire
  Non	Non	Aucune version de ligne ni surcharge de 14 octets	Aucune version de ligne ni surcharge de 14 octets
  Non	Oui	Versions de ligne et surcharge de 14 octets	Pas de versionnement des lignes, mais un surcoût de 14 octets
  Oui	Non	Pas de versionnement des lignes, mais un surcoût de 14 octets	Versions de ligne et surcharge de 14 octets
  Oui	Oui	Versions de ligne et surcharge de 14 octets	Versions de ligne et surcharge de 14 octets

Tâches associées

• Configurer l’accès en lecture seule sur un réplica de disponibilité (SQL Server)

• Configurer le routage en lecture seule pour un groupe de disponibilité (SQL Server)

• Créer ou configurer un écouteur de groupe de disponibilité (SQL Server)

• Surveiller des groupes de disponibilité (Transact-SQL)

• Afficher les propriétés d'un réplica de disponibilité (SQL Server)

• Utiliser la boîte de dialogue Nouveau groupe de disponibilité (SQL Server Management Studio)

Contenu associé

• Blog de l’équipe SQL Server Always On : Blog officiel de l’équipe SQL Server Always On

Voir aussi

Vue d’ensemble des groupes de disponibilité Always On (SQL Server)
À propos de l'accès client aux réplicas de disponibilité (SQL Server)
Écouteurs de groupe de disponibilité, connectivité client et basculement d’application (SQL Server)
Statistiques