Résoudre des problèmes de configuration des groupes de disponibilité Always On (SQL Server)

S'applique à :SQL Server

Cet article fournit des informations pour vous aider à résoudre les problèmes classiques liés à la configuration des instances de serveur pour les groupes de disponibilité Always On. Voici quelques problèmes courants de configuration : les groupes de disponibilité Always On sont désactivés, les comptes ne sont pas correctement configurés, le point de terminaison de mise en miroir de bases de données est introuvable, le point de terminaison est inaccessible (erreur SQL Server 1418), l’accès réseau n’existe pas et la jointure de base de données a échoué (erreur SQL Server 35250).

Dans cette rubrique :

Les groupes de disponibilité Always On ne sont pas activés

La fonctionnalité Groupes de disponibilité Always On doit être activée sur chacune des instances de SQL Server.

Si la fonctionnalité Groupes de disponibilité Always On n’est pas activée, vous obtenez ce message d’erreur lorsque vous essayez de créer un groupe de disponibilité sur SQL Server.

The Always On Availability Groups feature must be enabled for server instance 'SQL1VM' before you can create an availability group on this instance. To enable this feature, open the SQL Server Configuration Manager, select SQL Server Services, right-click on the SQL Server service name, select Properties, and use the Always On Availability Groups tab of the Server Properties dialog. Enabling Always On Availability Groups may require that the server instance is hosted by a Windows Server Failover Cluster (WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

Le message d’erreur indique clairement que la fonctionnalité AG n’est pas activée et vous explique également comment l’activer. Il existe deux scénarios dans lesquels vous pouvez vous retrouver dans cet état, en plus de celui évident où AG n’a pas été activé à l'origine.

1. Si SQL Server a été installé et que la fonctionnalité Groupes de disponibilité Always On a été activée avant d’installer la fonctionnalité de clustering de basculement Windows, vous risquez d’obtenir cette erreur lorsque vous tentez de créer un groupe de disponibilité Always On.
2. Si vous supprimez une fonctionnalité de clustering de basculement Windows existante et la régénérez alors que SQL Server a toujours Always On configuré, lorsque vous tentez d’utiliser à nouveau l'Availability Group, cette erreur peut se produire.

Dans ce cas, vous pouvez effectuer les étapes suivantes pour résoudre ce problème :

1. Désactiver la fonctionnalité AG
2. Redémarrez le service SQL Server
3. Réactiver la fonctionnalité AG
4. Redémarrer le service SQL

Pour plus d’informations, consultez Activer et désactiver les groupes de disponibilité Always On (SQL Server).

Comptes

Les comptes sous lesquels SQL Server est en cours d'exécution doivent être configurés correctement.

1. Les comptes possèdent-ils les autorisations appropriées ?

   1. Si les partenaires s'exécutent sous le même compte de domaine, les connexions d’utilisateur correctes existent automatiquement dans les deux bases de données principales. Cela simplifie la configuration de la sécurité et est recommandé.

   2. Si deux instances de serveur s'exécutent sous des comptes différents, chaque compte doit être créé dans la base de données master sur l'instance de serveur distant, et le principal de serveur doit se voir accorder les autorisations CONNECT pour se connecter au point de terminaison de mise en miroir de la base de données de cette instance de serveur. Pour plus d’informations, consultez Configurer des comptes de connexion pour la mise en miroir de bases de données ou les groupes de disponibilité Always On (SQL Server). Vous pouvez utiliser la requête suivante sur chaque instance pour vérifier si les connexions ont des autorisations CONNECT :

   SELECT 
     perm.class_desc,
     prin.name,
     perm.permission_name,
     perm.state_desc,
     prin.type_desc as PrincipalType,
     prin.is_disabled
   FROM sys.server_permissions perm
     LEFT JOIN sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
     LEFT JOIN sys.tcp_endpoints tep ON perm.major_id = tep.endpoint_id
   WHERE 
     perm.class_desc = 'ENDPOINT'
     AND perm.permission_name = 'CONNECT'
     AND tep.type = 4    
   

2. Si SQL Server s'exécute en tant que compte intégré, tel que Système local, Service local ou Service réseau ou comme compte qui n'appartient pas au domaine, vous devez utiliser des certificats pour l'authentification de point de terminaison. Si vos comptes de service utilisent des comptes de domaine au sein du même domaine, vous pouvez choisir d'accorder l'accès CONNECT pour chaque compte de service sur tous les emplacements de réplica ou vous pouvez utiliser des certificats. Pour plus d’informations, consultez Utiliser des certificats pour un point de terminaison de mise en miroir de bases de données (Transact-SQL).

Points de terminaison

Les points de terminaison doivent être correctement configurés.

1. Assurez-vous que chaque instance de SQL Server qui hébergera une réplique disponible (chaque emplacement de réplica) possède un point de terminaison de mise en miroir de bases de données. Pour déterminer si un point de terminaison de mise en miroir de bases de données existe sur une instance de serveur donnée, utilisez l’affichage catalogue sys.database_mirroring_endpoints :

   SELECT name, state_desc FROM sys.database_mirroring_endpoints  
   

   Pour plus d’informations sur la création des points de terminaison, consultez Créer un point de terminaison de mise en miroir de bases de données pour l’authentification Windows (Transact-SQL) ou Autoriser un point de terminaison de mise en miroir de bases de données afin d’utiliser des certificats pour les connexions sortantes (Transact-SQL).

2. Vérifiez les numéros de ports.

   Pour identifier le port actuellement associé au point de terminaison de mise en miroir de bases de données d’une instance de serveur, utilisez l’instruction Transact-SQL suivante :

   SELECT type_desc, port FROM sys.tcp_endpoints;  
   GO  
   

3. Pour les problèmes de configuration des groupes de disponibilité Always On difficiles à expliquer, nous vous conseillons d'examiner chaque instance de serveur pour déterminer si elle est à l'écoute sur les ports corrects.

4. Vérifiez que les points de terminaison sont démarrés (STATE=STARTED). Sur chaque instance de serveur, utilisez l’instruction Transact-SQL suivante :

   SELECT state_desc FROM sys.database_mirroring_endpoints  
   

   Pour plus d’informations sur la colonne state_desc, consultez sys.database_mirroring_endpoints (Transact-SQL).

   Pour démarrer un point de terminaison, utilisez l’instruction Transact-SQL suivante :

   ALTER ENDPOINT Endpoint_Mirroring   
   STATE = STARTED   
   AS TCP (LISTENER_PORT = <port_number>)  
   FOR database_mirroring (ROLE = ALL);  
   GO  
   

   Pour plus d’informations, consultez ALTER ENDPOINT (Transact-SQL).

   Note

   Dans certains cas, si le point de terminaison est démarré, mais que les réplicas du groupe de disponibilité (AG) ne communiquent pas, essayez d’arrêter et de redémarrer le point de terminaison. Vous pouvez utiliser ALTER ENDPOINT [Endpoint_Mirroring] STATE = STOPPED suivi de ALTER ENDPOINT [Endpoint_Mirroring] STATE = STARTED.

5. Assurez-vous que la connexion de l'autre serveur possède l'autorisation CONNECT. Pour déterminer qui a une autorisation CONNECT sur un point de terminaison, pour chaque instance de serveur, utilisez l’instruction Transact-SQL suivante :

   SELECT 'Metadata Check';  
   SELECT EP.name, SP.STATE,   
      CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))   
         AS GRANTOR,   
      SP.TYPE AS PERMISSION,  
      CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))   
         AS GRANTEE   
      FROM sys.server_permissions SP , sys.endpoints EP  
      WHERE SP.major_id = EP.endpoint_id  
      ORDER BY Permission,grantor, grantee;   
   

