Secondaires actifs : réplicas secondaires lisibles (groupes de disponibilité d'AlwaysOn)

Article
06/25/2013

Les fonctions secondaires actives de Groupes de disponibilité AlwaysOn incluent la prise en charge de l'accès en lecture seule à un ou plusieurs réplicas secondaires (réplicas secondaires lisibles). Un réplica secondaire lisible autorise l'accès en lecture seule à toutes ses bases de données secondaires. Toutefois, les bases de données secondaires lisibles ne sont pas définies 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 un réplica secondaire classique, les données des bases de données secondaires sont quasiment synchronisées en temps réel. De plus, mes index de recherche en texte intégral sont synchronisés avec les bases de données secondaires. Dans de nombreux cas, 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é qui se produisent dans les bases de données primaires sont conservé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.

[!REMARQUE]

Bien que vous ne puissiez pas écrire de données dans les bases de données secondaires, vous pouvez écrire dans d'autres bases de données en lecture-écriture sur l'instance de serveur qui héberge le réplica secondaire, notamment les bases de données utilisateur et les bases de données système telles que tempdb.

Groupes de disponibilité AlwaysOn prend également en charge le reroutage des demandes de connexion d'intention de lecture sur 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).

Dans cette rubrique :

Avantages
Conditions préalables requises pour le groupe de disponibilité
Limitations et restrictions
Considérations relatives aux performances
Considérations sur la planification des capacités
Tâches associées
Contenu connexe

Avantages

La direction des connexions en lecture seule vers les réplicas secondaires accessibles en lecture 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 en lecture critique ou si la charge de travail ne tolère aucune latence, vous pouvez l'exécuter sur le serveur principal.
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 sur la base de données secondaire accessible en lecture optimisent les requêtes en lecture seule. 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 utilisent le contrôle de version de ligne pour supprimer la contention de blocage sur les bases de données secondaires. Toutes les requêtes exécutées sur les bases de données secondaires sont automatiquement mappées au niveau de la transaction d'isolement d'instantané, même lorsque d'autres niveaux d'isolation de la transaction sont définis de manière explicite. De plus, tous les indicateurs de verrouillage sont ignorés. Cela élimine la contention de lecture/écriture.

Icône de flèche utilisée avec le lien Retour en haut [Haut de la page]

Conditions préalables requises pour le groupe de disponibilité

Accès en lecture aux réplicas secondaires (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, ils autorisent toutes les connexions (uniquement pour l'accès en lecture seule) ou uniquement les connexions d'intention 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).
Écouteur de groupe de disponibilité

Pour prendre en charge le routage en lecture seule, un groupe de disponibilité doit posséder un écouteur de 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 ». Autrement dit, il doit s'agir de demandes de connexion d'intention de lecture.
Routage en lecture seule

Le routage en lecture seule fait référence à la capacité de SQL Server de router les demandes de connexions d'intention de lecture entrantes, qui sont dirigés vers un écouteur de groupe de disponibilité et 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, un réplica secondaire lisible requiert 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 par réplica, si nécessaire. Chaque URL de routage en lecture seule est utilisée pour acheminer les demandes de connexion d'intention de lecture vers un réplica secondaire lisible spécifique. En général, à chaque réplica secondaire lisible est affecté 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 requiert 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 spécifiée par 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 sont acheminées vers le premier secondaire lisible disponible dans la liste de routage en lecture seule du réplica principal actuel. Il n'existe aucun équilibrage de charge.
Pour plus d'informations, consultez Configurer le routage en lecture seule pour un groupe de disponibilité (SQL Server).

[!REMARQUE]

Pour plus d'informations sur les écouteurs de groupe de disponibilité et le routage en lecture seule, consultez Écouteurs de groupe de disponibilité, connectivité client et basculement d'application (SQL Server).

Icône de flèche utilisée avec le lien Retour en haut [Haut de la page]

Limitations et restrictions

Certaines opérations ne sont pas entièrement prises en charge, comme suit :

Dès qu'un réplica secondaire accessible en lecture est joint au groupe de disponibilité, le réplica secondaire peut commencer à accepter les connexions à 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 immédiatement 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.

[!REMARQUE]

L'exécution d'une longue transaction a une incidence sur le nombre de lignes avec version conservées.
Le suivi des modifications et la capture de données modifiées ne sont pas pris en charge sur les bases de données secondaires qui appartiennent à un réplica secondaire accessible en lecture :
- Le suivi des modifications est explicitement désactivé sur les bases de données secondaires.
- La capture de données modifiées peut être activée sur une base de données secondaire, mais cette fonctionnalité n'est pas pris en charge.
Étant donné que les opérations de lecture sont mappées au niveau de la transaction d'isolement d'instantané, le nettoyage des enregistrements fantômes sur le réplica primaire peut être bloqué par des transactions sur un ou plusieurs réplicas secondaires. La tâche de nettoyage d'enregistrement fantôme nettoiera automatiquement les enregistrements fantômes sur le réplica principal lorsqu'aucun réplica secondaire n'en aura plus besoin. 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 est déconnecté ou si le déplacement des données est interrompu sur la base de données secondaire. 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é.
L'opération DBCC SHRINKFILE peut échouer sur le réplica principal si le fichier contient des enregistrements fantômes qui sont toujours requis par un réplica secondaire.

[!REMARQUE]

Si vous interrogez la vue de gestion dynamique sys.dm_db_index_physical_stats sur une instance de serveur qui héberge un réplica secondaire lisible, vous pouvez rencontrer une erreur matérielle REDO. Cela est dû au fait que cette vue de gestion dynamique acquiert un verrou IS sur la table utilisateur ou la vue spécifiée, qui peut bloquer les demandes par un thread REDO pour un verrou X sur cette table utilisateur ou cette vue.

Icône de flèche utilisée avec le lien Retour en haut [Haut de la page]

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. Dans les situations où la latence des données est inacceptable, envisagez d'exécuter les charges de travail en lecture seule sur le réplica principal.

Le réplica principal envoie les enregistrements du journal des modifications sur la base de données primaire aux réplicas secondaires. Sur chaque base de données secondaire, un thread de restauration par progression dédié applique les enregistrements de 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 le déplacement des données suspendu, vous pouvez utiliser le Tableau de bord AlwaysOn ou la vue de gestion dynamique sys.dm_hadr_database_replica_states.

Impact d'une charge de travail en lecture seule

Lorsque vous configurez un réplica secondaire pour l'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 de thread de phase de restauration par progression, surtout si les charges de travail en lecture seule nécessitent de nombreuses E/S.

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.

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 à exécution longue, telles que les agrégations de données, pendant les périodes de faible activité.

[!REMARQUE]

Si un thread de phase de restauration par progression est bloqué par des requêtes sur un réplica secondaire, le XEvent sqlserver.lock_redo_blocked est déclenché.

Indexation

Pour optimiser les charges de travail en lecture seule sur les réplicas secondaires accessibles en lecture, vous pouvez 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 les modifications à transférer à 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_scans et 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 conserve les 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 les statistiques temporaires à l'aide de l'instruction DROP STATISTICS Transact-SQL.
Surveillez les statistiques à l'aide des affichages catalogue sys.stats et sys.stats_columns. sys_stats inclut une colonne, is_temporary, pour distinguer les statistiques permanentes des statistiques temporaires.

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 à modifier la base de données primaire. Pour l'optimisation de la requête, SQL Server crée des statistiques temporaires 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é pour les 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.

Icône de flèche utilisée avec le lien Retour en haut [Haut de la page]

Considérations sur la planification des capacités

Les réplicas secondaires accessibles en lecture peuvent nécessiter de l'espace dans tempdb pour deux raisons :
- Le niveau d'isolement 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 sur les lignes de données supprimées, modifiées ou insérées pour stocker les pointeurs vers les versions de ligne sur les bases de données secondaires. 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 de données de version de ligne dépend du niveau d'isolement d'instantané ou d'isolement d'instantané de lecture validée (RCSI) sur la base de données primaire. 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.

Accès en lecture au réplica secondaire ?	Isolement d'instantané ou niveau RCSI activé ?	Base de données primaire	Base de données secondaire
Non	Non	Aucune version de ligne ou surcharge de 14 octets	Aucune version de ligne ou surcharge de 14 octets
Non	Oui	Versions de ligne et surcharge de 14 octets	Aucune version de ligne, mais surcharge de 14 octets
Oui	Non	Aucune version de ligne, mais surcharge 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