Ottimizzazione delle prestazioni di backup e ripristino in SQL Server
MicrosoftSQL Server consente di accelerare le operazioni di backup e ripristino nei due modi seguenti:
Utilizzo di più dispositivi di backup per la scrittura in parallelo dei backup in tutti i dispositivi. La velocità del dispositivo di backup può rappresentare un potenziale collo di bottiglia per la velocità effettiva del backup. Utilizzo di più dispositivi, con cui è possibile ottenere un aumento della velocità effettiva proporzionalmente al numero di dispositivi utilizzati. In modo analogo, è possibile eseguire il ripristino del backup in parallelo da più dispositivi. Per ulteriori informazioni, vedere "Utilizzo di più supporti o di più dispositivi" 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 operazioni bulk, 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ù dispositivi
La copia dei dati e del log delle transazioni dai dispositivi di backup ai file di database e del log delle transazioni viene eseguita da thread di lettura/scrittura. A ogni dispositivo di backup viene assegnato un thread distinto. Le prestazioni possono risultare limitate dalla capacità di trasmissione dei dati dei dispositivi 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 dispositivi 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ù dispositivi 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 nei diversi dispositivi vengono eseguite contemporaneamente. Nel caso di organizzazioni con database di grandi dimensioni, l'utilizzo di più dispositivi di backup consente di ridurre notevolmente il tempo necessario per le operazioni di backup e ripristino. SQL Server supporta un massimo di 64 dispositivi di backup per ogni singola operazione di backup.
Durante la scrittura di un backup di database in più dispositivi 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.
.gif "Nota importante") Importante |
|---|
Quando un'operazione di backup viene eseguita su più dispositivi, 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ù dispositivi di backup è identica alla procedura che prevede l'utilizzo di un solo dispositivo. L'unica differenza è data dal fatto che è necessario specificare tutti i dispositivi di backup, anziché uno solo. Se, ad esempio, si desidera creare il backup di un database tramite tre dispositivi di backup su nastro, \\.\TAPE0, \\.\TAPE1 e \\.\TAPE2, sarà necessario specificare tutti questi dispositivi nastro per l'operazione di backup. Al momento del successivo ripristino del backup, sarà tuttavia possibile utilizzare un numero inferiore di dispositivi di backup su nastro.
Quando si crea un backup su più dispositivi utilizzando supporti rimovibili, i dispositivi 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 un dispositivo di backup, l'operazione di scrittura sul dispositivo verrà interrotta e verrà richiesto di inserire un nuovo volume dei supporti. Il dispositivo risulterà bloccato 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 negli altri dispositivi i cui supporti dispongono dello spazio necessario. Dopo la sostituzione del volume dei supporti pieno, il relativo dispositivo risulta nuovamente disponibile ed è possibile riprendere la scrittura dei dati su tale dispositivo. Tenere presente, tuttavia, che in caso di punto di sincronizzazione interno durante il blocco di un qualsiasi dispositivo l'operazione di backup viene sospesa fino a quando il dispositivo non torna nuovamente disponibile.
Esempio
Si consideri uno scenario in cui per l'archiviazione di un backup completo del database vengono utilizzate tre dispositivi 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 tutti e tre i dispositivi 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, il dispositivo verrà bloccato e verrà richiesto l'inserimento di un nuovo nastro. La scrittura dei dati di backup continuerà negli altri due dispositivi. 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 nel terzo dispositivo.
Ottimizzazione delle prestazioni dei backup completi e differenziali
La procedura per la creazione di un backup completo o differenziale prevede i passaggi seguenti:
Copia dei dati dai file del database nei dispositivi di backup.
Copia della parte del log delle transazioni necessaria per il rollforward del database in uno stato consistente con gli stessi dispositivi 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 nei dispositivi di backup.
I file di database utilizzati per l'archiviazione del database vengono ordinati da un dispositivo disco e a ogni dispositivo viene assegnato un thread di lettura. Il thread di lettura legge i dati dai file di database. A ogni dispositivo di backup viene assegnato un thread di scrittura. Il thread di scrittura registra i dati nel dispositivo 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 dei dispositivi di backup utilizzati.
In genere il collo di bottiglia è rappresentato dai file di database o dai dispositivi di backup. Se la velocità effettiva totale di lettura è maggiore della velocità effettiva totale del dispositivo di backup, il collo di bottiglia sarà presente a livello del dispositivo di backup. L'utilizzo di dispositivi di backup aggiuntivi, 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 dispositivi oppure utilizzando dischi aggiuntivi in un dispositivo 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 nei dispositivi 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 dispositivo di backup viene assegnato un thread di scrittura. L'aggiunta di dispositivi di backup determina un miglioramento delle prestazioni.
Il collo di bottiglia può essere rappresentato dal dispositivo disco contenente i file del log delle transazioni o dal dispositivo di backup, a seconda della velocità relativa del dispositivo e del numero di dispositivi di backup utilizzati. L'aggiunta di dispositivi di backup comporta miglioramenti lineari delle prestazioni fino al raggiungimento della capacità massima del dispositivo disco contenente i file del log delle transazioni. Ulteriori miglioramenti sono possibili solo aumentando la velocità dei dispositivi 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:
Creazione dei file di database e dei file del log delle transazioni (se non esistono già).
Copia dei dati dai dispositivi di backup ai file di database.
Copia del log delle transazioni dai file del log delle transazioni.
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:
Copia dei dati dai dispositivi di backup al file del log delle transazioni.
Rollforward del log delle transazioni.
La procedura per il ripristino di un file di database prevede due passaggi:
Creazione di eventuali file di database mancanti.
Copia dei dati dai dispositivi 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 al dispositivo disco e a ogni dispositivo 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 e versioni successive 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, Windows Server 2003 o successivi.
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 del dispositivo di backup su nastro
Le prestazioni del dispositivo di backup su nastro sono influenzate da numerose variabili, che consentono un miglioramento pressoché lineare delle prestazioni di SQL Server con l'aggiunta di dispositivi nastro:
Dimensioni dei blocchi di dati software
Numero di dispositivi nastro che condividono un bus SCSI (Small Computer System Interface)
Tipo di dispositivo 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 dispositivi 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 dei dispositivi di backup su disco
La velocità di I/O nominale del dispositivo di backup su disco influisce sulle prestazioni del dispositivo 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 un dispositivo 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 del dispositivo.
Se il dispositivo di backup è caratterizzato da uno striping intensivo, per cui la velocità massima di scrittura nel dispositivo di backup supera in misura significativa la velocità di aggiunta di dati a un file, può risultare opportuno includere più dispositivi di backup logici 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 dispositivo di backup in un dispositivo disco distinto.
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).
Compressione dei backup
Per impostazione predefinita, l'esecuzione di backup mediante la compressione dei backup aumenta in modo significativo l'utilizzo della CPU e la CPU aggiuntiva utilizzata dal processo di compressione può avere un impatto negativo sulle operazioni simultanee. È pertanto possibile creare un backup compresso con priorità bassa in una sessione con utilizzo della CPU limitato da Resource Governor nel caso in cui si verifichi un conflitto di CPU. Per ulteriori informazioni, vedere Procedura: Utilizzo di Resource Governor per limitare l'utilizzo della CPU da parte della compressione dei backup (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, è 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 del log shipping
Quando si tenta di sincronizzare una destinazione di log shipping, non è necessario utilizzare WITH STANDBY tra i passaggi RESTORE LOG.