Secondaires actifs : réplicas secondaires accessibles en lecture (groupes de disponibilité Always On)Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)

Cette rubrique s’applique à : OuiSQL Serveraucunbase de données SQL AzureaucunAzure SQL Data Warehouse aucun Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Les fonctions secondaires actives de Groupes de disponibilité Always OnAlways On availability groups incluent la prise en charge de l’accès en lecture seule à un ou plusieurs réplicas secondaires (réplicas secondaires accessibles en lecture).The Groupes de disponibilité Always OnAlways On availability groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). Un réplica secondaire accessible en lecture autorise l'accès en lecture seule à toutes ses bases de données secondaires.A readable secondary replica allows read-only access to all its secondary databases. Toutefois, les bases de données secondaires accessibles en lecture ne sont pas définies en lecture seule.However, readable secondary databases are not set to read-only. Elles sont dynamiques.They are dynamic. 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.A given secondary database changes as changes on the corresponding primary database are applied to the secondary database. Pour un réplica secondaire classique, les données des bases de données secondaires, y compris les tables optimisées en mémoire durables, sont quasiment synchronisées en temps réel.For a typical secondary replica, the data, including durable memory optimized tables, in the secondary databases is in near real time. De plus, les index de recherche en texte intégral sont synchronisés avec les bases de données secondaires.Furthermore, full-text indexes are synchronized with the secondary databases. 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.In many circumstances, data latency between a primary database and the corresponding secondary database is only a few seconds.

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.Security settings that occur in the primary databases are persisted to the secondary databases. 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.This includes users, database roles, and applications roles together with their respective permissions and transparent data encryption (TDE), if enabled on the primary database.

Note

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.Though you cannot write data to secondary databases, you can write to read-write databases on the server instance that hosts the secondary replica, including user databases and system databases such as tempdb.

Groupes de disponibilité Always OnAlways On availability groups prend également en charge le reroutage des demandes de connexion de tentative de lecture sur un réplica secondaire accessible en lecture (routage en lecture seule). also supports the re-routing of read-intent connection requests to a readable secondary replica (read-only routing). 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).For information about read-only routing, see Using a Listener to Connect to a Read-Only Secondary Replica (Read-Only Routing).

Dans cette rubrique :In this Topic:

Avantages Benefits

La direction des connexions en lecture seule vers les réplicas secondaires accessibles en lecture offre les avantages suivants :Directing read-only connections to readable secondary replicas provides the following benefits:

  • 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.Offloads your secondary read-only workloads from your primary replica, which conserves its resources for your mission critical workloads. 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 réplica principal.If you have mission critical read-workload or the workload that cannot tolerate latency, you should run it on the primary.

  • Améliore votre retour sur investissement pour les systèmes qui hébergent des réplicas secondaires accessibles en lecture.Improves your return on investment for the systems that host readable secondary replicas.

    En outre, les réplicas secondaires accessibles en lecture assurent une prise en charge fiable des opérations en lecture seule, comme suit :In addition, readable secondaries provide robust support for read-only operations, as follows:

  • 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.Automatic temporary statistics on readable secondary database optimize read-only queries on disk-based tables. Pour les tables optimisées en mémoire, les statistiques manquantes sont créées automatiquement.For memory-optimized tables, the missing statistics are created automatically. Cependant, aucune mise à jour automatique des statistiques obsolètes n'a lieu.However, there is no auto-update of stale statistics. Vous devrez mettre manuellement à jour les statistiques sur le réplica principal.You will need to manually update the statistics on the primary replica. Pour plus d’informations, consultez Statistiques des bases de données d’accès en lecture seule, plus loin dans cette rubrique.For more information, see Statistics for Read-Only Access Databases, later in this topic.

  • Les charges de travail en lecture seule pour les tables sur disque utilisent le contrôle de version de ligne pour supprimer la contention de blocage sur les bases de données secondaires.Read-only workloads for disk-based tables use row versioning to remove blocking contention on the secondary databases. Toutes les requêtes exécutées sur les bases de données secondaires sont automatiquement mappées au niveau des transactions d'isolation d'instantané, même lorsque d'autres niveaux d'isolation des transactions sont définis de manière explicite.All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. De plus, tous les indicateurs de verrouillage sont ignorés.Also, all locking hints are ignored. Cela élimine la contention de lecture/écriture.This eliminates reader/writer contention.

  • 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).Read-only workloads for memory-optimized durable tables access the data in exactly the same way it is accessed on the primary database, using native stored procedures or SQL Interoperability with the same transaction isolation level limitations (See Isolation Levels in the Database Engine). 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.Reporting workload or read-only queries running on the primary replica can be run on the secondary replica without requiring any changes. 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.Similarly, a reporting workload or read-only queries running on a secondary replica can be run on the primary replica without requiring any changes. Similaires aux tables sur disque, toutes les requêtes exécutées sur les bases de données secondaires sont automatiquement mappées au niveau des transactions d'isolation d'instantané, même lorsque d'autres niveaux d'isolation des transactions sont définis de manière explicite.Similar to disk-based tables, all queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set.

  • Les opérations DML sont autorisées sur les variables de table pour les types de tables sur disque et optimisées en mémoire sur le réplica secondaire.DML operations are allowed on table variables both for disk-based and memory-optimized table types on the secondary replica.

