Risolvere i problemi relativi alla configurazione di Gruppi di disponibilità Always On (SQL Server)

Articolo
04/26/2023

Si applica a:SQL Server

Questo argomento fornisce informazioni per la risoluzione dei problemi tipici relativi alla configurazione delle istanze del server per i gruppi di disponibilità Always On. Tra i problemi di configurazione tipici sono inclusi la disabilitazione dei gruppi di disponibilità Always On, la configurazione errata degli account, l'endpoint del mirroring del database inesistente, l'endpoint inaccessibile (errore di SQL Server 1418), l'accesso alla rete inesistente e l'esito negativo di un comando di aggiunta di database (errore di SQL Server 35250).

Nota

Verificare che i prerequisiti dei gruppi di disponibilità Always On siano soddisfatti. Per altre informazioni, vedere Prerequisiti, restrizioni e consigli per i gruppi di disponibilità Always On (SQL Server).

Contenuto dell'argomento:

Sezione	Descrizione
Funzionalità Gruppi di disponibilità Always On non abilitata	Se un'istanza di SQL Server non è abilitata per i gruppi di disponibilità Always On, l'istanza non supporta la creazione di gruppi di disponibilità e non può ospitare repliche di disponibilità.
Account	Illustra i requisiti per la corretta configurazione degli account con cui viene eseguito SQL Server.
Endpoint	Illustra come diagnosticare problemi relativi all'endpoint del mirroring del database di un'istanza del server.
Accesso alla rete	Documenta il requisito in base a cui ogni istanza del server che ospita una replica di disponibilità deve essere in grado di accedere alla porta di ciascuna altra istanza del server su TCP.
Listener	Documenta come stabilire l'indirizzo IP e la porta del listener e assicurarsi che sia in esecuzione e in ascolto per le connessioni in ingresso
Accesso all'endpoint (errore di SQL Server 1418)	Contiene informazioni su questo messaggio di errore di SQL Server.
Errore nella creazione del join del database (errore di SQL Server 35250)	Illustra le possibili cause e la risoluzione di un errore nell'aggiunta dei database secondari a un gruppo di disponibilità perché la connessione alla replica primaria non è attiva.
Il routing di sola lettura non funziona correttamente
Attività correlate	Contiene un elenco di argomenti orientati alle attività nella documentazione online di SQL Server rilevanti per risolvere i problemi relativi a una configurazione del gruppo di disponibilità.
Contenuto correlato	Contiene un elenco di risorse rilevanti esterne alla documentazione online di SQL Server.

Funzionalità Gruppi di disponibilità Always On non abilitata

La funzionalità Gruppi di disponibilità Always On deve essere abilitata in ogni istanza di SQL Server.

Se la funzionalità Gruppi di disponibilità Always On non è abilitata, verrà visualizzato questo messaggio di errore quando si tenta di creare un gruppo di disponibilità in 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)

Il messaggio di errore indica chiaramente che la funzionalità del gruppo di disponibilità non è abilitata e include anche istruzioni per abilitarla. Esistono due scenari in cui è possibile ritrovarsi in questo stato oltre a quello ovvio della mancata abilitazione del gruppo di disponibilità.

Se SQL Server è stato installato e la funzionalità Gruppi di disponibilità Always On è stata abilitata prima di installare la funzionalità Clustering di failover di Windows, è possibile che questo errore venga visualizzato quando si tenta di creare un gruppo di disponibilità di Always On.
Se si rimuove una funzionalità Clustering di failover di Windows esistente e la si ripristina mentre Always On è ancora configurato in SQL Server, quando si tenta di usare nuovamente il gruppo di disponibilità può verificarsi questo errore.

In questi casi è possibile seguire questa procedura per risolvere il problema:

Disabilitare la funzionalità del gruppo di disponibilità
Riavviare il servizio SQL Server
Abilitare nuovamente la funzionalità del gruppo di disponibilità
Riavviare di nuovo il servizio SQL Server

