Configurer l’accès en lecture seule à un réplica secondaire d’un groupe de disponibilité Always On

  • Article

S'applique à : SQL Server

Par défaut l’accès en lecture/écriture et l’intention de lecture sont autorisés sur le réplica principal et aucune connexion directe n’est autorisée sur les réplicas secondaires d’un groupe de disponibilité Always On. Cette rubrique explique comment configurer l’accès de la connexion sur un réplica de disponibilité d’un groupe de disponibilité Always On dans SQL Server en utilisant SQL Server Management Studio, Transact-SQL ou PowerShell.

Pour plus d’informations sur les conséquences de l’activation de l’accès en lecture seule pour un réplica secondaire, et pour obtenir une présentation de l’accès de la connexion, consultez À propos de l’accès de la connexion client aux réplicas de disponibilité (SQL Server) et Secondaires actifs : réplicas secondaires accessibles en lecture (groupes de disponibilité Always On).

Conditions préalables requises et restrictions

  • Pour configurer un autre accès à la connexion, vous devez être connecté à l'instance de serveur qui héberge le réplica principal.

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 SQL Server Management Studio

Pour configurer l'accès sur un réplica de disponibilité

  1. Dans l'Explorateur d'objets, connectez-vous à l'instance de serveur qui héberge le réplica principal et développez l'arborescence du serveur.

  2. Développez le nœud Haute disponibilité AlwaysOn et le nœud Groupes de disponibilité .

  3. Cliquez sur le groupe de disponibilité dont vous souhaitez modifier le réplica.

  4. Cliquez avec le bouton droit sur le réplica de disponibilité, puis cliquez sur Propriétés.

  5. Dans la boîte de dialogue Propriétés du réplica de disponibilité , vous pouvez modifier l'accès à la connexion pour le rôle principal et le rôle secondaire, comme suit :

    • Pour le rôle secondaire, sélectionnez une nouvelle valeur dans la liste déroulante Rôle secondaire accessible en lecture , comme suit :

      Non
      Aucune connexion utilisateur n'est autorisée aux bases de données secondaires de ce réplica. Elles ne sont pas disponibles pour l'accès en lecture. Il s'agit du paramètre par défaut.

      Tentative de lecture uniquement
      Seules les connexions en lecture seule sont autorisées aux bases de données secondaires de ce réplica. La ou les bases de données secondaires sont toutes disponibles pour l'accès en lecture.

      Oui
      Toutes les connexions sont autorisées aux bases de données secondaires de ce réplica, mais uniquement pour l'accès en lecture. La ou les bases de données secondaires sont toutes disponibles pour l'accès en lecture.

    • Pour le rôle principal, sélectionnez une nouvelle valeur dans la liste déroulante Connexions en rôle principal , comme suit :

      Autoriser toutes les connexions
      Toutes les connexions aux bases de données sont autorisées dans le réplica principal. Il s'agit du paramètre par défaut.

      Autoriser les connexions en lecture/écriture
      Lorsque la propriété d'intention de l'application a la valeur ReadWrite ou si cette propriété n'est pas définie, la connexion est autorisée. Les connexions où la propriété de connexion d'intention de l'application a la valeur ReadOnly ne sont pas autorisées. Cela peut permettre d'éviter aux clients de se connecter à une charge de travail de tentative de lecture au réplica primaire par erreur. Pour plus d'informations sur la propriété de connexion d'intention de l'application, consultez Using Connection String Keywords with SQL Server Native Client.

Utilisation de Transact-SQL

Pour configurer l'accès sur un réplica de disponibilité

Notes

Pour obtenir un exemple de cette procédure, 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 d’un groupe de disponibilité existant, utilisez l’instruction ALTER AVAILABILITY GROUP Transact-SQL.

    • Pour configurer l'accès à la connexion pour le rôle secondaire, dans la clause ADD REPLICA ou MODIFY REPLICA WITH, spécifiez l'option SECONDARY_ROLE, comme suit :

      SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

      où :

      Non
      Aucune connexion directe n'est autorisée aux bases de données secondaires de ce réplica. Elles ne sont pas disponibles pour l'accès en lecture. Il s'agit du paramètre par défaut.

      READ_ONLY
      Seules les connexions en lecture seule sont autorisées aux bases de données secondaires de ce réplica. La ou les bases de données secondaires sont toutes disponibles pour l'accès en lecture.

      ALL
      Toutes les connexions sont autorisées aux bases de données secondaires de ce réplica, mais uniquement pour l'accès en lecture. La ou les bases de données secondaires sont toutes disponibles pour l'accès en lecture.

  3. Pour configurer l'accès à la connexion pour le rôle principal, dans la clause ADD REPLICA ou MODIFY REPLICA WITH, spécifiez l'option PRIMARY_ROLE, comme suit :

    PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

    où :

    READ_WRITE
    Les connexions où la propriété de connexion d’intention de l’application a la valeur ReadOnly ne sont pas autorisées. Lorsque la propriété d'intention de l'application a la valeur ReadWrite ou si cette propriété n'est pas définie, la connexion est autorisée. Pour plus d'informations sur la propriété de connexion d'intention de l'application, consultez Using Connection String Keywords with SQL Server Native Client.

    ALL
    Toutes les connexions aux bases de données sont autorisées dans le réplica principal. Il s'agit du paramètre par défaut.