6. Vérifiez que le nom de serveur correct est utilisé dans l’URL du point de terminaison

   Pour le nom du serveur dans une URL de point de terminaison, il est recommandé d’utiliser le nom de domaine complet (FQDN), bien que vous puissiez utiliser n’importe quel nom qui identifie de façon unique l’ordinateur. L'adresse de serveur peut être un nom Netbios (si les systèmes sont dans le même domaine), un nom de domaine complet ou une adresse IP (de préférence une adresse IP statique). L’utilisation du nom de domaine complet est l’option recommandée.

   Si vous avez déjà défini une URL de point de terminaison, vous pouvez l’interroger à l’aide de :

   select endpoint_url from sys.availability_replicas
   

   Ensuite, comparez la sortie endpoint_url au nom du serveur (nom Netbios ou FQDN). Pour interroger le nom du serveur, exécutez les commandes suivantes dans PowerShell sur le réplica localement :

   $env:COMPUTERNAME
   [System.Net.Dns]::GetHostEntry([string]$env:computername).HostName
   

   Pour valider le nom du serveur sur un ordinateur distant, exécutez cette commande à partir de PowerShell.

   $servername_from_endpoint_url = "server_from_endpoint_url_output"
   
   Test-NetConnection -ComputerName $servername_from_endpoint_url
   

   Pour plus d’informations, consultez Spécifier l’URL du point d'accès lors de l’ajout ou de la modification d’un réplica de disponibilité (SQL Server).