Conditions préalables requises pour le groupe de disponibilité Prerequisites for the Availability Group

  • Accès en lecture aux réplicas secondaires (requis)Readable secondary replicas (required)

    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.The database administrator needs to configure one or more replicas so that, when running under the secondary role, they allow either all connections (just for read-only access) or only read-intent connections.

    Note

    É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.Optionally, the database administrator can configure any of the availability replicas to exclude read-only connections when running under the primary role.

    Pour plus d’informations, consultez À propos de l’accès de la connexion client aux réplicas de disponibilité (SQL Server).For more information, see About Client Connection Access to Availability Replicas (SQL Server).

  • Écouteur de groupe de disponibilitéAvailability group listener

    Pour prendre en charge le routage en lecture seule, un groupe de disponibilité doit posséder un écouteur de groupe de disponibilité.To support read-only routing, an availability group must possess an availability group listener. Le client en lecture seule doit diriger ses demandes de connexion à cet écouteur, et la chaîne de connexion du client doit spécifier la tentative d'application « en lecture seule ».The read-only client must direct its connection requests to this listener, and the client's connection string must specify the application intent as "read-only." Autrement dit, il doit s’agir de demandes de connexion d’intention de lecture.That is, they must be read-intent connection requests.

  • Routage en lecture seuleRead only routing

    Leroutage en lecture seule fait référence à la capacité de SQL Server d’acheminer les demandes de connexions de tentative de lecture entrantes, qui sont dirigées vers un écouteur de groupe de disponibilité, vers un réplica secondaire accessible en lecture disponible.Read-only routing refers to the ability of SQL Server to route incoming read-intent connection requests, that are directed to an availability group listener, to an available readable secondary replica. Les conditions préalables requises pour le routage en lecture seule sont les suivantes :The prerequisites for read-only routing are as follows:

    • Pour prendre en charge le routage en lecture seule, un réplica secondaire accessible en lecture requiert une URL de routage en lecture seule.To support read-only routing, a readable secondary replica requires a read-only routing URL. Cette URL est effective uniquement lorsque le réplica local s'exécute sous le rôle secondaire.This URL takes effect only when the local replica is running under the secondary role. L'URL de routage en lecture seule doit être spécifiée par réplica, si nécessaire.The read-only routing URL must be specified on a replica-by-replica basis, as needed. 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.Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. En général, à chaque réplica secondaire lisible est affecté une URL de routage en lecture seule.Typically, every readable secondary replica is assigned a read-only routing URL.

    • 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.Each availability replica that is to support read-only routing when it is the primary replica requires a read-only routing list. 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.A given read-only routing list takes effect only when the local replica is running under the primary role. Cette liste doit être spécifiée par réplica, si nécessaire.This list must be specified on a replica-by-replica basis, as needed. 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.Typically, each read-only routing list would contain every read-only routing URL, with the URL of the local replica at the end of the list.

      Note

      Les demandes de connexion d’intention de lecture peuvent faire l’objet d’un équilibrage de charge entre les réplicas.Read-intent connection requests can be load-balanced across replicas. Pour plus d’informations, consultez Configurer l’équilibrage de charge entre des réplicas en lecture seule.For more information, see Configure load-balancing across read-only replicas.

      Pour plus d’informations, consultez Configurer le routage en lecture seule pour un groupe de disponibilité (SQL Server).For more information, see Configure Read-Only Routing for an Availability Group (SQL Server).

