Gestire il ripristino accelerato del database

  • Articolo

Si applica a: SQL Server 2019 (15.x)

Questo articolo contiene informazioni sulle procedure consigliate per la gestione e la configurazione del ripristino accelerato del database in SQL Server 2019 (15.x) e versioni successive. Per altre informazioni sul ripristino accelerato del database in Azure SQL, vedere Ripristino accelerato del database in Azure SQL.

Nota

In database SQL di Azure e Istanza gestita di SQL di Azure il ripristino accelerato del database (ADR) è abilitato in tutti i database e non può essere disabilitato. Se si osservano problemi con l'utilizzo dell'archiviazione, la transazione di interruzione elevata e ad altri fattori, vedere Risoluzione dei problemi relativi al ripristino accelerato del database o contattare il supporto di Azure.

Utenti che possono trarre vantaggio dal ripristino accelerato del database

Molti clienti ritengono che il ripristino accelerato del database (ADR) sia una tecnologia preziosa per migliorare i tempi di ripristino. Prima di decidere quali database debbano ricorrere al ripristino accelerato del database, è necessario prendere in considerazione un insieme di fattori, valutando se l'insieme dei fattori è favorevole o contrario all'uso del ripristino accelerato del database.

  • Il ripristino accelerato del database è consigliato per i carichi di lavoro con transazioni a esecuzione prolungata che non possono essere evitate. Ad esempio, nei casi in cui le transazioni a esecuzione prolungata sono a rischio di essere sottoposte a rollback, è possibile eseguire il rollback di ripristino accelerato del database.

  • Il ripristino accelerato del database è consigliato per i carichi di lavoro in cui le transazioni attive causano una crescita significativa del log delle transazioni.

  • Il ripristino accelerato del database è consigliato per carichi di lavoro che hanno riscontrato lunghi periodi di indisponibilità del database a causa di un ripristino a esecuzione prolungata di SQL Server, ad esempio in caso di riavvio imprevisto di SQL Server o rollback di transazione manuale.

  • Il ripristino accelerato del database non è supportato per i database registrati nel mirroring del database.

  • Il ripristino accelerato del database non è consigliato per i database di dimensioni superiori a 100 terabyte a causa della pulizia della versione dell’archivio versioni persistente (PVS) a thread singolo.

  • Se l'applicazione esegue molti aggiornamenti incrementali non in batch, ad esempio l'aggiornamento di un record ogni volta che viene eseguito l'accesso o l'inserimento di una riga, il carico di lavoro potrebbe non essere ottimale per il ripristino accelerato del database.. Valutare la possibilità di riscrivere le query dell'applicazione per aggiornare in batch, se possibile, fino alla fine del comando e ridurre un numero elevato di transazioni di aggiornamento di piccole dimensioni.

Valutare se il carico di lavoro è adatto per il ripristino accelerato del database

Dopo aver abilitato il ripristino accelerato del database nel carico di lavoro, cercare i segni che indicano che l'archivio delle versioni permanenti (PVS) non è in grado di rimanere aggiornato. È consigliabile monitorare l'integrità del ripristino accelerato del database usando i metodi disponibili in Risoluzione dei problemi relativi al ripristino accelerato del database.

Il ripristino accelerato del database non è consigliato per gli ambienti di database con un numero elevato di aggiornamenti/eliminazioni, ad esempio OLTP ad alto volume, senza un periodo di riposo/ripristino per il processo di pulizia dell’archivio delle versioni permanenti per recuperare spazio. In genere, i cicli di operazioni aziendali consentono questo periodo di tempo, ma in alcuni scenari è consigliabile avviare manualmente il processo di pulizia dell’archivio delle versioni permanenti per sfruttare le condizioni di attività dell'applicazione.

  • Se il processo di pulizia dell’archivio delle versioni permanenti è in esecuzione per un lungo periodo di tempo, si potrebbe notare che il conteggio delle transazioni interrotte aumenterà, causando anche l'aumento delle dimensioni dell’archivio delle versioni permanenti. Usare la DMV sys.dm_tran_aborted_transactions per segnalare il numero di transazioni interrotte e usare sys.dm_tran_persistent_version_store_stats per segnalare l'ora di inizio/fine della pulizia insieme alle dimensioni dell’archivio delle versioni permanenti. Per altre informazioni, vedere sys.dm_tran_persistent_version_store_stats.

  • Per attivare manualmente il processo di pulizia del PVS tra carichi di lavoro o durante le finestre di manutenzione, usare sys.sp_persistent_version_cleanup. Per altre informazioni, consultare sys.sp_persistent_version_cleanup.

  • I carichi di lavoro con query a esecuzione prolungata in modalità di isolamento SNAPSHOT o READ COMMITTED SNAPSHOT possono ritardare la pulizia del ripristino accelerato del database PVS in altri database, causando la crescita del file PVS. Per altre informazioni, vedere la sezione relativa alle scansioni snapshot attive di lunga durata in Risolvere i problemi relativi al ripristino accelerato del database. Questo vale per le istanze di SQL Server e Istanza gestita di SQL di Azure o in un pool elastico del database SQL di Azure.

