Partager via

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

  • Article

Pour configurer un groupe de disponibilité AlwaysOn afin de prendre en charge le routage en lecture seule dans SQL Server 2014, vous pouvez utiliser Transact-SQL ou PowerShell. Le routage en lecture seule fait référence à la capacité des SQL Server à acheminer les demandes de connexion en lecture seule éligibles vers un réplica secondaire accessible en lecture AlwaysOn disponible (c’est-à-dire un réplica configuré pour autoriser les charges de travail en lecture seule lors de l’exécution 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 ».

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

Notes

La configuration du routage en lecture seule n’est pas prise en charge par SQL Server Management Studio.

Avant de commencer

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

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

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

Sécurité

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

Pour configurer le routage en lecture seule

Notes

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

    • 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 =('server' [ ,... 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.

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

Pour configurer le routage en lecture seule

Notes

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. Lorsque vous ajoutez un réplica de disponibilité à un groupe de disponibilité, utilisez l'applet de commande New-SqlAvailabilityReplica. Lorsque 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 AlwaysOn.

    • Pour configurer l’accès à la connexion pour le rôle principal, spécifiez ReadonlyRoutingList « server » [ ,... n ], où le serveur identifie un serveur instance 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 afficher la syntaxe d’une applet de commande, utilisez l’applet Get-Help de commande dans l’environnement PowerShell SQL Server. Pour en savoir plus, voir Get Help SQL Server PowerShell.

Pour configurer et utiliser le fournisseur PowerShell SQL Server, consultez SQL Server fournisseur PowerShell et Obtenir de l’aide SQL Server PowerShell.

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 accessible en lecture (comme déterminé par le paramètre ConnectionModeInSecondaryRole), la connexion sera 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

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

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.

Tâches associées

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

Contenu associé

Voir aussi

Vue d'ensemble des groupes de disponibilité AlwaysOn (SQL Server)
Vue d'ensemble des groupes de disponibilité AlwaysOn (SQL Server)
Secondaires actifs : Réplicas secondaires accessibles en lecture (groupes de disponibilité AlwaysOn)
À propos de l'accès de la connexion client aux réplicas de disponibilité (SQL Server)
Écouteurs de groupe de disponibilité, connectivité client et basculement d’application (SQL Server)