Note

Pour plus d’informations sur les écouteurs de groupe de disponibilité et sur le routage en lecture seule, consultez Écouteurs de groupe de disponibilité, connectivité client et basculement d’application (SQL Server).For information about availability group listeners and more information about read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

Limitations et restrictions Limitations and Restrictions

Certaines opérations ne sont pas entièrement prises en charge, comme expliqué ci-après :Some operations are not fully supported, as follows:

  • Dès qu'un réplica accessible en lecture est activé en lecture, il peut commencer à accepter des connexions à ses bases de données secondaires.As soon as a readable replica is enabled for read, it can start accepting connections to its secondary databases. 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.However, if any active transactions exist on a primary database, the row versions will not be fully available on the corresponding secondary database. Toutes les transactions actives qui existaient sur le réplica principal lorsque le réplica secondaire a été configuré doivent être validées ou restaurées.Any active transactions that existed on the primary replica when the secondary replica was configured must commit or roll back. 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.Until this process completes, the transaction isolation level mapping on the secondary database is incomplete and queries are temporarily blocked.

    Avertissement

    Les transactions de longue durée ont un impact sur le nombre de lignes avec version conservées, à la fois pour les tables sur disque et les tables optimisées en mémoire.Running long transactions impacts the number of versioned rows kept, both for disk-based and memory-optimized tables.

  • 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.On a secondary database with memory-optimized tables, even though row versions are always generated for memory-optimized tables, queries are blocked until all active transactions that existed in the primary replica when the secondary replica was enabled for read complete. 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.This ensures that both disk-based and memory-optimized tables are available to the reporting workload and read-only queries at the same time.

  • 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 :Change tracking and change data capture are not supported on secondary databases that belong to a readable secondary replica:

    • Le suivi des modifications est explicitement désactivé sur les bases de données secondaires.Change tracking is explicitly disabled on secondary databases.

    • La capture des données modifiées ne peut pas être activée uniquement sur une base de données du réplica secondaire.Change Data Capture cannot be enabled only on a secondary replica database. La capture des données modifiées peut être activée sur la base de données du réplica principal et les modifications peuvent être lues à partir des tables de capture des données modifiées en utilisant les fonctions de la base de données du réplica secondaire.Change Data Capture can be enabled on the primary replica database and the changes can be read from the CDC tables using the functions on the secondary replica database.

  • Étant donné que les opérations de lecture sont mappées au niveau des transactions d'isolation d'instantané, le nettoyage des enregistrements fantômes sur le réplica principal peut être bloqué par des transactions sur un ou plusieurs réplicas secondaires.Because read operations are mapped to snapshot isolation transaction level, the cleanup of ghost records on the primary replica can be blocked by transactions on one or more secondary replicas. La tâche de nettoyage des enregistrements fantômes nettoiera automatiquement les enregistrements fantômes pour les tables sur disque sur le réplica principal lorsqu'aucun réplica secondaire n'en aura plus besoin.The ghost record cleanup task will automatically clean up the ghost records for disk-based tables on the primary replica when they are no longer needed by any secondary replica. Cela s'apparente aux opérations réalisées lorsque vous exécutez des transactions sur le réplica principal.This is similar to what is done when you run transaction(s) on the primary replica. 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.In the extreme case on the secondary database, you will need to kill a long running read-query that is blocking the ghost cleanup. 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.Note, the ghost clean can be blocked if the secondary replica gets disconnected or when data movement is suspended on the secondary database. 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é.This state also prevents log truncation, so if this state persists, we recommend that you remove this secondary database from the availability group. Il n'existe aucun problème de nettoyage d'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 le réplica principal.There is no ghost record cleanup issue with memory-optimized tables because the row versions are kept in memory and are independent of the row versions on the primary replica.

  • 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.The DBCC SHRINKFILE operation on files containing disk-based tables might fail on the primary replica if the file contains ghost records that are still needed on a secondary replica.

  • À compter de SQL Server 2014SQL Server 2014, les réplicas secondaires accessibles en lecture peuvent rester en ligne même lorsque le réplica principal est hors connexion en raison d'une action de l'utilisateur ou d'un échec.Beginning in SQL Server 2014SQL Server 2014, readable secondary replicas can remain online even when the primary replica is offline due to user action or a failure. Toutefois, le routage en lecture seule ne fonctionne pas dans ce cas, car l'écouteur du groupe de disponibilité est également hors connexion.However, read-only routing does not work in this situation because the availability group listener is offline as well. Les clients doivent se connecter directement aux réplicas secondaires en lecture seule pour les charges de travail en lecture seule.Clients must connect directly to the read-only secondary replicas for read-only workloads.

