Configurer le routage en lecture seule pour un groupe de disponibilité Always On

  • Article

S'applique à : SQL Server

Pour configurer un groupe de disponibilité Always On et prendre en charge le routage en lecture seule dans SQL Server, vous pouvez utiliser Transact-SQL ou PowerShell. Leroutage en lecture seule fait référence à la capacité de SQL Server d’acheminer les demandes de connexion en lecture seule applicables à un réplica secondaire lisible Always On disponible (autrement dit, un réplica configuré pour autoriser des charges de travail en lecture seule lorsqu’il s’exécute sous le rôle secondaire). Pour prendre en charge le routage en lecture seule, le groupe de disponibilité doit posséder un écouteur de groupe de disponibilité. Les clients en lecture seule doivent diriger leurs demandes de connexion à cet écouteur, et les chaînes de connexion du client doivent spécifier l'intention d'application « en lecture seule ». C’est-à-dire qu’ils doivent être des requêtes de connexion « intention de lecture ».

Le routage en lecture seule est disponible dans SQL Server 2016 (13.x) et versions ultérieures.

Notes

Pour plus d’informations sur la configuration d’un réplica secondaire lisible, consultez Configurer l’accès en lecture seule sur un réplica de disponibilité (SQL Server).

Prérequis

Quelles sont les propriétés du réplica que vous avez besoin de configurer pour prendre en charge le routage en lecture seule ?

  • Pour chaque réplica secondaire lisible qui doit prendre en charge le routage en lecture seule, vous devez spécifier 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.

    Pour plus d’informations sur le calcul de l’URL de routage en lecture seule pour un réplica de disponibilité, consultez Calcul de l’URL de routage en lecture seule pour Always On

  • Pour chaque réplica de disponibilité qui doit prendre en charge le routage en lecture seule lorsqu’il est le réplica principal, vous devez spécifier 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.

    Notes

    Les demandes de connexion d’intention de lecture sont acheminées à la première entrée disponible sur la liste de routage en lecture seule du réplica principal actuel. Toutefois, l’équilibrage de charge entre les réplicas en lecture seule est pris en charge. Pour plus d’informations, consultez Configurer l’équilibrage de charge entre des réplicas en lecture seule.

Notes

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).

Autorisations

Tâche Autorisations
Pour configurer des réplicas lors de la création d'un groupe de disponibilité Requiert l’appartenance au rôle serveur fixe sysadmin et l’autorisation de serveur CREATE AVAILABILITY GROUP, l’autorisation ALTER ANY AVAILABILITY GROUP ou l’autorisation CONTROL SERVER.
Pour modifier un réplica de disponibilité : Requiert l'autorisation ALTER AVAILABILITY GROUP sur le groupe de disponibilité, l'autorisation CONTROL AVAILABILITY GROUP, l'autorisation ALTER ANY AVAILABILITY GROUP ou l'autorisation CONTROL SERVER.

Utilisation de Transact-SQL

Configurer une liste de routage en lecture seule

Procédez comme suit pour configurer le routage en lecture seule à l’aide de Transact-SQL. Pour obtenir un exemple de code, consultez Exemple (Transact-SQL), plus loin dans cette section.

  1. Connectez-vous à l'instance de serveur qui héberge le réplica principal.

  2. Si vous spécifiez un réplica pour un nouveau groupe de disponibilité, utilisez l’instruction CREATE AVAILABILITY GROUPTransact-SQL. Si vous ajoutez ou modifiez un réplica pour un groupe de disponibilité existant, utilisez l'instruction Transact-SQL ALTER AVAILABILITY GROUP.

    • Pour configurer le routage en lecture seule pour le rôle secondaire, dans la clause ADD REPLICA ou MODIFY REPLICA WITH, spécifiez l'option SECONDARY_ROLE, comme suit :

      SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')

      Les paramètres de l'URL de routage en lecture sont les suivants :

      system-address
      Chaîne, telle qu'un nom de système, un nom de domaine complet ou une adresse IP, qui identifie de manière unique l'ordinateur de destination.

      port
      Numéro de port utilisé par le moteur de base de données de l'instance de SQL Server .

      Par exemple : SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')

      Dans une clause MODIFY REPLICA, ALLOW_CONNECTIONS est facultatif si le réplica est déjà configuré pour autoriser les connexions en lecture seule.

      Pour plus d’informations, consultez Calcul de l’URL de routage en lecture seule pour Always On.

    • Pour configurer le routage en lecture seule pour le rôle principal, dans la clause ADD REPLICA ou MODIFY REPLICA WITH, spécifiez l'option PRIMARY_ROLE, comme suit :

      PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('serveur' [ , ...n ] ))

      où server indique une instance de serveur qui héberge un réplica secondaire en lecture seule dans le groupe de disponibilité.

      Par exemple : PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))

      Notes

      Vous devez définir le routage en lecture seule avant de configurer la liste de routage en lecture seule.

Configurer l’équilibrage de charge entre des réplicas en lecture seule

Depuis SQL Server 2016 (13.x), vous pouvez configurer l’équilibrage de charge sur un ensemble de réplicas en lecture seule. Auparavant, le routage en lecture seule dirigeait toujours le trafic vers le premier réplica disponible dans la liste de routage. Pour tirer parti de cette fonctionnalité, utilisez un seul niveau de parenthèses imbriquées autour des instances de serveur READ_ONLY_ROUTING_LIST dans les commandes CREATE AVAILABILITY GROUP ou ALTER AVAILABILITY GROUP .

