Ottimizzazione delle prestazioni di backup e ripristino in SQL Server

Data aggiornamento: 14 aprile 2006

Microsoft SQL Server 2005 consente di accelerare le operazioni di backup e ripristino nei due modi seguenti:

• Utilizzo di più periferiche di backup per la scrittura in parallelo dei backup in tutte le periferiche. La velocità della periferica di backup può rappresentare un potenziale collo di bottiglia per la velocità effettiva del backup. Utilizzo di più periferiche, con cui è possibile ottenere un aumento della velocità effettiva proporzionalmente al numero di periferiche utilizzate. In modo analogo, è possibile eseguire il ripristino del backup in parallelo da più periferiche. Per ulteriori informazioni, vedere "Utilizzo di più supporti o di più periferiche" di seguito in questo argomento.
• Utilizzo di una combinazione di backup completi, differenziali e, per il modello di recupero con registrazione completa o con registrazione minima delle transazioni di massa, di backup del log delle transazioni, in modo da ridurre al minimo i tempi di ripristino. La creazione di backup differenziali del database risulta in genere più rapida rispetto alla creazione dei backup completi e consente di ripristinare il database utilizzando una parte del log delle transazioni più ridotta. Per ulteriori informazioni, vedere Creazione di backup completi e differenziali per un database di SQL Server.

Utilizzo di più supporti o di più periferiche

La copia dei dati e del log delle transazioni dalle periferiche di backup ai file di database e del log delle transazioni viene eseguita da thread di lettura/scrittura. A ogni periferica di backup viene assegnato un thread distinto. Le prestazioni possono risultare limitate dalla capacità di trasmissione dei dati delle periferiche di backup oppure dalla capacità di ricezione dei dati dei file di database o dei file del log delle transazioni. Pertanto, le prestazioni aumentano proporzionalmente al numero di periferiche di backup, fino al raggiungimento della velocità effettiva massima della capacità di ricezione dati dei file di database o dei file del log delle transazioni.

L'utilizzo di più periferiche di backup per le operazioni di backup e di ripristino consente a SQL Server l'utilizzo dell'input/output (I/O) parallelo per aumentare la velocità delle operazioni di backup e ripristino, in quanto le operazioni di lettura o scrittura nelle diverse periferiche vengono eseguite contemporaneamente. Nel caso di organizzazioni con database di grandi dimensioni, l'utilizzo di più periferiche di backup consente di ridurre notevolmente il tempo necessario per le operazioni di backup e ripristino. SQL Server supporta un massimo di 64 periferiche di backup per ogni singola operazione di backup.

Durante la scrittura di un backup di database in più periferiche si verificano vari punti di sincronizzazione interna. Il punto di sincronizzazione più importante si verifica quando il backup di tutti i dati del database è stato completato e sta per essere eseguito il backup del log delle transazioni.

Importante:
Quando un'operazione di backup viene eseguita su più periferiche, i supporti possono essere utilizzati esclusivamente per operazioni di backup di SQL Server. Per ulteriori informazioni, vedere Utilizzo di supporti di backup.

La procedura per la creazione e il ripristino di backup tramite più periferiche di backup è identica alla procedura che prevede l'utilizzo di una sola periferica. L'unica differenza è data dal fatto che è necessario specificare tutte le periferiche di backup, anziché una sola. Se, ad esempio, si desidera creare il backup di un database tramite tre periferiche di backup su nastro, \\.\TAPE0, \\.\TAPE1 e \\.\TAPE2, sarà necessario specificare tutte queste periferiche nastro per l'operazione di backup. Al momento del successivo ripristino del backup, sarà tuttavia possibile utilizzare un numero inferiore di periferiche di backup su nastro.

Quando si crea un backup su più periferiche utilizzando supporti rimovibili, le periferiche possono operare a velocità diverse e lo spazio disponibile sui volumi dei supporti può essere diverso. Se durante il backup si esaurisce lo spazio disponibile sul volume dei supporti di una periferica di backup, l'operazione di scrittura sulla periferica verrà interrotta e verrà richiesto di inserire un nuovo volume dei supporti. La periferica risulterà bloccata fino a quando non si sostituisce il volume dei supporti pieno con uno vuoto. Nel frattempo l'operazione di backup prosegue con la scrittura dei dati nelle altre periferiche i cui supporti dispongono dello spazio necessario. Dopo la sostituzione del volume dei supporti pieno, la relativa periferica risulta nuovamente disponibile ed è possibile riprendere la scrittura dei dati su tale periferica. Tenere presente, tuttavia, che in caso di punto di sincronizzazione interno durante il blocco di una qualsiasi periferica l'operazione di backup viene sospesa fino a quando la periferica non torna nuovamente disponibile.

