Risoluzione dei problemi di failover automatico negli ambienti SQL Server Always On

Questo articolo consente di risolvere i problemi che si verificano durante il failover automatico in Microsoft SQL Server.

Versione originale del prodotto: SQL Server
Numero KB originale: 2833707

Riepilogo

SQL Server Always On gruppi di disponibilità possono essere configurati per il failover automatico. Se viene rilevato un problema di integrità nell'istanza di SQL Server che ospita la replica primaria, è possibile eseguire la transizione del ruolo primario al partner di failover automatico (replica secondaria). Tuttavia, la replica secondaria non può sempre essere passata al ruolo primario. In alcuni casi, è possibile eseguire la transizione solo al RESOLVING ruolo. In questo caso, nessuna replica avrà il ruolo primario a meno che la replica primaria non restituisca uno stato integro. Inoltre, i database di disponibilità non saranno accessibili.

Questo articolo elenca alcune cause comuni di failover automatico non riuscito e illustra i passaggi che è possibile eseguire per diagnosticare la causa di questi errori.

Sintomi se viene attivato correttamente un failover automatico

Quando viene attivato un failover automatico nell'istanza di SQL Server che ospita la replica primaria, la replica secondaria passa al RESOLVING ruolo e quindi al ruolo primario. Sebbene il processo abbia esito positivo, le voci di errore vengono registrate nel report di log SQL Server simile al testo seguente:

The state of the local availability replica in availability group '\<Group name>' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'  
The state of the local availability replica in availability group '\<Group name>' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'

Sintomi se un failover automatico non riesce

Se un evento di failover automatico non riesce, la replica secondaria non passa correttamente al ruolo primario. Pertanto, la replica di disponibilità segnalerà che questa replica è in uno RESOLVING stato. Inoltre, i database di disponibilità segnalano che si trovano in uno NOT SYNCHRONIZING stato e le applicazioni non possono accedere a questi database.

Nell'immagine seguente, ad esempio, SQL Server Management Studio segnala che la replica secondaria è in uno RESOLVING stato perché il processo di failover automatico non è riuscito a eseguire la transizione della replica secondaria al ruolo primario.

Le sezioni seguenti illustrano diversi possibili motivi per cui il failover automatico potrebbe non riuscire e come diagnosticare ogni causa.

Caso 1: il valore "Numero massimo di errori nel periodo specificato" è esaurito

Il gruppo di disponibilità dispone di proprietà delle risorse del cluster Windows, ad esempio la proprietà Numero massimo di errori nella proprietà Periodo specificato . Questa proprietà viene utilizzata per evitare lo spostamento a tempo indeterminato di una risorsa cluster quando si verificano più errori di nodo.

Per analizzare e diagnosticare se questa è la causa del failover non riuscito, esaminare il log del cluster Windows (Cluster.log) e quindi controllare la proprietà .

Passaggio 1: Esaminare i dati nel log del cluster Windows (Cluster.log)

1. Usare Windows PowerShell per generare il log del cluster Windows nel nodo del cluster che ospita la replica primaria. A tale scopo, eseguire il cmdlet seguente in una finestra di PowerShell con privilegi elevati nell'istanza di SQL Server che ospita la replica primaria:

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

   [! NOTE]

   • Il -TimeSpan 15 parametro in questo passaggio presuppone che il problema diagnosticato si sia verificato nei 15 minuti precedenti.
   • Per impostazione predefinita, il file di log viene creato in %WINDIR%\cluster\reports.

2. Aprire il file Cluster.log nel Blocco note per esaminare il log del cluster Windows.

3. Nel Blocco note selezionare Modifica>ricerca e quindi cercare la stringa "failoverCount" alla fine del file. Nei risultati è necessario trovare un messaggio simile al seguente:

   Non viene eseguito il failover del gruppo <Nome> risorsa, failoverCount 3, failoverNumero di controlloimpostazione<>, computedFailoverThreshold 2

   

Passaggio 2: Controllare il numero massimo di errori nella proprietà Periodo specificato

1. Avviare Gestione cluster di failover.

2. Nel riquadro di spostamento selezionare Ruoli.

3. Nel riquadro Ruoli fare clic con il pulsante destro del mouse sulla risorsa cluster e quindi scegliere Proprietà.

4. Selezionare la scheda Failover e selezionare il valore Massimo errori nel valore Periodo specificato .

   

   Nota

   Il comportamento predefinito specifica che se la risorsa cluster ha esito negativo tre volte entro sei ore, deve rimanere nello stato non riuscito. Per un gruppo di disponibilità, ciò significa che la replica viene lasciata nello RESOLVING stato .