Note

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 accessible en lecture, vous pouvez rencontrer un problème de blocage REDO.If you query the sys.dm_db_index_physical_stats dynamic management view on a server instance that is hosting a readable secondary replica, you might encounter a REDO blocking issue. Ce problème est dû au fait que la vue de gestion dynamique acquiert un verrou IS sur la table ou la vue utilisateur spécifiée qui peut bloquer les demandes d'un thread de phase de restauration par progression concernant un verrou X sur la table ou vue utilisateur en question.This is because this dynamic management view acquires an IS lock on the specified user table or view that can block requests by a REDO thread for an X lock on that user table or view.

Considérations relatives aux performances Performance Considerations

Cette section présente plusieurs considérations relatives aux performances des bases de données secondaires accessibles en lectureThis section discusses several performance considerations for readable secondary databases

Dans cette section :In This Section:

Latence des données Data Latency

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.Implementing read-only access to secondary replicas is useful if your read-only workloads can tolerate some data latency. 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.In situations where data latency is unacceptable, consider running read-only workloads against the primary replica.

Le réplica principal envoie les enregistrements du journal des modifications sur la base de données primaire aux réplicas secondaires.The primary replica sends log records of changes on primary database to the secondary replicas. Sur chaque base de données secondaire, un thread de phase de restauration par progression (REDO) dédié applique les enregistrements de journal.On each secondary database, a dedicated redo thread applies the log records. 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.On a read-access secondary database, a given data change does not appear in query results until the log record that contains the change has been applied to the secondary database and the transaction has been committed on primary database.

Cela signifie qu'il y a une certaine latence, en général de quelques secondes, entre les réplicas principal et secondaire.This means that there is some latency, usually only a matter of seconds, between the primary and secondary replicas. Dans des cas exceptionnels, toutefois, par exemple si des problèmes réseau réduisent le débit, la latence peut devenir importante.In unusual cases, however, for example if network issues reduce throughput, latency can become significant. La latence augmente en cas de survenue de goulots d'étranglement d'E/S et lorsque le déplacement des données est suspendu.Latency increases when I/O bottlenecks occur and when data movement is suspended. Pour surveiller le déplacement des données suspendu, utilisez le Tableau de bord Always On ou la vue de gestion dynamique sys.dm_hadr_database_replica_states .To monitor suspended data movement, you can use the Always On Dashboard or the sys.dm_hadr_database_replica_states dynamic management view.

Latence des données sur des bases de données avec des tables optimisées en mémoire Data Latency on databases with memory-optimized tables

