Configurer l'accès en lecture seule sur un réplica de disponibilité (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é AlwaysOn. Cette rubrique explique comment configurer l’accès à la connexion sur un réplica de disponibilité d’un groupe de disponibilité AlwaysOn dans SQL Server 2014 à 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 et pour une présentation de l’accès à la connexion, consultez À propos de l’accès de connexion client aux réplicas de disponibilité (SQL Server) et aux secondaires actifs : réplicas secondaires lisibles (groupes de disponibilité AlwaysOn).

Avant de commencer

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.

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 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 les exemples PowerShell plus loin dans cette section.

1. Accédez au répertoire (cd) de 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 ConnectionModeInSecondaryRole paramètre secondary_role_keyword, où secondary_role_keyword 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 ConnectionModeInPrimaryRoleprimary_role_keyword, où primary_role_keyword 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 afficher la syntaxe d’une applet de commande, utilisez l’applet Get-Help de commande dans l’environnement PowerShell SQL Server 2014. Pour en savoir plus, voir Get Help SQL Server PowerShell.

Pour configurer et utiliser le fournisseur PowerShell SQL Server, consultez SQL Server fournisseur 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.

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

• 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é Updatability 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],'Updatability') = N'READ_ONLY'  

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

Tâches associées

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

• Créer ou configurer un écouteur de groupe de disponibilité (SQL Server)

Contenu associé

• Always On : Proposition de valeur du réplica secondaire accessible en lecture

• Always On : Pourquoi existe-t-il deux options pour activer un réplica secondaire pour la charge de travail en lecture ?

• Always On : Configuration d’un réplica secondaire accessible en lecture

• Always On : J’ai activé un réplica secondaire accessible en lecture, mais ma requête est bloquée

• Always On : Mise à disposition des dernières statistiques disponibles sur le réplica secondaire accessible en lecture, la base de données en lecture seule et l’instantané de base de données

• Always On : Problèmes avec des statistiques sur la base de données en lecture seule, l’instantané de base de données et le réplica secondaire

• Always On : Impact sur la charge de travail principale lorsque vous exécutez la charge de travail de création de rapports sur le réplica secondaire

• Always On : Impact du mappage entre la charge de travail de création de rapports sur le réplica secondaire accessible en lecture et l’isolement d’instantané

• Always On : Réduction des blocages de thread REDO lors de l’exécution d’une charge de travail de création de rapports sur le réplica secondaire

• Always On : Réplica secondaire accessible en lecture et latence des données

Voir aussi

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)