Esempio

Si consideri uno scenario in cui per l'archiviazione di un backup completo del database vengono utilizzate tre periferiche di backup con la stessa velocità. Sui primi due nastri sono disponibili 10 gigabyte (GB), ma sul terzo sono disponibili solo 5 GB. Se si esegue il backup di un database da 20 GB su tutte e tre le periferiche di backup contemporaneamente, il terzo nastro esaurirà lo spazio disponibile prima del completamento del backup. Terminata la scrittura di 5 GB di dati sul terzo nastro, l'operazione verrà interrotta, la periferica verrà bloccata e verrà richiesto l'inserimento di un nuovo nastro. La scrittura dei dati di backup continuerà nelle altre due periferiche. Prima della sostituzione del terzo nastro, tuttavia, si verifica un punto di sincronizzazione interno, in corrispondenza del quale l'intera operazione di backup viene sospesa fino a quando non viene montato un nuovo nastro nella terza periferica.

Ottimizzazione delle prestazioni dei backup completi e differenziali

La procedura per la creazione di un backup completo o differenziale prevede i passaggi seguenti:

1. Copia dei dati dai file del database nelle periferiche di backup.
2. Copia della parte del log delle transazioni necessaria per il rollforward del database in uno stato consistente con le stesse periferiche di backup.

La procedura per la creazione di un backup differenziale è identica alla procedura per la creazione di un backup completo, con la sola eccezione che vengono copiati solo i dati modificati. L'operazione di backup di un file di database consiste nella copia dei dati del file nelle periferiche di backup.

I file di database utilizzati per l'archiviazione del database vengono ordinati da una periferica disco e a ogni periferica viene assegnato un thread di lettura. Il thread di lettura legge i dati dai file di database. A ogni periferica di backup viene assegnato un thread di scrittura. Il thread di scrittura registra i dati nella periferica di backup. È possibile aumentare il numero di operazioni di lettura parallele suddividendo i file di database tra più unità logiche. In modo analogo, è possibile aumentare il numero di operazioni di scrittura in parallelo aumentando il numero delle periferiche di backup utilizzate.

In genere il collo di bottiglia è rappresentato dai file di database o dalle periferiche di backup. Se la velocità effettiva totale di lettura è maggiore della velocità effettiva totale della periferica di backup, il collo di bottiglia sarà presente a livello della periferica di backup. L'utilizzo di periferiche di backup aggiuntive, e se necessario di controller SCSI aggiuntivi, può comportare un miglioramento delle prestazioni. Se invece la velocità effettiva totale di backup è maggiore della velocità effettiva totale di lettura, è necessario aumentare la velocità effettiva di lettura, ad esempio aggiungendo file di database o periferiche oppure utilizzando dischi aggiuntivi in una periferica RAID (Redundant Array of Independent Disks).

Ottimizzazione delle prestazioni del backup del log delle transazioni

La creazione di un backup del log delle transazioni consiste semplicemente nel copiare nelle periferiche di backup la parte del log di cui non è ancora stato eseguito il backup. Anche quando sono disponibili più file del log delle transazioni, a livello logico il log delle transazioni consiste in un unico flusso di dati letto in modo sequenziale da un solo thread.

A ogni periferica di backup viene assegnato un thread di scrittura. L'aggiunta di periferiche di backup determina un miglioramento delle prestazioni.

Il collo di bottiglia può essere rappresentato dalla periferica disco contenente i file del log delle transazioni o dalla periferica di backup, a seconda della velocità relativa della periferica e del numero di periferiche di backup utilizzate. L'aggiunta di periferiche di backup comporta miglioramenti lineari delle prestazioni fino al raggiungimento della capacità massima della periferica disco contenente i file del log delle transazioni. Ulteriori miglioramenti sono possibili solo aumentando la velocità delle periferiche disco contenenti il log delle transazioni, ad esempio tramite lo striping del disco.

Ottimizzazione delle prestazioni di ripristino