Conclusione

Dopo aver analizzato il log, si scopre che il valore failoverCount di 3 è maggiore del valore calcolatoFailoverThresholdpari a 2. Pertanto, il cluster Windows non può completare l'operazione di failover della risorsa del gruppo di disponibilità al partner di failover.

Risoluzione

Per risolvere questo problema, aumentare il valore Numero massimo di errori nel valore Periodo specificato .

Caso 2: Autorizzazioni dell'account NT Authority\SYSTEM insufficienti

La DLL della risorsa del motore di database SQL Server si connette all'istanza di SQL Server che ospita la replica primaria tramite ODBC per monitorare l'integrità. Le credenziali di accesso usate per questa connessione sono l'account di accesso SQL Server NT AUTHORITY\SYSTEM locale. Per impostazione predefinita, a questo account di accesso locale vengono concesse le autorizzazioni seguenti:

• Modificare qualsiasi gruppo di disponibilità
• Connessione a SQL Server
• Visualizzare lo stato del server

Se l'account NT AUTHORITY\SYSTEM di accesso non dispone di alcuna di queste autorizzazioni nel partner di failover automatico (la replica secondaria), SQL Server non può avviare il rilevamento dell'integrità quando si verifica un failover automatico. Pertanto, la replica secondaria non può eseguire la transizione al ruolo primario. Per analizzare e diagnosticare se questa è la causa, esaminare il log del cluster Windows. A tal fine, attenersi alla seguente procedura:

1. Usare Windows PowerShell per generare il log del cluster Windows nel nodo del cluster. A tale scopo, eseguire il cmdlet seguente in una finestra di PowerShell con privilegi elevati nell'istanza di SQL Server che ospita la replica secondaria che non ha eseguito la transizione al ruolo primario:

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

2. Aprire il file Cluster.log nel Blocco note per esaminare il log del cluster Windows.

3. Trovare una voce di errore simile al testo seguente:

   Impossibile eseguire il comando di diagnostica. L'utente non dispone dell'autorizzazione per eseguire questa azione.

   

Conclusione

Il file Cluster.log segnala che esiste un problema di autorizzazioni quando SQL Server esegue il comando di diagnostica. In questo esempio, l'errore è stato causato dalla rimozione dell'autorizzazione Visualizza stato server dall'account NT AUTHORITY\SYSTEM di accesso nell'istanza di SQL Server che ospita la replica secondaria di una coppia di failover automatico.

Risoluzione

Per risolvere questo problema, concedere autorizzazioni sufficienti all'account NT AUTHORITY\SYSTEM di accesso per il rilevamento dell'integrità della DLL della risorsa del motore di database SQL Server.

Caso 3: I database di disponibilità non sono in uno stato SYNCHRONIZED

Per eseguire automaticamente il failover, tutti i database di disponibilità definiti nel gruppo di disponibilità devono trovarsi in uno SYNCHRONIZED stato compreso tra la replica primaria e la replica secondaria. Quando si verifica un failover automatico, questa condizione di sincronizzazione deve essere soddisfatta per assicurarsi che non si verifichi alcuna perdita di dati. Pertanto, se un database di disponibilità nel gruppo di disponibilità è in stato di sincronizzazione o NOT SYNCHRONIZED stato, il failover automatico non esegue correttamente la transizione della replica secondaria al ruolo primario.

Per altre informazioni sulle condizioni necessarie per un failover automatico, vedere Le condizioni necessarie per un failover automatico e le repliche con commit sincrono supportano due sezioni delle impostazioni delle modalità di failover e failover (Always On gruppi di disponibilità).

Per analizzare e diagnosticare se questa è la causa del failover non riuscito, esaminare il log degli errori SQL Server. È necessario trovare una voce di errore simile al testo seguente:

Uno o più database non sono sincronizzati o non sono stati aggiunti al gruppo di disponibilità.

Per verificare se i database di disponibilità erano nello SYNCHRONIZED stato, seguire questa procedura:

1. Connettersi alla replica secondaria.