Accès réseau

Chaque instance de serveur qui héberge un réplica de disponibilité doit être en mesure d’accéder au port de chaque autre instance de serveur via TCP. Ceci est d'autant plus important que les instances de serveur peuvent se trouver dans différents domaines, entre lesquels aucune relation d'approbation n'a été établie (domaines non approuvés). Vérifiez si vous pouvez vous connecter aux points de terminaison en procédant comme suit :

• Utilisez Test-NetConnection (équivalent à Telnet) pour valider la connectivité. Voici des exemples de commandes que vous pouvez utiliser :

  $server_name = "your_server_name"
  $IP_address = "your_ip_address"
  $port_number = "your_port_number"
  
  Test-NetConnection -ComputerName $server_name -Port $port_number
  Test-NetConnection -ComputerName $IP_address -Port $port_number
  

• Si le point de terminaison écoute et que la connexion réussit, vous voyez « TcpTestSucceeded : True ». Si ce n’est pas le cas, vous recevez un message « TcpTestSucceededed : False ».

• Si la connexion Test-NetConnection (Telnet) à l’adresse IP fonctionne, mais pas à ServerName, il y a probablement un problème de résolution de noms ou DNS

• Si la connexion fonctionne par ServerName et non par adresse IP, plusieurs points de terminaison peuvent être définis sur ce serveur (une autre instance SQL peut-être) qui écoute sur ce port. Bien que l’état du point de terminaison sur l’instance en question indique « STARTED », une autre instance peut avoir le port lié et empêcher l’instance correcte d’écouter et d’établir des connexions TCP.

• Si Test-NetConnection ne parvient pas à se connecter, recherchez les logiciels pare-feu et/ou antivirus susceptibles de bloquer le port du point de terminaison en question. Vérifiez le paramètre du pare-feu pour voir s’il permet la communication de port du point de terminaison entre les instances de serveur qui hébergent le réplica principal et le réplica secondaire (port 5022 par défaut). Exécutez le script PowerShell suivant pour rechercher les règles de trafic entrant désactivées

• Si vous exécutez SQL Server sur une machine virtuelle Azure, vous devez également vérifier que le groupe de sécurité réseau (NSG) autorise le trafic vers le port du point de terminaison. Vérifiez le paramètre du pare-feu (et le NSG pour la machine virtuelle Azure) pour voir s’il permet la communication de port du point de terminaison entre les instances de serveur qui hébergent le réplica principal et le réplica secondaire (port 5022 par défaut)

  Get-NetFirewallRule -Action Block -Enabled True -Direction Inbound |Format-Table
  

• Capturez la sortie de la cmdlet Get-NetTCPConnection (équivalente à NETSTAT -a) et vérifiez que l’état est LISTENING ou ESTABLISHED sur l’IP:Port du point de terminaison spécifié.

  Get-NetTCPConnection 
  

Écouteur

Pour une configuration correcte d’un écouteur de groupe de disponibilité, suivez « Configurer un écouteur pour un groupe de disponibilité Always On »

1. Une fois l’écouteur configuré, vous pouvez valider l’adresse IP et le port sur lequel il écoute à l’aide de la requête suivante :

   $server_name = $env:computername  #replace this with your sql instance "server\instance"
   
   sqlcmd -E -S$server_name -Q"SELECT dns_name AS AG_listener_name, port, ip_configuration_string_from_cluster 
   FROM sys.availability_group_listeners"
   

