Risolvere i problemi relativi ai database di disponibilità Always On in stato di ripristino in sospeso o sospetto in SQL Server

Articolo
06/27/2023

Questo articolo descrive gli errori e le limitazioni di un database di disponibilità in Microsoft SQL Server in uno Recovery Pending stato o Suspect e come ripristinare la funzionalità completa del database in un gruppo di disponibilità.

Versione originale del prodotto: SQL Server 2012
Numero KB originale: 2857849

Riepilogo

Si supponga che un database di disponibilità definito in un gruppo di disponibilità Always On passa a uno Recovery Pending stato o Suspect in SQL Server. Se ciò si verifica nella replica primaria del gruppo di disponibilità, la disponibilità del database è interessata. In questo caso, non è possibile accedere al database tramite le applicazioni client. Inoltre, non è possibile eliminare o rimuovere il database dal gruppo di disponibilità.

Si supponga, ad esempio, che SQL Server sia in esecuzione e che un database di disponibilità sia impostato sullo Recovery Pending stato o Suspect . Quando si eseguono query sulle viste a gestione dinamica (DMV) nella replica primaria usando lo script SQL seguente, il database potrebbe essere segnalato in uno NOT_HEALTHY stato e RECOVERY_PENDING o in uno SUSPECT stato come indicato di seguito:

SELECT
    dc.database_name,
    d.synchronization_health_desc,
    d.synchronization_state_desc,
    d.database_state_desc
FROM
    sys.dm_hadr_database_replica_states d
    JOIN sys.availability_databases_cluster dc ON d.group_database_id = dc.group_database_id
    AND d.is_local = 1

database_name          synchronization_health_desc     synchronization_state_desc   database_state_desc
-------------------- ------------------------------ ------------------------------ ---------------------
<DatabaseName>                         NOT_HEALTHY              NOT SYNCHRONIZING      RECOVERY_PENDING
(1 row(s) affected)

Screenshot del risultato dell'esecuzione per lo script per controllare l'integrità e lo stato di sincronizzazione del database.

Inoltre, questo database potrebbe essere segnalato come in stato Non sincronizzante/In sospeso di ripristino o Sospetto in SQL Server Management Studio.

Screenshot del database in stato Non sincronizzante/In sospeso di ripristino.

Quando il database è definito in un gruppo di disponibilità, non è possibile eliminare o ripristinare il database. Pertanto, è necessario eseguire passaggi specifici per ripristinare il database e restituirlo all'uso in produzione.

Ulteriori informazioni

Il contenuto seguente illustra gli errori e le limitazioni di un database di disponibilità in stato di ripristino in sospeso in varie situazioni.

Lo stato del database impedisce il ripristino del database

Si tenta di eseguire lo script SQL seguente per ripristinare il database con il RECOVERY parametro :
```
RESTORE DATABASE <DatabaseName> WITH RECOVERY
```
Quando si esegue questo script, viene visualizzato il messaggio di errore seguente perché il database è definito in un gruppo di disponibilità:

Msg 3104, Livello 16, Stato 1, Riga 1
RESTORE non può funzionare nel database <DatabaseName> perché è configurato per il mirroring del database o è stato aggiunto a un gruppo di disponibilità. Se si intende ripristinare il database, usare ALTER DATABASE per rimuovere il mirroring o rimuovere il database dal relativo gruppo di disponibilità.

Msg 3013, Livello 16, Stato 1, Riga 1
RESTORE DATABASE termina in modo anomalo.
Lo stato del database impedisce l'eliminazione del database

Si tenta di eseguire lo script SQL seguente per eliminare il database:
```
DROP DATABASE <DatabaseName>
```
Quando si esegue questo script, viene visualizzato il messaggio di errore seguente perché il database è definito in un gruppo di disponibilità:

Msg 3752, Livello 16, Stato 1, Riga 1
<DatabaseName> è attualmente aggiunto a un gruppo di disponibilità. Prima di eliminare il database, è necessario rimuoverlo dal gruppo di disponibilità.
Lo stato del database impedisce la rimozione del database dal gruppo di disponibilità

Si tenta di eseguire lo script SQL seguente per rimuovere il database dal gruppo di disponibilità:
```
ALTER DATABASE <DatabaseName> SET hadr OFF
```
Quando si tenta di eseguire questo script, viene visualizzato il messaggio di errore seguente perché il database di disponibilità appartiene alla replica primaria:

Msg 35240, Livello 16, Stato 14, Riga 1
Database <DatabaseName> non può essere aggiunto o non aggiunto dal gruppo <di disponibilità AvailabilityGroupName>. Questa operazione non è supportata nella replica primaria del gruppo di disponibilità.

A causa di questo messaggio di errore, potrebbe essere necessario eseguire il failover del database. Dopo il failover del database, la replica proprietaria del database in sospeso di ripristino si trova nel ruolo secondario. In questo caso, si tenta di eseguire di nuovo lo script SQL seguente per rimuovere il database dal gruppo di disponibilità nella replica secondaria:
```
ALTER DATABASE <DatabaseName> SET hadr OFF
```
Tuttavia, non è ancora possibile rimuovere il database dal gruppo di disponibilità e viene visualizzato il messaggio di errore seguente perché il database è ancora in stato Di ripristino in sospeso:

Msg 921, Livello 16, Stato 112, Riga 1
Database <DatabaseName> non ancora recuperato. Attendere e riprovare.

Risoluzione quando il database si trova nel ruolo secondario

Per risolvere questo problema, eseguire le azioni generali seguenti:

Rimuovere dal gruppo di disponibilità la replica che ospita il database danneggiato quando il database è nel ruolo secondario.
Risolvere eventuali problemi che interessano il sistema e che potrebbero aver contribuito all'errore del database.
Ripristinare la replica nel gruppo di disponibilità.

Per eseguire queste azioni, connettersi alla nuova replica primaria ed eseguire lo ALTER AVAILABILITY GROUP script SQL per rimuovere la replica che ospita il database di disponibilità non riuscita. A tale scopo, attenersi alla seguente procedura.

Questi passaggi presuppongono che la replica primaria ospiti prima il database danneggiato. Pertanto, è necessario innanzitutto eseguire un failover per eseguire la transizione della replica che ospita il database danneggiato in un ruolo secondario.

Connettersi al server che esegue SQL Server e che ospita la replica secondaria.

Eseguire lo script SQL seguente:

ALTER AVAILABILITY GROUP <AvailabilityGroupName> FAILOVER

Eseguire lo script SQL seguente per rimuovere la replica che ospita il database danneggiato dal gruppo di disponibilità:
```
ALTER AVAILABILITY GROUP <AvailabilityGroupName> REMOVE REPLICA ON '<SQLServerNodeName>'
```
Risolvere eventuali problemi nel server che esegue SQL Server e che potrebbero contribuire all'errore del database.
Aggiungere nuovamente la replica al gruppo di disponibilità.

Risoluzione quando la replica primaria è l'unica replica nel gruppo di disponibilità

Se la replica primaria ospita il database danneggiato ed è l'unica replica funzionante nel gruppo di disponibilità, è necessario eliminare il gruppo di disponibilità. Dopo l'eliminazione del gruppo di disponibilità, è possibile ripristinare il database da un backup oppure eseguire altre operazioni di ripristino di emergenza per ripristinare i database e riprendere l'produzione.

Per eliminare il gruppo di disponibilità, usare lo script SQL seguente:

DROP AVAILABILITY GROUP <AvailabilityGroupName>

A questo punto, è possibile provare a ripristinare il database problematico. In alternativa, è possibile ripristinare il database dall'ultima copia di backup valida nota.

Risoluzione quando si elimina il gruppo di disponibilità

Quando si elimina un gruppo di disponibilità, anche la risorsa listener viene eliminata e interrompe la connettività dell'applicazione ai database di disponibilità.

Per ridurre al minimo i tempi di inattività dell'applicazione, usare uno dei metodi seguenti per sostenere la connettività dell'applicazione tramite il listener e eliminare il gruppo di disponibilità:

Metodo 1: associare il listener a un nuovo gruppo di disponibilità (ruolo) in Gestione cluster di failover

Questo metodo consente di mantenere il listener durante l'eliminazione e la ricreazione del gruppo di disponibilità.