2. Eseguire lo script SQL seguente per controllare il is_failover_ready valore per tutti i database di disponibilità nel gruppo di disponibilità di cui non è stato eseguito il failover.

   Nota

   Il valore zero per uno qualsiasi dei database di disponibilità può impedire il failover automatico. Questo valore indica che il database di disponibilità non SYNCHRONIZEDera .

   SELECT database_name, is_failover_ready FROM sys.dm_hadr_database_replica_cluster_states WHERE replica_id IN (SELECT replica_id FROM sys.dm_hadr_availability_replica_states)
   

   

Conclusione

Per il corretto failover automatico del gruppo di disponibilità è necessario che tutti i database di disponibilità siano nello SYNCHRONIZED stato . Per altre informazioni sulle modalità di disponibilità, vedere Modalità di disponibilità in Always On gruppi di disponibilità.

Caso 4: la configurazione "Force Protocol Encryption" è selezionata per i protocolli client nella replica secondaria (primaria di destinazione) anche se la replica non è configurata per la crittografia

Durante il failover, quando il server primario rileva un problema di integrità, la DLL del cluster nel partner di failover (replica secondaria) tenta di connettersi alla replica locale per avviare il monitoraggio dell'integrità. Questa operazione fa parte della transizione al ruolo primario. Se la replica secondaria non è configurata per la crittografia, ma l'impostazione Force Protocol Encryption è inavvertitamente impostata nella configurazione client, la connessione avrà esito negativo e il failover non può verificarsi.

Per verificare la presenza di questa configurazione:

1. Avviare Gestione configurazione SQL Server.
2. Nel riquadro sinistro fare clic con il pulsante destro del mouse sulla configurazione di SQL Native Client 11.0 e quindi scegliere Proprietà.
3. Nella finestra di dialogo selezionare l'impostazione Forza crittografia protocollo . Se è impostato su Sì, modificare il valore in No.
4. Ripetere il failover.

Conclusione

SQL Server Always On monitoraggio dell'integrità usa una connessione ODBC locale per monitorare l'integrità SQL Server. È consigliabile abilitare Force Protocol Encryption nella sezione Configurazione client di Gestione configurazione SQL Server solo se SQL Server è stato configurato per forzare la crittografia in Gestione configurazione SQL Server nel SQL Server Sezione Configurazione di rete. Per altre informazioni, vedere Abilitare le connessioni crittografate al motore di database.

Caso 5: Problemi di prestazioni nella replica secondaria o nel nodo causa l'esito negativo dei controlli di integrità Always On

Prima di eseguire il failover dalla replica primaria alla replica secondaria, SQL Server DLL della risorsa del motore di database si connette alla replica secondaria per verificare l'integrità della replica. Se questa connessione non riesce a causa di problemi di prestazioni nella replica secondaria, il failover automatico non si verifica.

Per analizzare e diagnosticare se questa è la causa, seguire questa procedura:

1. Esaminare il log del cluster nella replica secondaria per verificare la presenza del messaggio di errore "Impossibile completare il processo di accesso a causa di un ritardo nell'apertura della connessione al server".

   0000110c.00002bcc::2020/08/06-01:17:54.943 INFO  [RCM] move of group AOCProd01AG from CO2ICMV3SQL09(1) to CO2ICMV3SQL10(2) of type MoveType::Manual is about to succeed, failoverCount=3, lastFailoverTime=2020/08/05-02:08:54.524 targeted=true 
   00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection (0) 
   00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Could not connect to SQL Server (rc -1) 
   00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] SQLDisconnect returns following information 
   00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0) 
   00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Failed to connect to SQL Server 
   00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RHS] Online for resource AOCProd01AG failed. 
   

   Questa situazione può verificarsi se il failover viene eseguito in una replica secondaria SQL Server con un carico di lavoro esistente occupato. Ciò potrebbe ritardare la risposta di SQL Server al tentativo di connessione all'integrità HADR e impedire un tentativo di failover riuscito.