2. Vous pouvez également trouver les informations du listen maître ainsi que les ports SQL Server à l’aide de cette requête :

   $server_name = $env:computername      #replace this with your sql instance "server\instance"
   
   sqlcmd -E -S($server_name) -Q("SELECT  convert(varchar(32), SERVERPROPERTY ('servername')) servername, convert(varchar(32),ip_address) ip_address, port, type_desc,state_desc, start_time 
   FROM sys.dm_tcp_listener_states 
   WHERE ip_address not in ('127.0.0.1', '::1') and type <> 2")
   

3. Si vous devez établir une connectivité à l’écouteur et soupçonnez qu’un port est bloqué, vous pouvez effectuer un test à l’aide de la cmdlet PowerShell Test-NetConnection (équivalente à telnet).

   $listener_name = "your_ag_listener"
   $IP_address = "your_ip_address"
   $port_number = "your_port_number"
   
   Test-NetConnection -ComputerName $listener_name -Port $port_number
   Test-NetConnection -ComputerName $IP_address -Port $port_number
   

4. Enfin, vérifiez si l’écouteur écoute sur le port spécifié :

   $port_number = "your_port_number"
   
   Get-NetTCPConnection -LocalPort $port_number -State Listen
   

Accès au point de terminaison (erreur SQL Server 1418)

Ce message SQL Server indique que l’adresse réseau du serveur spécifiée dans l’URL de point de terminaison n’est pas accessible ou n’existe pas et suggère de vérifier le nom de l’adresse réseau et de réexécuter la commande.

Échec de jointure de base de données (erreur SQL Server 35250)

Cette section décrit les causes possibles et la résolution d'un échec de l'ajout des bases de données secondaires au groupe de disponibilité en raison de l'inactivité de la connexion au réplica principal. Voici le message d’erreur complet :

Msg 35250 The connection to the primary replica is not active. The command cannot be processed.

Résolution :

Le récapitulatif des étapes est décrit ci-dessous.

Pour obtenir des instructions pas à pas détaillées, consultez l’erreur MSSQLSERVER_35250 du moteur

1. Assurez-vous que le point de terminaison a été créé et lancé.
2. Vérifiez si vous pouvez vous connecter au point de terminaison via Telnet et vérifiez qu’aucune règle de pare-feu ne bloque la connectivité
3. Recherchez les erreurs dans le système. Vous pouvez interroger le sys.dm_hadr_availability_replica_states pour le last_connect_error_number qui peut vous aider à diagnostiquer le problème de jointure.
4. Assurez-vous que le point de terminaison est défini pour correspondre correctement à l'adresse IP et au port qu'AG utilise.
5. Vérifiez si le compte de service réseau dispose de l’autorisation CONNECT sur le point de terminaison.
6. Vérifiez les problèmes éventuels de résolution de noms
7. Vérifiez que votre serveur SQL exécute une build récente (de préférence la dernière build pour éviter les problèmes déjà corrigés.

Le routage en lecture seule ne fonctionne pas correctement

1. Vérifiez que vous avez configuré le routage en lecture seule en suivant le document Configurer le routage en lecture seule.

2. Garantir la prise en charge du pilote client

   L’application cliente doit utiliser un fournisseur client qui prend en charge le ApplicationIntent paramètre. Consultez Prise en charge pour la connectivité des pilotes et des clients pour les groupes de disponibilité

   Note

   Si vous vous connectez à un écouteur de nom de réseau distribué (DNN), le fournisseur doit également prendre en charge le paramètre MultiSubnetFailover

3. Vérifier que les propriétés de chaîne de connexion sont correctement définies

   Pour que le routage en lecture seule fonctionne correctement, votre application cliente doit utiliser ces propriétés dans la chaîne de connexion :

   • Nom de base de données appartenant au groupe de disponibilité
   • Nom de l’écouteur du groupe de disponibilité
     • Si vous utilisez DNN, vous devez spécifier le nom de l’écouteur DNN et le numéro de port DNN <DNN name,DNN port>
   • ApplicationIntent est défini sur Lecture seule
   • MultiSubnetFailover doit être défini sur true pour le nom de réseau distribué (DNN)

   Exemples

   Cet exemple présente la chaîne de connexion du fournisseur .NET Microsoft.Data.SqlClient ou System.Data.SqlClient pour un auditeur de nom de réseau virtuel (VNN) :

   Server=tcp:VNN_AgListener,1433;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
   

   Cet exemple illustre la chaîne de connexion pour le fournisseur .NET Microsoft.Data.SqlClient ou System.Data.SqlClient pour un écouteur de noms de réseau distribué (DNN) :

   Server=tcp:DNN_AgListener,DNN_Port;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
   

   Note

   Si vous utilisez des programmes de ligne de commande comme SQLCMD, vérifiez que vous spécifiez les commutateurs appropriés pour le nom du serveur. Par exemple, dans SQLCMD, vous devez utiliser le commutateur en majuscule -S qui spécifie le nom du serveur, et non le commutateur en minuscules -s utilisé pour le séparateur de colonnes.
   Exemple : sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M

4. Assurez-vous que le listener du groupe de disponibilité est en ligne. Pour s'assurer que l'interlocuteur du groupe de disponibilité est en ligne, exécutez la requête suivante sur le réplica principal :

   SELECT * FROM sys.dm_tcp_listener_states;
   

   Si vous découvrez que l’écouteur est hors connexion, vous pouvez essayer de le mettre en ligne à l’aide d’une commande comme celle-ci :

   ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'AG_Listener';
   

5. Assurez-vous que READ_ONLY_ROUTING_LIST est correctement rempli. Sur le réplica principal, vérifiez que l’option READ_ONLY_ROUTING_LIST contient uniquement les instances de serveur qui hébergent des réplicas secondaires accessibles en lecture.

   Pour afficher les propriétés de chaque réplica, vous pouvez exécuter cette requête et examiner le point de terminaison de connectivité (URL) du réplica en lecture seule.

   SELECT replica_id, replica_server_name, secondary_role_allow_connections_desc, read_only_routing_url 
   FROM sys.availability_replicas;   
   

   Pour afficher une liste de routage en lecture seule et la comparer à l’URL du point de terminaison :

   SELECT * FROM sys.availability_read_only_routing_lists;
   

   Pour modifier une liste de routage en lecture seule, vous pouvez utiliser une requête comme suit :

   ALTER AVAILABILITY GROUP [AG1]   
   MODIFY REPLICA ON  
   N'COMPUTER02' WITH   
   (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
   

   Pour plus d’informations, consultez Configurer le routage en lecture seule pour un groupe de disponibilité - SQL Server Always On

6. Vérifiez que le port READ_ONLY_ROUTING_URL est ouvert. Vérifiez que le Pare-feu Windows ne bloque pas le port READ_ONLY_ROUTING_URL. Configurez un Pare-feu Windows pour l’accès au moteur de base de données sur chaque réplica dans le read_only_routing_list et pour tous les clients qui se connectent à ces réplicas.

   Note

   Si vous exécutez SQL Server sur une machine virtuelle Azure, vous devez effectuer des étapes de configuration supplémentaires. Vérifiez que le groupe de sécurité réseau (NSG) de chaque machine virtuelle réplica autorise le trafic vers le port de point de terminaison et le port DNN, si vous utilisez l’écouteur DNN. Si vous utilisez l’écouteur VNN, vous devez vous assurer que l’équilibreur de charge est configuré correctement.

7. Vérifiez que READ_ONLY_ROUTING_URL (TCP://adresse_système:port) contient le nom de domaine complet (FQDN) et le numéro de port corrects. Consultez l'article :

   • Calcul de l’URL de routage en lecture seule pour Always On
   • sys.availability_replicas (Transact-SQL)
   • MODIFIER GROUPE DE DISPONIBILITÉ (Transact-SQL)

8. Vérifiez que la configuration de mise en réseau SQL Server est appropriée dans le Gestionnaire de configuration SQL Server.

   Vérifiez sur chaque réplica dans read_only_routing_list ce qui suit :

   • La connectivité à distance SQL Server est activée
   • TCP/IP est activé
   • Les adresses IP sont configurées correctement

   Note

   Vous pouvez vérifier rapidement que tous ces éléments sont correctement configurés si vous pouvez vous connecter à partir d’un ordinateur distant au nom d’instance SQL Server d’un réplica secondaire cible à l’aide de la syntaxe TCP:SQL_Instance.

Voir : Configurer un serveur pour écouter un port TCP spécifique (Gestionnaire de configuration SQL Server) et Afficher ou modifier les propriétés du serveur (SQL Server)

Tâches associées

• Création et configuration des groupes de disponibilité (SQL Server)

• Créer un point de terminaison de mise en miroir de bases de données pour l'authentification Windows (Transact-SQL)

• Spécifier l'URL de point de terminaison lors de l'ajout ou lors de la modification d'un réplica de disponibilité (SQL Server)

• Préparer manuellement une base de données secondaire pour un groupe de disponibilité (SQL Server)

• Résolution d'une défaillance lors d'une opération d'ajout de fichier (Groupes de disponibilité Always On)

• Gestion des connexions et des tâches pour les bases de données d'un groupe de disponibilité (SQL Server)

• Gérer les métadonnées lors de la mise à disposition d'une base de données sur une autre instance de serveur (SQL Server)

Contenu connexe

• Afficher les événements et journaux pour un cluster de basculement
• Applets de commande de cluster de basculement Get-ClusterLog
• Blog de l’équipe SQL Server Always On : Blog officiel de l’équipe SQL Server Always On
• Mise en miroir des bases de données et sécurité du transport pour les groupes de disponibilité Always On (SQL Server)
• Configuration du réseau client
• Prérequis, restrictions et recommandations pour les groupes de disponibilité Always On (SQL Server)