Nell'istanza di SQL Server a cui il listener del gruppo di disponibilità esistente indirizza le connessioni, creare un nuovo gruppo di disponibilità vuoto. Per semplificare questo processo, usare il comando Transact-SQL per creare un gruppo di disponibilità senza replica o database secondario:
```
USE master
GO
CREATE AVAILABILITY GROUP ag FOR REPLICA ON 'sqlnode1' WITH (
    ENDPOINT_URL = 'tcp://sqlnode1:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL
)
```
Avviare Gestione cluster di failover e quindi selezionare Ruoli nel riquadro sinistro. Nel riquadro in cui sono elencati i ruoli selezionare il gruppo di disponibilità originale.
Nel riquadro inferiore centrale nella scheda Risorse fare clic con il pulsante destro del mouse sulla risorsa del gruppo di disponibilità e quindi scegliere Proprietà. Selezionare la scheda Dipendenze , eliminare la dipendenza dal listener e quindi selezionare OK.
Nelle risorse fare clic con il pulsante destro del mouse sul listener, selezionare Altre azioni e quindi selezionare Assegna a un altro ruolo.
Nella finestra di dialogo Assegna origine al ruolo selezionare il nuovo gruppo di disponibilità e quindi selezionare OK.
Nel riquadro Ruoli selezionare il nuovo gruppo di disponibilità. Nel riquadro inferiore centrale, nella scheda Risorse , verranno visualizzati il nuovo gruppo di disponibilità e la risorsa listener. Fare clic con il pulsante destro del mouse sulla nuova risorsa del gruppo di disponibilità e quindi scegliere Proprietà.
Fare clic sulla scheda Dipendenze , selezionare la risorsa listener nella casella a discesa e quindi selezionare OK.
In SQL Server Management Studio usare Esplora oggetti per connettersi all'istanza di SQL Server che ospita la replica primaria del nuovo gruppo di disponibilità. Selezionare Always On Disponibilità elevata, fare clic sul nuovo gruppo di disponibilità e quindi selezionare Listener del gruppo di disponibilità. È necessario trovare il listener.
Fare clic con il pulsante destro del mouse sul listener, scegliere Proprietà, digitare il numero di porta appropriato per il listener e quindi selezionare OK.

In questo modo, le applicazioni che usano il listener possono comunque usarlo per connettersi all'istanza di SQL Server che ospita i database di produzione senza interruzioni. Il gruppo di disponibilità originale può ora essere completamente rimosso e ricreato. In alternativa, è possibile aggiungere database e repliche al nuovo gruppo di disponibilità.

Se si ricrea il gruppo di disponibilità originale, riassegnare il listener al ruolo del gruppo di disponibilità, configurare la dipendenza tra la nuova risorsa del gruppo di disponibilità e il listener e quindi riassegnare la porta al listener. A tal fine, attenersi alla seguente procedura:

Avviare Gestione cluster di failover e quindi selezionare Ruoli nel riquadro sinistro. Nel riquadro in cui sono elencati i ruoli fare clic sul nuovo gruppo di disponibilità che ospita il listener.
Nel riquadro centrale inferiore nella scheda Risorse fare clic con il pulsante destro del mouse sul listener, selezionare Altre azioni e quindi selezionare Assegna a un altro ruolo. Nella finestra di dialogo scegliere il gruppo di disponibilità ricreato e quindi selezionare OK.
Nel riquadro Ruoli fare clic sul gruppo di disponibilità ricreato. Nel riquadro centrale inferiore, nella scheda Risorse , dovrebbero essere visualizzati il gruppo di disponibilità ricreato e la risorsa listener. Fare clic con il pulsante destro del mouse sulla risorsa del gruppo di disponibilità ricreata e quindi scegliere Proprietà.
Selezionare la scheda Dipendenze , selezionare la risorsa listener nella casella a discesa e quindi selezionare OK.
In SQL Server Management Studio usare Esplora oggetti per connettersi all'istanza di SQL Server che ospita la replica primaria del gruppo di disponibilità ricreato. Selezionare Always On Disponibilità elevata, fare clic sul nuovo gruppo di disponibilità e quindi selezionare Listener del gruppo di disponibilità. È necessario trovare il listener.
Fare clic con il pulsante destro del mouse sul listener, scegliere Proprietà, digitare il numero di porta appropriato per il listener e quindi selezionare OK.

Metodo 2: associare il listener a un'istanza cluster di failover SQL Server esistente (SQLFCI)

Se si ospita il gruppo di disponibilità in un'istanza cluster di failover (SQLFCI) SQL Server, è possibile associare la risorsa cluster del listener al gruppo di risorse cluster SQLFCI mentre si elimina e quindi si ricrea il gruppo di disponibilità.