Procedure consigliate per il ripristino accelerato del database

Questa sezione contiene indicazioni e consigli per il ripristino accelerato del database.

  • Per SQL Server, isolare l'archivio delle versioni PVS in un filegroup in un archivio di livello superiore, ad esempio unità SSD di fascia alta o UNITÀ SSD avanzata o memoria persistente (PMEM), talvolta definito memoria della classe di archiviazione (SCM). Per maggiori informazioni, vedere Modificare la posizione dell'archivio versioni permanente in un filegroup diverso. Questa opzione non è disponibile per database SQL di Azure e Istanza gestita di SQL di Azure.

  • Assicurarsi che nel database sia disponibile spazio sufficiente per tenere conto dell'utilizzo di PVS. Se il database non dispone di spazio sufficiente per l'aumento delle dimensioni del PVS, il ripristino accelerato del database non genererà versioni. Il ripristino accelerato del database consente di risparmiare spazio nell'archivio delle versioni rispetto all'archivio delle versioni tempdb.

  • Evitare più transazioni a esecuzione prolungata nel database. Anche se un obiettivo del ripristino accelerato del database è velocizzare il ripristino del database rallentato da rollforward, più transazioni a esecuzione prolungata possono ritardare la pulizia della versione e aumentare le dimensioni del PVS.

  • Evitare transazioni di grandi dimensioni con modifiche alla definizione dei dati o operazioni DDL. Il ripristino accelerato del database usa un meccanismo SLOG (flusso di log di sistema) per tenere traccia delle operazioni DDL usate nel ripristino. Il flusso SLOG viene utilizzato solo quando la transazione è attiva. Poiché viene sottoposto a checkpoint, è possibile evitare transazioni di grandi dimensioni che usano SLOG per migliorare le prestazioni complessive. Gli scenari seguenti possono causare l’uso di più spazio da parte di SLOG:

    • In un’unica transazione vengono eseguiti molti DDLS. Ad esempio, la creazione rapida e l’eliminazione di tabelle temporanee in un’unica transazione.

    • Una tabella ha un numero elevato di partizioni o indici modificati. Ad esempio, un’operazione DROP TABLE in tale tabella richiederebbe una grande prenotazione di memoria SLOG, che ritarderebbe il troncamento del log delle transazioni e le operazioni di annullamento/rollforward. La soluzione alternativa può essere eliminare gli indici singolarmente e gradualmente, per poi eliminare la tabella. Per altre informazioni su SLOG, consultare Componenti del ripristino accelerato del database.

  • Evitare o ridurre interruzioni non necessarie. Una velocità di interruzione elevata metterà pressione sul pulitore del PVS e ridurrà le prestazioni del ripristino accelerato del database. Le interruzioni possono provenire da una frequenza elevata di deadlock, chiavi duplicate o altre violazioni dei vincoli.

    • La DMV sys.dm_tran_aborted_transactions mostra tutte le transazioni interrotte nell’istanza di SQL Server. La colonna nested_abort indica che la transazione è stata sottoposta a commit, ma sono presenti parti interrotte (punti di salvataggio o transazioni nidificate) che possono bloccare il processo di pulizia del PVS. Per altre informazioni, consultare sys.dm_tran_aborted_transactions (Transact-SQL).

Abilitazione e controllo del ripristino accelerato del database

Nota

In database SQL di Azure e Istanza gestita di SQL di Azure, il ripristino accelerato del database (ADR) è abilitato in tutti i database e non può essere disabilitato o spostato in un filegroup diverso.

La funzionalità di ripristino accelerato del database è disattivata per impostazione predefinita in SQL Server 2019 (15.x) e può essere controllata usando la sintassi DDL:

ALTER DATABASE [DB] SET ACCELERATED_DATABASE_RECOVERY = {ON | OFF};

Usare questa sintassi per controllare se la funzionalità è attivata o disattivata e designare un filegroup specifico per i dati dell'archivio versioni permanente (PSV, Persistent Version Store). Se non è specificato alcun filegroup, per l'archivio versioni permanente viene usato il filegroup PRIMARY.