Dans SQL Server 2014SQL Server 2014, la latence des données sur les secondaires actifs faisait l’objet de considérations spéciales. consultez SQL Server 2014SQL Server 2014 Secondaires actifs : réplicas secondaires accessibles en lecture.In SQL Server 2014SQL Server 2014 there were special considerations around data latency on active secondaries - see SQL Server 2014SQL Server 2014 Active Secondaries: Readable Secondary Replicas. À partir de SQL Server 2016SQL Server 2016 , la latence des données ne fait plus l’objet de considérations spéciales pour les tables optimisées en mémoire.Starting SQL Server 2016SQL Server 2016 there are no special considerations around data latency for memory-optimized tables. La latence des données attendue pour les tables optimisées en mémoire est comparable à celle des tables sur disque.The expected data latency for memory-optimized tables is comparable to the latency for disk-based tables.

Impact d'une charge de travail en lecture seule Read-Only Workload Impact

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 (pour les tables sur disque) des threads de phase de restauration par progression, surtout si les charges de travail en lecture seule sur les tables sur disque nécessitent de nombreuses E/S.When you configure a secondary replica for read-only access, your read-only workloads on the secondary databases consume system resources, such as CPU and I/O (for disk-based tables) from redo threads, especially if the read-only workloads on disk-based tables are highly I/O-intensive. Aucun impact n'est à constater sur les E/S lors de l'accès aux tables optimisées en mémoire, car toutes les lignes résident en mémoire.There is no IO impact when accessing memory-optimized tables because all the rows reside in memory.

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.Also, read-only workloads on the secondary replicas can block data definition language (DDL) changes that are applied through log records.

  • 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.Even though the read operations do not take shared locks because of row versioning, these operations take schema stability (Sch-S) locks, which can block redo operations that are applying DDL changes. Les opérations DDL incluent les vues et tables ALTER/DROP, mais pas DROP ou ALTER des procédures stockées.DDL operations include ALTER/DROP tables and Views but not DROP or ALTER of stored procedures. Par exemple, si vous supprimez une table sur disque ou optimisée en mémoire sur le réplica principal.So for example, if you drop a table disk-based or memory-optimized, on primary. 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.When REDO thread processes the log record to drop the table, it must acquire a SCH_M lock on the table and can get blocked by a running query accessing 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.This is the same behavior on primary replica except that the drop of the table is done as part of a user session and not REDO thread.

  • D'autres formes de blocage peuvent avoir lieu avec les tables optimisées en mémoire.There is additional blocking Memory-Optimized Tables. 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.A drop of native stored procedure can cause REDO thread to block if there is a concurrent execution of the native stored procedure on the secondary replica. 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.This is the same behavior on the primary replica except that the drop of the stored procedure is done as part of a user session and not REDO thread.

    Tenez compte des meilleures pratiques pour créer des requêtes, et utilisez ces meilleures pratiques dans les bases de données secondaires.Be aware of best practices around building queries, and exercise those best practices in the secondary databases. 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é.For example, schedule long-running queries such as aggregations of data during times of low activity.

Note

Si un thread de restauration par progression est bloqué par des requêtes sur un réplica secondaire, le XEvent sqlserver.lock_redo_blocked est déclenché.If a redo thread is blocked by queries on a secondary replica, the sqlserver.lock_redo_blocked XEvent is raised.

Indexation Indexing

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.To optimize read-only workloads on the readable secondary replicas, you may want to create indexes on the tables in the secondary databases. É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.Because you cannot make schema or data changes on the secondary databases, create indexes in the primary databases and allow the changes to transfer to the secondary database through the redo process.

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 .To monitor index usage activity on a secondary replica, query the user_seeks, user_scans, and user_lookups columns of the sys.dm_db_index_usage_stats dynamic management view.

Statistiques des bases de données d’accès en lecture seule Statistics for Read-Only Access Databases