Avviare Gestione cluster di failover e quindi selezionare Ruoli nel riquadro sinistro.
Nel riquadro in cui sono elencati i ruoli selezionare il gruppo di disponibilità originale.
Nel riquadro centrale inferiore nella scheda Risorse fare clic con il pulsante destro del mouse sulla risorsa del gruppo di disponibilità e quindi scegliere Proprietà.
Selezionare la scheda Dipendenze , eliminare la dipendenza dal listener e quindi selezionare OK.
Nel riquadro centrale inferiore nella scheda Risorse fare clic con il pulsante destro del mouse sul listener, selezionare Altre azioni e quindi selezionare Assegna a un altro ruolo.
Nella finestra di dialogo Assegna risorsa al ruolo fare clic sull'istanza del cluster di failover SQL Server e quindi selezionare OK.
Nel riquadro Ruoli selezionare il gruppo SQLFCI. Nel riquadro centrale inferiore, nella scheda Risorse , verrà visualizzata la nuova risorsa listener.

In questo modo, le applicazioni che usano il listener possono comunque usarlo per connettersi all'istanza di SQL Server che ospita i database di produzione senza interruzioni. Il gruppo di disponibilità originale può ora essere rimosso e ricreato. In alternativa, è possibile aggiungere database e repliche al nuovo gruppo di disponibilità.

Dopo aver ricreato il gruppo di disponibilità, riassegnare il listener al ruolo del gruppo di disponibilità. Configurare quindi la dipendenza tra la nuova risorsa del gruppo di disponibilità e il listener e riassegnare la porta al listener:

Avviare Gestione cluster di failover e quindi selezionare Ruoli nel riquadro sinistro.
Nel riquadro in cui sono elencati i ruoli fare clic sul ruolo SQLFCI originale.
Nel riquadro centrale inferiore, nella scheda Risorse fare clic con il pulsante destro del mouse sul listener, selezionare Altre azioni e quindi selezionare Assegna a un altro ruolo.
Nella finestra di dialogo fare clic sul gruppo di disponibilità ricreato e quindi selezionare OK.
Nel riquadro Ruoli selezionare il nuovo gruppo di disponibilità.
Nella scheda Risorse dovrebbero essere visualizzati il nuovo gruppo di disponibilità e la risorsa listener. Fare clic con il pulsante destro del mouse sulla nuova risorsa del gruppo di disponibilità e quindi scegliere Proprietà.
Selezionare la scheda Dipendenze , selezionare la risorsa listener nella casella a discesa e quindi selezionare OK.
In SQL Server Management Studio usare Esplora oggetti per connettersi all'istanza di SQL Server che ospita la replica primaria del nuovo gruppo di disponibilità.
Selezionare Always On Disponibilità elevata, fare clic sul nuovo gruppo di disponibilità e quindi selezionare Listener del gruppo di disponibilità. È necessario trovare il listener.
Fare clic con il pulsante destro del mouse sul listener, scegliere Proprietà, digitare il numero di porta appropriato per il listener e quindi selezionare OK.

Metodo 3: eliminare il gruppo di disponibilità e quindi ricreare il gruppo di disponibilità e il listener con lo stesso nome del listener

Questo metodo comporterà una piccola interruzione per le applicazioni attualmente connesse perché il gruppo di disponibilità e il listener vengono eliminati e quindi ricreati:

Eliminare il gruppo di disponibilità.

Nota

In questo modo verrà anche eliminata la listener.
Creare immediatamente un nuovo gruppo di disponibilità vuoto che include la definizione del listener nello stesso server che ospita i database di produzione.

Si supponga, ad esempio, che il listener del gruppo di disponibilità sia aglisten. L'istruzione Transact-SQL seguente crea un gruppo di disponibilità senza database primario o secondario, ma crea anche un listener denominato aglisten. Le applicazioni possono usare questo listener per connettersi.
```
USE master
GO
    CREATE AVAILABILITY GROUP ag FOR REPLICA ON 'sqlnode1' WITH (
        ENDPOINT_URL = 'tcp://sqlnode1:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        FAILOVER_MODE = MANUAL
    ) LISTENER 'aglisten' (
        WITH IP ((N'11.0.0.25', N'255.0.0.0')),
        PORT = 1433
    )
GO
```
Ripristinare il database danneggiato. Quindi aggiungerlo e la replica secondaria al gruppo di disponibilità.