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

S'applique à :SQL Server

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

Pour plus d’informations sur les implications de l’activation de l’accès en lecture seule pour un réplica secondaire ainsi qu’une présentation de l’accès aux connexions, consultez À propos de l’accès client aux connexions pour les réplicas de disponibilité (SQL Server) et Secondaires actifs : réplicas secondaires lisibles (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é Nécessite l’appartenance au rôle de serveur fixe sysadmin et soit l’autorisation de serveur CREATE AVAILABILITY GROUP, soit l’autorisation ALTER ANY AVAILABILITY GROUP, ou l’autorisation CONTROL SERVER.
Pour modifier un réplica de disponibilité : Nécessite 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é Always On 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 les accès de connexion pour le rôle principal et pour 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 sur les bases de données secondaires de cette réplique. Elles ne sont pas disponibles pour l'accès en lecture. Il s'agit du paramètre par défaut.

      Intention de lecture uniquement
      Seules les connexions en lecture seule sont autorisées sur les bases de données secondaires de cette réplique. La ou les bases de données secondaires sont toutes disponibles pour l'accès en lecture.

      Oui
      Toutes les connexions aux bases de données secondaires de cette réplique sont autorisées, mais en lecture seule. 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 aider à éviter que les clients ne connectent par erreur une charge de travail en intention de lecture à la réplique principale. Pour plus d'informations sur la propriété de connexion « Application Intent », 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é

Remarque

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 GROUPTransact-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 cette réplique. 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 sur les bases de données secondaires de cette réplique. La ou les bases de données secondaires sont toutes disponibles pour l'accès en lecture.

      TOUT
      Toutes les connexions aux bases de données secondaires de cette réplique sont autorisées, mais en lecture seule. 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 Application Intent, consultez Using Connection String Keywords with SQL Server Native Client.

    Tout
    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 désignée pour héberger la nouvelle réplique 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é

Remarque

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. 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 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 vers les bases de données de la réplique secondaire sont autorisées en 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 « Application Intent », 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.

    Remarque

    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

Étapes suivantes : après avoir configuré l’accès en lecture seule pour une réplique de disponibilité

Accès en lecture seule à une réplique secondaire lisible

  • Lorsque vous utilisez l’utilitaire bcp ou l’utilitaire sqlcmd, vous pouvez spécifier un accès en lecture seule à tout réplica secondaire activé pour la lecture seule en indiquant l’option -K ReadOnly.

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

Prérequis 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 susceptibles d’affecter les déclencheurs et les tâches après 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 client aux réplicas de disponibilité (SQL Server)

  • Last updated on