La procedura per il ripristino di un backup del database o di un backup differenziale prevede quattro passaggi:

1. Creazione dei file di database e dei file del log delle transazioni (se non esistono già).
2. Copia dei dati dalle periferiche di backup ai file di database.
3. Copia del log delle transazioni dai file del log delle transazioni.
4. Rollforward del log delle transazioni e riavvio dell'operazione di recupero, se necessario.

La procedura per l'applicazione di un backup del log delle transazioni prevede due passaggi:

1. Copia dei dati dalle periferiche di backup al file del log delle transazioni.
2. Rollforward del log delle transazioni.

La procedura per il ripristino di un file di database prevede due passaggi:

1. Creazione di eventuali file di database mancanti.
2. Copia dei dati dalle periferiche di backup ai file di database.

Inizializzazione dei file

Se i file di database e i file del log delle transazioni non esistono, è necessario crearli in modo da poter ripristinare i dati. I file di database e i file di log delle transazioni vengono creati e il contenuto dei file viene inizializzato su zero. I file vengono creati e inizializzati in parallelo da thread di lavoro distinti. I file di database e i file del log delle transazioni vengono ordinati in base alla periferica disco e a ogni periferica viene assegnato un thread di lavoro distinto. Poiché la creazione e l'inizializzazione di file richiede una velocità effettiva molto elevata, una suddivisione equa dei file tra le varie unità logiche disponibili consente di ottenere prestazioni ottimali.

Inizializzazione dei file immediata

In SQL Server 2005 i file di dati possono essere inizializzati in modo immediato, con un conseguente incremento della velocità di esecuzione delle operazioni di ripristino di database o filegroup. Tramite l'inizializzazione dei file immediata infatti viene recuperato lo spazio su disco in uso evitando il riempimento di tale spazio con zeri. Il contenuto del disco viene invece sovrascritto via via che nuovi dati vengono scritti nei file. L'inizializzazione dei file di log richiede comunque l'azzeramento, ma esso avviene in parallelo con il trasferimento dei dati dal backup. Il passaggio costituito dal rollforward, nell'ambito della procedura di ripristino, viene avviato solo dopo il trasferimento di tutti i dati e l'inizializzazione del log completo.

[!NOTA] L'inizializzazione immediata dei file è disponibile solo in sistemi Microsoft Windows XP o .

Per applicarla, è necessario eseguire l'account del servizio MSSQLSERVER con un account di Windows e assegnare il privilegio speciale SE_MANAGE_VOLUME_NAME a tale account di Windows. Questo privilegio viene assegnato al gruppo Administrators di Windows per impostazione predefinita. Se si dispone di diritti di amministratore di sistema, è possibile assegnare questo privilegio aggiungendo l'account di Windows al criterio di protezione Esecuzione operazioni di manutenzione volume. Per ulteriori informazioni sull'assegnazione di diritti utente, vedere la documentazione di Windows.

Ottimizzazione delle prestazioni delle periferiche di backup su nastro

Le prestazioni delle periferiche di backup su nastro sono influenzate da numerose variabili, che consentono un miglioramento pressoché lineare delle prestazioni di SQL Server con l'aggiunta di periferiche nastro:

• Dimensioni dei blocchi di dati software
• Numero di periferiche nastro che condividono un bus SCSI (Small Computer System Interface)
• Tipo di periferica nastro

La dimensione dei blocchi di dati software viene gestita in modo ottimale da SQL Server e non deve essere modificata. Le dimensioni massime di BLOCKSIZE sono pari a 64 KB.

Molte unità a nastro ad alta velocità offrono prestazioni migliori se ogni unità dispone di un bus SCSI dedicato. Le unità con velocità di trasferimento nativa superiore al 50% della velocità del bus SCSI devono essere utilizzate su bus SCSI dedicati, per evitare riduzioni delle prestazioni. Per ulteriori informazioni sulle impostazioni che influiscono sulle prestazioni delle unità a nastro, vedere la documentazione dell'unità nastro.

Importante:
Non associare mai un'unità nastro allo stesso bus SCSI utilizzato da unità disco o da unità CD-ROM. Le operazioni di gestione degli errori di tali periferiche sono incompatibili tra loro.

Quando si eseguono operazioni multiple di backup su un nastro caricato, è possibile migliorare le prestazioni specificando NOREWIND. Questa opzione fa in modo che in SQL Server il nastro o i nastri rimangano aperti dopo l'operazione di backup. L'opzione NOREWIND implica l'utilizzo di NOUNLOAD.