Per altre informazioni, vedere Abilitare e disabilitare la funzionalità Gruppi di disponibilità Always On (SQL Server).

Account

È necessario configurare correttamente gli account usati per l'esecuzione di SQL Server.

Autorizzazioni corrette per gli account
1. Se i partner vengono eseguiti con lo stesso account utente di dominio, gli account di accesso utente corretti saranno disponibili automaticamente in entrambi i database master. Ciò semplifica la configurazione della sicurezza del database ed è la scelta consigliata.
2. Se due istanze del server vengono eseguite con account diversi, è necessario creare ogni account nel database master nell'istanza del server remoto e a tale entità server è necessario concedere le autorizzazioni CONNECT per la connessione all'endpoint del mirroring del database di tale istanza del server. Per altre informazioni, vedere Configurare gli account di accesso per il mirroring del database o i gruppi di disponibilità Always On (SQL Server). È possibile usare la query seguente in ogni istanza per verificare se gli account di accesso dispongono delle autorizzazioni 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    
```
Se SQL Server è in esecuzione con un account predefinito, ad esempio Sistema locale, Servizio locale o Servizio di rete, oppure con un account non di dominio, è necessario usare certificati per l'autenticazione dell'endpoint. Se gli account del servizio utilizzano account di dominio nello stesso dominio, è possibile scegliere di concedere l'accesso CONNECT per ogni account del servizio su tutti i percorsi di replica oppure utilizzare certificati. Per altre informazioni, vedere Usare certificati per un endpoint del mirroring del database (Transact-SQL).

Endpoint

È necessario configurare correttamente gli endpoint.

Verificare che ogni istanza di SQL Server che ospiterà una replica di disponibilità (ogni percorso di replica) disponga di un endpoint del mirroring del database. Per determinare se in una determinata istanza del server è presente un endpoint del mirroring del database, usare la vista del catalogo sys.database_mirroring_endpoints:
```
SELECT name, state_desc FROM sys.database_mirroring_endpoints  
```
Per altre informazioni sulla creazione degli endpoint, vedere Creare un endpoint del mirroring del database per l'autenticazione Windows (Transact-SQL) oppure Impostare l'endpoint del mirroring del database per l'uso di certificati per le connessioni in uscita (Transact-SQL).
Verificare che i numeri di porta siano corretti.

Per individuare la porta attualmente associata all'endpoint di mirroring del database per un'istanza del server, usare l'istruzione Transact-SQL seguente:
```
SELECT type_desc, port FROM sys.tcp_endpoints;  
GO  
```
Per i problemi di configurazione dei gruppi di disponibilità Always On che sono difficili da diagnosticare, è consigliabile controllare ogni istanza del server per verificare che sia in attesa sulle porte corrette.
Verificare che gli endpoint siano stati avviati (STATE=STARTED). Usare l'istruzione Transact-SQL seguente in ogni istanza del server:
```
SELECT state_desc FROM sys.database_mirroring_endpoints  
```
Per altre informazioni sulla colonna state_desc, vedere sys.database_mirroring_endpoints (Transact-SQL).

Per avviare un endpoint, usare l'istruzione Transact-SQL seguente:
```
ALTER ENDPOINT Endpoint_Mirroring   
STATE = STARTED   
AS TCP (LISTENER_PORT = <port_number>)  
FOR database_mirroring (ROLE = ALL);  
GO  
```
Per altre informazioni, vedere ALTER ENDPOINT (Transact-SQL).

Nota

In alcuni casi, se l'endpoint viene avviato, ma le repliche del gruppo di disponibilità non comunicano, è possibile provare ad arrestare e riavviare l'endpoint. È possibile usare ALTER ENDPOINT [Endpoint_Mirroring] STATE = STOPPED seguito da ALTER ENDPOINT [Endpoint_Mirroring] STATE = STARTED

Verificare che l'account di accesso dell'altro server disponga dell'autorizzazione CONNECT. Per individuare gli account che dispongono dell'autorizzazione CONNECT per un endpoint, usare l'istruzione Transact-SQL seguente in ogni istanza del server:

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;

Verificare che il nome del server corretto venga usato nell'URL dell'endpoint

Per il nome del server in un URL dell'endpoint è consigliabile usare il nome di dominio completo (FQDN), anche se è possibile usare qualsiasi nome che identifica in modo univoco il computer. L'indirizzo del server può essere un nome Netbios (se i sistemi si trovano nello stesso dominio), un nome di dominio completo (FQDN) o un indirizzo IP (preferibilmente un indirizzo IP statico). L'uso del nome di dominio completo è l'opzione consigliata.

Se è già stato definito un URL dell'endpoint, è possibile eseguire una query su di esso usando:
```
select endpoint_url from sys.availability_replicas
```
Confrontare quindi l'output di endpoint_url con il nome del server (nome NetBIOS o FQDN). Per ottenere il nome del server tramite query, eseguire i comandi seguenti in PowerShell nella replica in locale:
```
$env:COMPUTERNAME
[System.Net.Dns]::GetHostEntry([string]$env:computername).HostName
```
Per convalidare il nome del server in un computer remoto, eseguire questo comando da PowerShell.
```
$servername_from_endpoint_url = "server_from_endpoint_url_output"