Per modificare questo stato, è necessario un blocco esclusivo nel database. Ciò significa che il comando ALTER DATABASE si blocca fino a quando tutte le sessioni attive non sono più attive e che tutte le nuove sessioni attenderanno dietro il comando ALTER DATABASE. Se è importante completare l'operazione e rimuovere il blocco, è possibile usare la clausola di terminazione per WITH ROLLBACK [IMMEDIATE | AFTER {number} SECONDS | NO_WAIT] interrompere le sessioni attive nel database. Per altre informazioni, vedere Opzioni di ALTER DATABASE SET.

Gestione del filegroup dell'archivio versioni permanente

La funzionalità di ripristino accelerato del database si basa sull'assegnazione di versioni alle modifiche, con versioni diverse di un elemento dati conservate nell'archivio versioni permanente. Ci sono alcune considerazioni in merito all'individuazione della posizione dell'archivio versioni permanente e a come gestire le dimensioni dei dati in tale archivio.

Per abilitare il ripristino accelerato del database senza specificare un filegroup

Questa operazione richiede l'accesso esclusivo al database.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

In questo caso, se il filegroup dell'archivio versioni permanente non viene specificato, i dati dell'archivio vengono inseriti nel filegroup PRIMARY.

Per abilitare il ripristino accelerato del database e specificare che l'archivio versioni permanente deve essere archiviato nel filegroup

È possibile configurare il ripristino accelerato del database per l'uso di un altro filegroup, oltre a quello predefinito PRIMARY , per contenere i dati dell’archivio versioni.

Prima di abilitare il ripristino accelerato del database in un filegroup oltre a PRIMARY, è necessario creare il filegroup e il file di dati.

Creare il filegroup VersionStoreFG e creare un nuovo file di dati nel filegroup. Ad esempio:

ALTER DATABASE [MyDatabase] ADD FILEGROUP [VersionStoreFG];
GO
ALTER DATABASE [MyDatabase] ADD FILE ( NAME = N'VersionStoreFG'
, FILENAME = N'E:\DATA\VersionStore.ndf'
, SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [VersionStoreFG];
GO

Dopo aver creato il filegroup e un file di dati secondario, abilitare il ripristino accelerato del database e specificare che il file dell’archivio versioni deve essere archiviato in un filegroup specifico. Questa operazione richiede l'accesso esclusivo al database.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Per disabilitare la funzionalità di ripristino accelerato del database

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

Anche dopo aver disabilitato la funzionalità di ripristino accelerato del database, saranno presenti versioni archiviate nell'archivio versioni permanente ancora necessarie al sistema per il ripristino logico.

Modificare la posizione dell'archivio versioni permanente in un filegroup diverso

In SQL Server, può essere necessario spostare l'archivio versioni permanente in un filegroup diverso per vari motivi. Un archivio versioni permanente potrebbe ad esempio richiedere più spazio o una maggiore velocità di archiviazione.

Il processo di modifica della posizione dell'archivio versioni permanente prevede tre passaggi.

  1. Disabilita il ripristino accelerato del database

    ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

  2. Attendere che tutte le versioni archiviate nell'archivio versioni permanente possano essere liberate.

    Per poter attivare il ripristino accelerato del database con una nuova posizione per l'archivio versioni permanente, è necessario prima di tutto assicurarsi che tutte le informazioni sulle versioni siano state eliminate dalla posizione dell'archivio versioni permanente precedente. Per forzare tale pulizia, eseguire il comando:

    EXEC sys.sp_persistent_version_cleanup [database name];

    La stored procedure sys.sp_persistent_version_cleanup è sincrona, ovvero non verrà completata fino a quando non vengono eliminate tutte le informazioni sulle versioni dall'archivio versioni permanente corrente. Dopo il completamento, è possibile verificare che le informazioni sulle versioni siano state effettivamente rimosse eseguendo una query sulla DMV sys.dm_tran_persistent_version_store_stats ed esaminando il valore di persistent_version_store_size_kb.

    SELECT DB_Name(database_id), persistent_version_store_size_kb 
FROM sys.dm_tran_persistent_version_store_stats where database_id = [MyDatabaseID];

    Quando il valore di persistent_version_store_size_kb è 0, è possibile riabilitare la funzionalità di ripristino accelerato del database, configurando l'archivio versioni permanente nel nuovo filegroup.

  3. Attivare il ripristino accelerato del database specificando la nuova posizione per l'archivio versioni permanente:

    ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Passaggi successivi