Exemple (Transact-SQL)

L'exemple suivant ajoute un réplica secondaire à un groupe de disponibilité nommé AG2. Une instance de serveur autonome, COMPUTER03\HADR_INSTANCE, est spécifiée pour héberger le nouveau réplica de disponibilité. Ce réplica est configuré pour autoriser uniquement les connexions en lecture/écriture pour le rôle principal et pour autoriser uniquement les connexions de tentative de lecture pour le rôle secondaire.

ALTER AVAILABILITY GROUP AG2   
   ADD REPLICA ON   
      'COMPUTER03\HADR_INSTANCE' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER03:7022',  
         PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ),  
         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY )  
         );   
GO

Utilisation de PowerShell

Pour configurer l'accès sur un réplica de disponibilité

Notes

Pour obtenir un exemple de code, consultez Exemple (PowerShell), plus loin dans cette section.

  1. Remplacez le répertoire (cd) par 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 l’accès à la connexion pour le rôle secondaire, spécifiez le paramètre ConnectionModeInSecondaryRolemot_clé_rôle_secondaire , où mot_clé_rôle_secondaire correspond à l’une des valeurs suivantes :

      AllowNoConnections
      Aucune connexion directe n'est autorisée aux bases de données dans le réplica secondaire et les bases de données ne sont pas disponibles pour un accès en lecture. Il s'agit du paramètre par défaut.

      AllowReadIntentConnectionsOnly
      Seules sont autorisées les connexions aux bases de données dans le réplica secondaire où la propriété d’intention de l’application est définie sur ReadOnly. Pour plus d'informations sur cette propriété, consultez Using Connection String Keywords with SQL Server Native Client.

      AllowAllConnections
      Toutes les connexions sont autorisées aux bases de données dans le réplica secondaire pour un accès en lecture seule.

    • Pour configurer l’accès à la connexion pour le rôle principal, spécifiez ConnectionModeInPrimaryRolemot_clé_rôle_principal, où mot_clé_rôle_principal correspond à l’une des valeurs suivantes :

      AllowReadWriteConnections
      Les connexions où la propriété de connexion d'intention de l'application a la valeur ReadOnly ne sont pas autorisées. Lorsque la propriété d'intention de l'application a la valeur ReadWrite ou si cette propriété n'est pas définie, la connexion est autorisée. Pour plus d'informations sur la propriété de connexion d'intention de l'application, consultez Using Connection String Keywords with SQL Server Native Client.

      AllowAllConnections
      Toutes les connexions aux bases de données sont autorisées dans le réplica principal. Il s'agit du paramètre par défaut.

    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.

Pour configurer et utiliser le fournisseur SQL Server PowerShell

Exemple (PowerShell)

L'exemple suivant définit les paramètres ConnectionModeInSecondaryRole et ConnectionModeInPrimaryRole sur AllowAllConnections.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" `   
-InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ConnectionModeInPrimaryRole "AllowAllConnections" `   
-InputObject $primaryReplica

Suivi : après avoir configuré l'accès en lecture seule pour un réplica de disponibilité

Accès en lecture seule aux réplicas secondaires accessibles en lecture.

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

  • Pour permettre aux applications clientes de se connecter aux réplicas secondaires accessibles en lecture :

Configuration requise Lien
Assurez-vous que le groupe de disponibilité possède un écouteur. Créer ou configurer un écouteur de groupe de disponibilité (SQL Server)
Configurez le routage en lecture seule pour le groupe de disponibilité. Configurer le routage en lecture seule pour un groupe de disponibilité (SQL Server)

Facteurs qui peuvent affecter les déclencheurs et les travaux à la suite d'un basculement

Si vous avez des déclencheurs et des travaux qui vont échouer lors de l'exécution sur une base de données secondaire inaccessible en lecture ou sur une base de données secondaire accessible en lecture, vous devez générer un script pour les déclencheurs et travaux à vérifier sur un réplica donné afin de déterminer si la base de données est une base de données principale ou une base de données secondaire accessible en lecture. Pour obtenir ces informations, utilisez la fonction DATABASEPROPERTYEX pour retourner la propriété Updateability de la base de données. Pour identifier une base de données en lecture seule, spécifiez la valeur READ_ONLY, comme suit :

DATABASEPROPERTYEX([db name],'UpdateAbility') = N'READ_ONLY'

Pour identifier une base de données en lecture-écriture, spécifiez la valeur READ_WRITE.

Tâches associées

Contenu associé

Voir aussi

Vue d’ensemble des groupes de disponibilité Always On (SQL Server)
Secondaires actifs : réplicas secondaires accessibles en lecture (groupes de disponibilité Always On)
À propos de l'accès de la connexion client aux réplicas de disponibilité (SQL Server)