Test-NetConnection -ComputerName $servername_from_endpoint_url
```
Per altre informazioni, vedere Specificare l'URL dell'endpoint quando si aggiunge o si modifica una replica di disponibilità (SQL Server).

Nota

Per usare l'autenticazione Kerberos per la comunicazione tra endpoint del gruppo di disponibilità (AG), registrare un nome dell'entità servizio per le connessioni Kerberos per gli endpoint del mirroring del database usati dal gruppo di disponibilità.

Accesso alla rete

Ogni istanza del server che ospita una replica di disponibilità deve essere in grado di accedere alla porta di ciascuna altra istanza del server su TCP. Questo requisito è particolarmente importante quando le istanze del server appartengono a domini diversi non trusted. Verificare se è possibile connettersi agli endpoint seguendo questa procedura:

Usare Test-NetConnection (equivalente a Telnet) per convalidare la connettività. Ecco alcuni esempi dei comandi che è possibile usare:

$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

Se l'endpoint è in ascolto e la connessione ha esito positivo, verrà visualizzato "TcpTestSucceeded : True". In caso contrario, verrà visualizzato un messaggio "TcpTestSucceeded : False".
Se la connessione Test-NetConnection (Telnet) all'indirizzo IP funziona ma non funziona per ServerName è probabile che esista un problema di DNS o di risoluzione dei nomi
Se la connessione funziona da ServerName e non dall'indirizzo IP, potrebbe essere presente più di un endpoint definito in tale server (forse un'altra istanza di SQL Server) in ascolto su tale porta. Anche se lo stato dell'endpoint nell'istanza in questione viene indicato come avviato, la porta potrebbe essere associata a un'altra istanza e ciò potrebbe impedire all'istanza corretta di porsi in ascolto e stabilire le connessioni TCP.
Se Test-NetConnection non riesce a connettersi, verificare se la porta dell'endpoint in questione è bloccata da firewall e/o software antivirus. Controllare l'impostazione del firewall per verificare se è consentita la comunicazione della porta dell'endpoint tra le istanze del server che ospitano la replica primaria e la replica secondaria (porta 5022 per impostazione predefinita). Eseguire lo script di PowerShell seguente per esaminare le regole di traffico in ingresso disabilitate
Se si esegue SQL Server nella macchina virtuale di Azure, è necessario assicurarsi anche che il gruppo di sicurezza di rete (NSG) consenta il traffico alla porta dell'endpoint. Controllare l'impostazione del firewall (e del gruppo di sicurezza di rete per la macchina virtuale di Azure) per verificare se è consentita la comunicazione della porta dell'endpoint tra le istanze del server che ospitano la replica primaria e la replica secondaria (porta 5022 per impostazione predefinita)
```
Get-NetFirewallRule -Action Block -Enabled True -Direction Inbound |Format-Table
```
Acquisire l'output dal cmdlet Get-NetTCPConnection (equivalente a NETSTAT -a) e verificare che lo stato sia in ascolto o stabilito per IP:Port per l'endpoint specificato
```
Get-NetTCPConnection 
```

Listener

Per la configurazione corretta di un listener del gruppo di disponibilità, vedere Configurare un listener per un gruppo di disponibilità Always On

Dopo aver configurato il listener, è possibile convalidare l'indirizzo IP e la porta su cui è in ascolto usando la query seguente:

$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"

È anche possibile trovare le informazioni sul listener insieme alle porte di SQL Server usando questa query:

$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")

Se è necessario stabilire la connettività al listener e si sospetta che una porta sia bloccata, è possibile eseguire un test usando il cmdlet Test-NetConnection di PowerShell (equivalente a 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

Controllare infine se il listener è in ascolto sulla porta specificata:

$port_number = "your_port_number"

Get-NetTCPConnection -LocalPort $port_number -State Listen

Accesso all'endpoint (errore di SQL Server 1418)

Questo messaggio di SQL Server indica che l'indirizzo di rete del server specificato nell'URL dell'endpoint non è raggiungibile o non esiste, pertanto si consiglia di controllare il nome dell'indirizzo di rete e quindi eseguire nuovamente il comando.

Errore nella creazione del join del database (errore di SQL Server 35250)

In questa sezione vengono illustrate le possibili cause e la risoluzione di un errore per l'aggiunta di database secondari al gruppo di disponibilità perché la connessione alla replica primaria non è attiva. Questo è il messaggio di errore completo:

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

Risoluzione:

Di seguito è disponibile un riepilogo dei passaggi.

Per istruzioni dettagliate, vedere l'errore del motore MSSQLSERVER_35250

Verificare che l'endpoint sia stato creato e avviato.
Controllare se è possibile connettersi all'endpoint tramite Telnet e assicurarsi che nessuna regola del firewall blocchi la connettività
Controllare gli errori nel sistema. È possibile eseguire una query su sys.dm_hadr_availability_replica_states per individuare last_connect_error_number e diagnosticare più facilmente il problema di aggiunta.
Verificare che l'endpoint sia definito in modo che corrisponda correttamente all'IP/porta usati dal gruppo di disponibilità.
Controllare se l'account del servizio di rete dispone dell'autorizzazione CONNECT per l'endpoint.
Verificare se sono presenti problemi di risoluzione dei nomi
Assicurarsi che sia in esecuzione una build di SQL Server recente (preferibilmente l'ultima build) per evitare di dover affrontare problemi già corretti.

Il routing di sola lettura non funziona correttamente

Assicurarsi di aver configurato il routing di sola lettura seguendo le indicazioni nel documento Configurare il routing di sola lettura.
Verificare il supporto dei driver client

L'applicazione client deve usare un provider client che supporta il parametro ApplicationIntent. Vedere Supporto della connettività di driver e client per i gruppi di disponibilità

Nota

Se ci si connette a un listener DNN (Distributed Network Name), il provider deve supportare anche il parametro MultiSubnetFailover
Verificare che le proprietà della stringa di connessione siano impostate correttamente

Per il corretto funzionamento del routing di sola lettura, l'applicazione client deve usare queste proprietà nella stringa di connessione:
- Nome del database appartenente al gruppo di disponibilità
- Nome del listener del gruppo di disponibilità
  - Se si usa DNN, è necessario specificare il nome del listener DNN e il numero di porta DNN <DNN name,DNN port>
- Proprietà ApplicationIntent impostata su ReadOnly
- La proprietà MultiSubnetFailover deve essere impostata su true per il nome di rete distribuita (DNN)
Esempi

Questo esempio illustra la stringa di connessione per il provider .NET System.Data.SqlClient per un listener VNN (Virtual Network Name):
```
Server=tcp:VNN_AgListener,1433;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
```
Questo esempio illustra la stringa di connessione per il provider .NET System.Data.SqlClient per un listener DNN (Distributed Network Name):
```
Server=tcp:DNN_AgListener,DNN_Port;Database=AgDb1;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
```
Nota

Se si usano programmi da riga di comando come SQLCMD, assicurarsi di specificare le opzioni corrette per il nome del server. Ad esempio, in SQLCMD è necessario usare l'opzione maiuscola -S che specifica il nome del server e non l'opzione minuscola -s usata per il separatore di colonna.
Esempio: sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M
Assicurarsi che il listener del gruppo di disponibilità sia online. Per assicurarsi che il listener del gruppo di disponibilità sia online, eseguire la query seguente nella replica primaria:
```
SELECT * FROM sys.dm_tcp_listener_states;
```
Se il listener è offline, è possibile provare a portarlo online usando un comando simile al seguente:
```
ALTER AVAILABILITY GROUP myAG RESTART LISTENER 'AG_Listener';
```
Verificare che READ_ONLY_ROUTING_LIST sia popolato correttamente. Nella replica primaria verificare che READ_ONLY_ROUTING_LIST contenga solo le istanze del server che ospitano repliche secondarie leggibili.

Per visualizzare le proprietà di ogni replica, è possibile eseguire questa query ed esaminare l'endpoint di connettività (URL) della replica di sola lettura.
```
SELECT replica_id, replica_server_name, secondary_role_allow_connections_desc, read_only_routing_url 
FROM sys.availability_replicas;   
```
Per visualizzare un elenco di routing di sola lettura e confrontarlo con l'URL dell'endpoint:
```
SELECT * FROM sys.availability_read_only_routing_lists;
```
Per modificare un elenco di routing di sola lettura, è possibile usare una query simile alla seguente:
```
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
```
Per altre informazioni, vedere Configurare il routing di sola lettura per un gruppo di disponibilità - SQL Server Always On
Verificare che la porta READ_ONLY_ROUTING_URL sia aperta. Verificare che Windows Firewall non blocchi la porta READ_ONLY_ROUTING_URL. Configurare Windows Firewall per l'accesso al motore di database in ogni replica nell'elenco read_only_routing_list e per i client che si connetteranno a tali repliche.

Nota

Se si esegue SQL Server nella macchina virtuale di Azure, è necessario eseguire passaggi di configurazione aggiuntivi. Assicurarsi che il gruppo di sicurezza di rete (NSG) di ogni macchina virtuale di replica consenta il traffico verso la porta dell'endpoint e la porta DNN, se si usa il listener DNN. Se si usa il listener VNN, è necessario assicurarsi che il servizio di bilanciamento del carico sia configurato correttamente.
Verificare che READ_ONLY_ROUTING_URL (TCP://system-address:port) contenga il nome di dominio completo (FQDN) e il numero di porta corretti. Vedere:
Assicurarsi che la configurazione di rete di SQL Server sia corretta in Gestione configurazione SQL Server.

In ogni replica in read_only_routing_list verificare che:
- La connettività remota di SQL Server sia abilitata
- TCP/IP sia abilitato
- Gli indirizzi IP siano configurati correttamente
Nota

È possibile verificare rapidamente che tutti questi elementi siano configurati correttamente se è possibile connettersi da un computer remoto all'istanza di SQL Server di una replica secondaria di destinazione usando la sintassi TCP:SQL_Instance.

Vedere: Configurare un server per l'ascolto su una porta TCP specifica (Gestione configurazione SQL Server) e Visualizzare o modificare le proprietà del server (SQL Server)

Attività correlate

Contenuto correlato

Vedi anche

Sicurezza trasporto per il mirroring del database e i gruppi di disponibilità Always On (SQL Server)
Configurazione di rete dei client
Prerequisiti, restrizioni e consigli per i gruppi di disponibilità AlwaysOn (SQL Server)