Par exemple, la liste de routage suivante équilibre la charge de la demande de connexion d’intention de lecture entre deux réplicas en lecture seule : Server1 et Server2. Les parenthèses imbriquées autour de ces serveurs identifient l’ensemble dont la charge est équilibrée. Si aucun réplica n’est disponible dans cet ensemble, la fonctionnalité tente de se connecter séquentiellement aux autres réplicas, Server3 et Server4, dans la liste de routage en lecture seule.

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), 'Server3', 'Server4')

Notez que chaque entrée de la liste de routage peut représenter un ensemble de réplicas en lecture seule dont la charge est équilibrée. l’exemple ci-dessous illustre ce cas de figure.

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), ('Server3', 'Server4', 'Server5'), 'Server6')

Seul un niveau de parenthèses imbriquées est pris en charge.

Exemple (Transact-SQL)

L'exemple suivant modifie deux réplicas de disponibilité d'un groupe de disponibilité existant, AG1 pour prendre en charge le routage en lecture seule si un de ces réplicas détient actuellement le rôle principal. Pour identifier les instances de serveur qui hébergent le réplica de disponibilité, cet exemple spécifie les noms d’instance COMPUTER01 et COMPUTER02.

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));  
  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));  
  
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));  
  
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
GO

Utilisation de PowerShell

Configurer une liste de routage en lecture seule

Procédez comme suit pour configurer le routage en lecture seule à l’aide de PowerShell. Pour obtenir un exemple de code, consultez Exemple (PowerShell), plus loin dans cette section.

  1. Définissez la valeur par défaut (cd) sur l’instance de serveur qui héberge le réplica principal.

  2. Quand vous ajoutez un réplica de disponibilité à un groupe de disponibilité, utilisez l’applet de commande New-SqlAvailabilityReplica . Quand vous modifiez un réplica de disponibilité existant, utilisez l’applet de commande Set-SqlAvailabilityReplica . Les paramètres pertinents sont les suivants :

    • Pour configurer le routage en lecture seule pour le rôle secondaire, spécifiez le paramètre ReadonlyRoutingConnectionUrl"url" .

      où url est le nom de domaine complet (FQDN) de la connectivité et le port à utiliser lors de l’acheminement vers le réplica pour les connexions en lecture seule. Par exemple : -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"

      Pour plus d’informations, consultez Calcul de l’URL de routage en lecture seule pour Always On.

    • Pour configurer l’accès à la connexion pour le rôle principal, spécifiez ReadonlyRoutingList"server" [ , ...n ], où server identifie une instance de serveur qui héberge un réplica secondaire en lecture seule dans le groupe de disponibilité. Par exemple : -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"

      Notes

      Vous devez définir l'URL de routage en lecture seule d'un réplica avant de configurer sa liste de routage en lecture seule.

    Notes

    Pour voir la syntaxe d’une applet de commande, utilisez l’applet de commande Get-Help dans l’environnement SQL Server PowerShell. Pour en savoir plus, voir Get Help SQL Server PowerShell.

Configurer et utiliser le fournisseur PowerShell SQL Server

Exemple (PowerShell)

L'exemple suivant configure le réplica principal et un réplica secondaire dans un groupe de disponibilité pour le routage en lecture seule. D'abord, l'exemple affecte une URL de routage en lecture seule à chaque réplica. Il définit ensuite la liste de routage en lecture seule sur le réplica principal. Les connexions avec la propriété « ReadOnly » définie dans la chaîne de connexion sont redirigées vers le réplica secondaire. Si ce réplica secondaire n’est pas lisible (comme le détermine le paramètre ConnectionModeInSecondaryRole ), la connexion est renvoyée vers le réplica principal.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"  
  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

Suivi : après la configuration du routage en lecture seule

Une fois le réplica principal actuel et les réplicas secondaires lisibles configurés pour prendre en charge le routage en lecture seule dans les deux rôles, les réplicas secondaires lisibles peuvent recevoir des demandes de connexion d'intention de lecture des clients qui se connectent via l'écouteur du groupe de disponibilité.

Conseil

Quand vous utilisez bcp Utility ou sqlcmd Utility, vous pouvez spécifier l’accès en lecture seule à n’importe quel réplica secondaire qui est activé pour l’accès en lecture seule en spécifiant le commutateur -K ReadOnly .

Exigences et recommandations pour les chaînes de connexion clientes

Pour qu'une application cliente utilise le routage en lecture seule, sa chaîne de connexion doit respecter les conditions suivantes :

  • Utiliser le protocole TCP.

  • Définir l'attribut/propriété d'intention de l'application en lecture seule.

  • Référencer l'écouteur d'un groupe de disponibilité qui est configuré pour prendre en charge le routage en lecture seule.

  • Référencer une base de données dans ce groupe de disponibilité.

En outre, nous recommandons que les chaînes de connexion autorisent le basculement de sous-réseaux multiples, ce qui permet de prendre en charge un thread client parallèle pour chaque réplica sur chaque sous-réseau. Cela réduit le temps de reconnexion du client après un basculement.

La syntaxe d'une chaîne de connexion dépend du fournisseur SQL Server utilisé par l'application. L'exemple de chaîne de connexion suivant pour le fournisseur de données .NET Framework Data Provider 4.0.2 pour SQL Server illustre les parties d'une chaîne de connexion requises et recommandées pour utiliser le routage en lecture seule.

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

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

Si le routage en lecture seule ne fonctionne pas correctement

Pour plus d’informations sur la résolution des problèmes liés à la configuration du routage en lecture seule, consultez Le routage en lecture seule ne fonctionne pas correctement.

Étapes suivantes

Pour consulter les configurations de routage en lecture seule

Pour configurer l'accès à la connexion du client

Pour utiliser les chaînes de connexion dans des applications

Blogs :

Livres blancs :

Contenu supplémentaire