2. Per determinare se si verificano pressioni sulle utilità di pianificazione di sistema, usare SQL Server Management Studio per eseguire lo script seguente nella replica secondaria:

   USE MASTER 
   GO  
   WHILE 1=1 
   BEGIN 
   PRINT convert(varchar(20), getdate(),120) 
   DECLARE @max INT; 
   SELECT @max = max_workers_count 
   FROM sys.dm_os_sys_info; 
   SELECT GETDATE() AS 'CurrentDate',  
          @max AS 'TotalThreads',  
          SUM(active_Workers_count) AS 'CurrentThreads',  
          @max - SUM(active_Workers_count) AS 'AvailableThreads',  
          SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',  
          SUM(work_queue_count) AS 'RequestWaitingForThreads' 
          --SUM(current_workers_count) AS 'AssociatedWorkers' 
   FROM sys.dm_os_Schedulers 
   WHERE STATUS = 'VISIBLE ONLINE'; 
   wait for delay '0:0:15' 
   END
   

   Di seguito è riportato l'output di esempio della query precedente:

   Currentdate	TotalThreads	CurrentThreads	AvailableThreads	WorkersWaitingForCpu	RequestWaitingForThreads
   2020-10-06 01:27:01.337	1216	361	855	33	0
   2020-10-06 01:27:08.340	1216	1412	-196	22	76
   2020-10-06 01:27:15.340	1216	1304	-88	2	161
   2020-10-06 01:27:22.340	1216	1242	-26	21	185
   2020-10-06 01:27:29.343	1216	1346	-130	19	476
   2020-10-06 01:27:36.350	1216	1350	-134	9	630
   2020-10-06 01:27:43.353	1216	1346	-130	13	539
   2020-10-06 01:27:50.360	1216	1378	-162	5	328
   2020-10-06 01:27:57.360	1216	197	1019	0	0

   I valori elevati riportati per WorkersWaitingForCpu e RequestWaitingForThreads indicano che la contesa di pianificazione si sta verificando e che SQL Server non è in grado di eseguire il carico di lavoro corrente in modo tempestivo.

Risoluzione

Se si verifica questo problema, ribilanciare il carico di lavoro nella replica secondaria o valutare la possibilità di aumentare la potenza di elaborazione (aggiungere processori) nei computer che eseguono questi carichi di lavoro.

Risolvere altri eventi di failover non riusciti

Per monitorare l'integrità della nuova replica primaria durante il failover, è necessario connettere localmente il monitoraggio dell'integrità AlwaysOn all'istanza di SQL Server che esegue la transizione al ruolo primario.

Oltre ai motivi più comuni illustrati in questo articolo, esistono molti altri motivi per cui questo tentativo di connessione potrebbe non riuscire. Per analizzare ulteriormente un tentativo di failover non riuscito, esaminare il log del cluster nel partner di failover (la replica a cui non è stato possibile eseguire il failover):

1. Usare Windows PowerShell per generare il log del cluster Windows nel nodo del cluster. A tale scopo, eseguire il cmdlet seguente in una finestra di PowerShell con privilegi elevati nell'istanza di SQL Server che ospita la replica secondaria che non ha eseguito la transizione al ruolo primario. Verrà generato un log cluster per gli ultimi 60 minuti di attività.

   Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
   

2. Per esaminare il log del cluster Windows, aprire il file Cluster.log nel Blocco note.

3. Cercare la stringa "Connetti a SQL Server" che si verifica durante l'evento di failover non riuscito.

4. Esaminare i messaggi di accesso successivi usando l'ID thread (vedere lo screenshot seguente) per correlare gli eventi correlati all'evento di accesso. Nell'esempio seguente viene illustrata la ricerca di "Connetti a SQL Server". Viene inoltre illustrato l'uso dell'ID thread (lato sinistro) per individuare l'altra diagnostica che descrive il motivo per cui il tentativo di connessione non è riuscito.

   

Negli esempi seguenti vengono illustrati gli errori di connessione alla nuova replica primaria.

Set di esempio 1

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: No client protocols are enabled and no protocol was specified in the connection
string [xFFFFFFFF]. (268435455)

Risoluzione

Avviare Gestione configurazione SQL Server e quindi verificare che la memoria condivisa o TCP/IP sia abilitata in Protocolli client per la configurazione di SQL Native Client.

Set di esempio 2

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: Server doesn't support requested protocol [xFFFFFFFF]. (268435455)

Risoluzione

Avviare Gestione configurazione SQL Server e quindi verificare che la memoria condivisa o TCP/IP sia abilitata in Protocolli client per la configurazione di SQL Native Client.

Set di esempio 3

000010b8.00001764::2020/12/02-16:52:49.808 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot alter the availability
group 'ag', because it does not exist or you do not have permission. (15151)
000010b8.00000fd0::2020/12/02-17:01:14.821 ERR [RES] SQL Server Availability Group: [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The user does not have permission to perform this action. (297)
000010b8.00001838::2020/12/02-17:10:04.427 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user
'SQLREPRO\NODE2$'. Reason: The account is disabled. (18470)

Risoluzione

Esaminare il caso 2: Autorizzazioni dell'account NT Authority\SYSTEM insufficienti.