Les statistiques sur les colonnes des tables et des vues indexées permettent d'optimiser les plans de requête.Statistics on columns of tables and indexed views are used to optimize query plans. 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.For availability groups, statistics that are created and maintained on the primary databases are automatically persisted on the secondary databases as part of applying the transaction log records. 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.However, the read-only workload on the secondary databases may need different statistics than those that are created on the primary databases. 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.However, because secondary databases are restricted to read-only access, statistics cannot be created on the secondary databases.

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.To address this problem, the secondary replica creates and maintains temporary statistics for secondary databases in 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.The suffix _readonly_database_statistic is appended to the name of temporary statistics to differentiate them from the permanent statistics that are persisted from the primary database.

Seul SQL ServerSQL Server peut créer et mettre à jour les statistiques temporaires.Only SQL ServerSQL Server can create and update temporary statistics. 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 :However, you can delete temporary statistics and monitor their properties using the same tools that you use for permanent statistics:

  • Supprimez les statistiques temporaires à l'aide de l'instruction DROP STATISTICS Transact-SQLTransact-SQL .Delete temporary statistics using the DROP STATISTICS Transact-SQLTransact-SQL statement.

  • Analysez les statistiques à l’aide des affichages catalogue sys.stats et sys.stats_columns .Monitor statistics using the sys.stats and sys.stats_columns catalog views. L’affichagesys_stats inclut la colonne is_temporarypour distinguer les statistiques permanentes des statistiques temporaires.sys_stats includes a column, is_temporary, to indicate which statistics are permanent and which are temporary.

    Il n'existe aucune prise en charge de la mise à jour automatique des statistiques pour les tables optimisées en mémoire sur le réplica principal ou secondaire.There is no support for auto-statistics update for memory-optimized tables on the primary or secondary replica. 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.You must monitor query performance and plans on the secondary replica and manually update the statistics on the primary replica when needed. Toutefois, les statistiques manquantes sont créées automatiquement sur le réplica principal et le réplica secondaire.However, the missing statistics are automatically created both on primary and secondary replica.

    Pour plus d’informations sur les statistiques SQL Server, consultez Statistiques.For more information about SQL Server statistics, see Statistics.

    Dans cette section :In This Section:

  • Statistiques permanentes obsolètes sur les bases de données secondairesStale Permanent Statistics on Secondary Databases

  • Limitations et restrictionsLimitations and Restrictions

Statistiques permanentes obsolètes sur les bases de données secondaires Stale Permanent Statistics on Secondary Databases

SQL ServerSQL Server détecte si les statistiques permanentes sur une base de données secondaire sont obsolètes. detects when permanent statistics on a secondary database are stale. Or, il n'est pas possible d'apporter des modifications aux statistiques permanentes, sauf en modifiant la base de données primaire.But changes cannot be made to the permanent statistics except through changes on the primary database. Pour l'optimisation des requêtes, SQL ServerSQL 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.For query optimization, SQL ServerSQL Server creates temporary statistics for disk-based tables on the secondary database and uses these statistics instead of the stale permanent statistics.

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.When the permanent statistics are updated on the primary database, they are automatically persisted to the secondary database. Ensuite, SQL ServerSQL Server utilise les statistiques permanentes mises à jour, qui sont plus récentes que les statistiques temporaires.Then SQL ServerSQL Server uses the updated permanent statistics, which are more current than the temporary statistics.

Si le groupe de disponibilité bascule, les statistiques temporaires sont supprimées sur tous les réplicas secondaires.If the availability group fails over, temporary statistics are deleted on all of the secondary replicas.

Limitations et restrictions Limitations and Restrictions

  • Étant donné que les statistiques temporaires sont stockées dans tempdb, un redémarrage du service SQL ServerSQL Server provoque la disparition de toutes les statistiques temporaires.Because temporary statistics are stored in tempdb, a restart of the SQL ServerSQL Server service causes all temporary statistics to disappear.

  • Le suffixe _readonly_database_statistic est réservé aux statistiques générées par SQL ServerSQL Server.The suffix _readonly_database_statistic is reserved for statistics generated by SQL ServerSQL Server. Vous ne pouvez pas utiliser ce suffixe lorsque vous créez des statistiques sur une base de données primaire.You cannot use this suffix when creating statistics on a primary database. Pour plus d'informations, consultez Statistics.For more information, see Statistics.