Ottimizzazione delle prestazioni delle periferiche di backup su disco

La velocità di I/O nominale della periferica di backup su disco influisce sulle prestazioni della periferica e consente a SQL Server un miglioramento pressoché lineare delle prestazioni di backup e ripristino mano a mano che vengono aggiunte altre unità disco.

È necessario valutare con attenzione l'utilizzo di RAID per una periferica di backup su disco. Ad esempio, il sistema RAID 5 offre prestazioni di scrittura limitate, simili a quelle fornite da un disco singolo, a causa del carico aggiuntivo di gestione delle informazioni di parità. Inoltre la velocità nominale di aggiunta di dati a un file è notevolmente inferiore alla velocità nominale di scrittura della periferica.

Se la periferica di backup è caratterizzata da uno striping intensivo, per cui la velocità massima di scrittura nella periferica di backup supera in misura significativa la velocità di aggiunta di dati a un file, può risultare opportuno includere più periferiche di backup logiche nello stesso set di striping. In altri termini è possibile migliorare le prestazioni di backup ponendo più gruppi di supporti di backup nella stessa unità logica. Soltanto un approccio empirico, tuttavia, consente di determinare se questa operazione è vantaggiosa o meno ai fini delle prestazioni in un particolare ambiente operativo. In genere è opportuno includere ogni periferica di backup in una periferica disco distinta.

In un bus SCSI è in genere possibile utilizzare un numero limitato di dischi alla velocità massima, anche se i bus Ultra-Wide e Ultra-2 offrono potenzialità maggiori. Per ottenere prestazioni ottimali, è necessaria una configurazione accurata dei componenti hardware.

Per ulteriori informazioni sulle impostazioni che influiscono sulle prestazioni del disco, vedere la documentazione fornita dal produttore dell'unità disco.

Compressione dei dati

Le unità nastro recenti offrono funzionalità di compressione dati hardware incorporate che consentono un incremento significativo della velocità di trasferimento dei dati nell'unità. La capacità di compressione dei dati reali del database dipende sia dalla natura dei dati stessi che dal tipo di unità nastro utilizzata. I rapporti di compressione tipici vanno da 1,2:1 a 2:1 per vari di tipi di database. Questi rapporti sono validi per i dati di una vasta gamma di applicazioni business, anche se alcuni database possono presentare rapporti di compressione superiori o inferiori. Ad esempio, un database costituito in gran parte da immagini già compresse non verrà compresso ulteriormente nelle unità nastro. Per ulteriori informazioni sulla compressione dei dati, vedere la documentazione fornita dal produttore dell'unità nastro in uso.

Per impostazione predefinita SQL Server supporta la compressione hardware. È possibile disattivare la compressione tramite il flag di traccia 3205. In alcuni casi rari la disattivazione della compressione hardware può migliorare le prestazioni di backup. Ad esempio, se i dati sono già completamente compressi, quando si disattiva la compressione hardware non verrà eseguita alcuna ulteriore compressione dei dati nell'unità nastro, con un conseguente risparmio di tempo.

Per ulteriori informazioni sui flag di traccia, vedere Flag di traccia (Transact-SQL).

Quantità di dati trasferiti su nastro

In un backup dei dati o differenziale viene acquisita soltanto la parte del database che include dati reali, non lo spazio inutilizzato. Le operazioni di backup pertanto risultano più rapide.

Sebbene sia possibile configurare l'aumento automatico delle dimensioni dei database SQL Server 2005, è comunque possibile riservare spazio del database in modo da garantirne la disponibilità. Ciò non ha alcun effetto negativo sulla velocità effettiva dell'operazione di backup, né sul tempo complessivo necessario per l'esecuzione del backup del database.

Ottimizzazione della sincronizzazione della distribuzione dei log

Quando si tenta di sincronizzare una destinazione di distribuzione dei log, non è necessario utilizzare WITH STANDBY tra i passaggi RESTORE LOG.

Vedere anche

Riferimento

Oggetto Backup Device di SQL Server
Oggetto Databases di SQL Server

Concetti

Operazioni di backup e ripristino in ambienti critici di grandi dimensioni
Informazioni sulle prestazioni di recupero in SQL Server

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche