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 2012 à l'aide de 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 une présentation de l'accès à 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 lisibles (groupes de disponibilité d'AlwaysOn).

• Avant de commencer :  

  Conditions préalables requises et restrictions

  Sécurité

• Pour configurer l'accès sur un réplica de disponibilité à l'aide de :  

  SQL Server Management Studio

  Transact-SQL

  PowerShell

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

• Tâches associées

• Contenu connexe

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

[Haut de la page]

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.

     • Intention 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 Utilisation de mots clés de chaîne de connexion avec SQL Server Native Client.

[Haut de la page]

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 GROUP Transact-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ù :

     • NO
       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 Utilisation de mots clés de chaîne de connexion avec 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

[Haut de la page]

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. 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 paramètre ConnectionModeInSecondaryRole 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 Utilisation de mots clés de chaîne de connexion avec 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 le paramètre ConnectionModeInPrimaryRole primary_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 Utilisation de mots clés de chaîne de connexion avec 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 afficher la syntaxe d'une applet de commande, utilisez l'applet de commande Get-Help dans l'environnement SQL Server 2012 PowerShell. Pour plus d'informations, consultez Obtenir de l'aide sur SQL Server PowerShell.

Pour configurer et utiliser le fournisseur SQL Server PowerShell

• Fournisseur PowerShell SQL Server

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

[Haut de la page]

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 Utilitaire bcp ou Utilitaire sqlcmd, 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 :

  	Condition préalable	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.

[Haut de la page]

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)

[Haut de la page]

Contenu connexe

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

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

• AlwaysOn : Installation du réplica de secondaire accessible en lecture

• AlwaysOn : J'ai activé un réplica secondaire accessible en lecture mais ma requête est bloquée

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

• AlwaysOn : Problèmes avec des statistiques dans la base de données en lecture seule, l'instantané de base de données et le réplica secondaire

• AlwaysOn : Impact sur la charge de travail principale lorsque vous exécutez la charge de travail de rapports sur le réplica secondaire

• AlwaysOn : Impact de la charge de travail de création de rapports de mappage sur le réplica secondaire accessible en lecture à l'isolement d'instantané

• AlwaysOn : Réduction des blocages de thread REDO lors de l'exécution d'une charge de travail de rapports sur le réplica secondaire

• AlwaysOn : réplica secondaire accessible en lecture et latence des données

[Haut de la page]

Voir aussi

Concepts

Vue d'ensemble des groupes de disponibilité AlwaysOn (SQL Server)

Secondaires actifs : réplicas secondaires lisibles (groupes de disponibilité d'AlwaysOn)

À propos de l'accès de la connexion client aux réplicas de disponibilité (SQL Server)