Accès aux tables optimisées en mémoire sur un réplica secondaire Accessing memory-optimized tables on a Secondary Replica

Les niveaux d’isolation de la transaction 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.The transaction isolation levels that can be used with memory-optimized tables on a secondary replica are the same as on the primary replica. 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.The recommendation is to set the session-level isolation level to READ COMMITTED and set the database-level option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON. Par exemple :For example:

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 Capacity Planning Considerations

  • 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 :In the case of disk-based tables, readable secondary replicas can require space in tempdb for two reasons:

    • Le niveau d'isolation d'instantané copie les versions de ligne dans tempdb.Snapshot isolation level copies row versions into tempdb.

    • Les statistiques temporaires des bases de données secondaires sont créées et conservées dans tempdb.Temporary statistics for secondary databases are created and maintained in tempdb. Les statistiques temporaires peuvent provoquer une légère augmentation de la taille de tempdb.The temporary statistics can cause a slight increase in the size of tempdb. Pour plus d’informations, consultez Statistiques des bases de données d’accès en lecture seule, plus loin dans cette section.For more information, see Statistics for Read-Only Access Databases, later in this 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 pour les tables sur disque.When you configure read-access for one or more secondary replicas, the primary databases add 14 bytes of overhead on deleted, modified, or inserted data rows to store pointers to row versions on the secondary databases for disk-based tables. Cette surcharge de 14 octets est reportée aux bases de données secondaires.This 14-byte overhead is carried over to the secondary databases. La surcharge de 14 octets étant ajoutée aux lignes de données, des fractionnements de page peuvent se produire.As the 14-byte overhead is added to data rows, page splits might occur.

    Les données de version de ligne ne sont pas générées par les bases de données primaires.The row version data is not generated by the primary databases. Au lieu de cela, les bases de données secondaires génèrent les versions de ligne.Instead, the secondary databases generate the row versions. Toutefois, le contrôle de version de ligne augmente le stockage des données dans les bases de données primaires et secondaires.However, row versioning increases data storage in both the primary and secondary databases.

    L'ajout de données de version de ligne dépend du niveau d'isolation d'instantané ou d'isolation d'instantané Read Committed défini sur la base de données primaire.The addition of the row version data depends on the snapshot isolation or read-committed snapshot isolation (RCSI) level setting on the primary database. 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.The table below describes the behavior of versioning on a readable secondary database under different settings for disk based tables.

    Accès en lecture au réplica secondaire ?Readable secondary replica? Isolation d'instantané ou isolation d'instantané Read Committed ?Snapshot isolation or RCSI level enabled? Base de données primairePrimary Database Base de données secondaireSecondary Database
    NonNo NonNo Aucune version de ligne ni surcharge de 14 octetsNo row versions or 14-byte overhead Aucune version de ligne ni surcharge de 14 octetsNo row versions or 14-byte overhead
    NonNo OuiYes Versions de ligne et surcharge de 14 octetsRow versions and 14-byte overhead Aucune version de ligne, mais surcharge de 14 octetsNo row versions, but 14-byte overhead
    OuiYes NonNo Aucune version de ligne, mais surcharge de 14 octetsNo row versions, but 14-byte overhead Versions de ligne et surcharge de 14 octetsRow versions and 14-byte overhead
    OuiYes OuiYes Versions de ligne et surcharge de 14 octetsRow versions and 14-byte overhead Versions de ligne et surcharge de 14 octetsRow versions and 14-byte overhead

Voir aussiSee Also

Vue d’ensemble des groupes de disponibilité Always On (SQL Server) Overview of Always On Availability Groups (SQL Server)
À propos de l’accès de la connexion client aux réplicas de disponibilité (SQL Server) About Client Connection Access to Availability Replicas (SQL Server)
Écouteurs de groupe de disponibilité, connectivité client et basculement d’application (SQL Server) Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)
StatistiquesStatistics