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

S'applique à : SQL Server

Cette rubrique fournit des informations pour vous aider à résoudre les problèmes courants de configuration des instances de serveur pour 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é Groupes de disponibilité n’est pas activée et vous explique également comment l’activer. Il existe deux scénarios dans lesquels vous pouvez accéder à cet état en plus de celui évident où le groupe de disponibilité n’a pas été activé au premier endroit.

1. Si SQL Server a été installé et que la fonctionnalité Groupes de disponibilité Always On est activée avant d’installer la fonctionnalité de clustering de basculement Windows, cette erreur peut s’afficher 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 encore Always On configurée, lorsque vous tentez d’utiliser à nouveau le groupe de disponibilité, 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é Groupes de disponibilité
2. Redémarrez le service SQL Server
3. Réactiver la fonctionnalité Groupes de disponibilité
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 le fichier maître sur l'instance de serveur distant et cette connexion doit disposer des autorisations CONNECT pour se connecter au point de terminaison de mise en miroir de bases 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. Vérifiez que chaque instance de SQL Server devant héberger un réplica de disponibilité (chaque emplacement de réplica) dispose d’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 Groupes de disponibilité Always On qu'il est difficile d'expliquer, nous vous conseillons d'examiner chaque instance de serveur pour déterminer si elle est à l'écoute sur les ports appropriés.

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

   Notes

   Dans certains cas, si le point de terminaison est démarré mais que les réplicas du groupe de disponibilité ne communiquent pas, vous pouvez essayer 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 de point de terminaison pendant l’ajout ou 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 autorisée à accéder au port de chacune de l'autre instance de serveur par le biais du protocole 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 est à l'écoute et que la connexion est réussie, vous verrez « TcpTestSucceeded : True ». Si ce n’est pas le cas, vous recevrez 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 à ServerName, mais pas à l’adresse IP, il peut y avoir plusieurs points de terminaison définis sur ce serveur (par exemple, une autre instance SQL) qui écoute sur ce port. Bien que l’état du point de terminaison sur l’instance en question indique « STARTED », une autre instance peut en fait avoir le port lié et empêcher l’instance appropriée d’écouter et d’établir des connexions TCP.

• Si est-NetConnection ne parvient pas à se connecter, recherchez le pare-feu et/ou le logiciel 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 de l’écouteur avec 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 problème de jointure de bases de données secondaires au groupe de disponibilité du fait que la connexion au réplica principal n'est pas active. 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 est créé et démarré.
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 sys.dm_hadr_availability_replica_states pour connaître le paramètre last_connect_error_number qui peut vous aider à diagnostiquer le problème de jointure.
4. Vérifiez que le point de terminaison est défini afin qu’il corresponde correctement à l’adresse IP/port que le groupe de disponibilité utilise.
5. Vérifiez si le compte de service réseau dispose de l’autorisation CONNECT sur le point de terminaison.
6. Recherchez 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 des fournisseurs de client qui prennent en charge le paramètre ApplicationIntent. Consultez Prise en charge des pilotes et de la connectivité du client pour les groupes de disponibilité

   Notes

   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 défini sur ReadOnly
   • MultiSubnetFailover défini sur True pour le nom de réseau distribué (DNN)

   Exemples

   Cet exemple illustre la chaîne de connexion du fournisseur .NET System.Data.SqlClient pour un écouteur de nom de réseau virtuel (VNN) :

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

   Cela illustre la chaîne de connexion pour le fournisseur .NET System.Data.SqlClient pour un écouteur DNN (Distributed Network Name) :

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

   Notes

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

4. Assurez-vous que l'écouteur du groupe de disponibilité est en ligne. Pour vous assurer que l’écouteur 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 celle-ci :

   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. Assurez-vous que le Pare-feu Windows ne bloque pas le port READ_ONLY_ROUTING_URL. Configurez un pare-feu Windows pour l’accès du moteur de base de données sur chaque réplica dans read_only_routing_list et tous les clients qui se connecteront à ces réplicas.

   Notes

   Si vous exécutez SQL Server sur une machine virtuelle Azure, vous devez effectuer des étapes de configuration supplémentaires. Assurez-vous que le groupe de sécurité réseau (NSG) de chaque machine virtuelle de réplica autorise le trafic vers le port de point de terminaison et le port DNN, si vous utilisez un é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)
   • ALTER AVAILABILITY GROUP (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

   Notes

   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ésoudre une opération d’ajout de fichier ayant échoué (groupes de disponibilité Always On)

• Gestion des connexions et des travaux 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 associé

• 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

Voir aussi

Sécurité du transport de la mise en miroir de bases de données et des 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)