Compressione backup (SQL Server)

  • Articolo

Si applica a:SQL Server

Questo articolo descrive la compressione dei backup di SQL Server, inclusi le restrizioni, il compromesso di prestazioni previsto dalla compressione di backup, la configurazione della compressione di backup e il rapporto di compressione. La compressione dei backup è supportata nelle edizioni di SQL Server: Developer, Standard ed Enterprise. Ogni edizione di SQL Server 2008 (10.0.x) e successiva è in grado di ripristinare un backup compresso.

Vantaggi

  • Poiché le dimensioni di un backup compresso sono minori di quelle di un backup non compresso degli stessi dati, la compressione di un backup richiede una minore quantità di I/O del dispositivo e pertanto la velocità del backup aumenta in genere in modo significativo.

    Per ulteriori informazioni, vedere Impatto sulle prestazioni della compressione di backup, più avanti in questo articolo.

Restrizioni

Ai backup compressi vengono applicate le restrizioni seguenti:

  • I backup compressi e non compressi non possono coesistere in un set di supporti.

  • Nelle versioni precedenti di SQL Server non è possibile leggere i backup compressi.

  • NTbackups non può condividere un nastro con backup compressi di SQL Server.

Impatto sulle prestazioni della compressione dei backup

Per impostazione predefinita, la compressione aumenta significativamente l'utilizzo della CPU, il che può avere un impatto negativo sulle operazioni simultanee. Potrebbe pertanto essere necessario creare backup compressi con priorità bassa in una sessione in cui l'utilizzo della CPU è limitato da Resource Governor. Per altre informazioni, vedere Usare Resource Governor per limitare l'utilizzo della CPU da parte della compressione dei backup (Transact-SQL).

A partire da SQL Server 2022 (16.x), è possibile usare loffload integrato e accelerazione per comprimere i backup e scaricare le risorse della CPU per il backup.

Per conoscere le prestazioni di I/O del backup, è possibile isolare l'I/O del backup in/da dispositivi valutando i seguenti ordinamenti di contatori delle prestazioni:

  • Contatori delle prestazioni di I/O di Windows, ad esempio i contatori del disco fisico

  • Contatore Byte/sec velocità effettiva dispositivo dell'oggetto SQLServer:Backup Device

  • Contatore Velocità effettiva backup o ripristino/sec dell'oggetto SQLServer:Databases

Per informazioni sui contatori di Windows, vedere la Guida di Windows. Per informazioni su come usare i contatori di SQL Server, vedere Utilizzare oggetti di SQL Server.

Calcolare il rapporto di compressione di un backup compresso

Per calcolare il rapporto di compressione di un backup, usare i valori per il backup nelle colonne backup_size e compressed_backup_size della tabella di cronologia backupset , come segue:

backup_size:compressed_backup_size

Una compressione 3:1, ad esempio, indica un risparmio approssimativo del 66% di spazio su disco. Per eseguire una query su queste colonne, è possibile utilizzare la seguente istruzione Transact-SQL:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

Il rapporto di compressione di un backup compresso dipende dai dati compressi. Vari fattori possono avere un impatto sul rapporto di compressione ottenuto. Di seguito vengono indicati alcuni dei fattori principali:

  • Tipo di dati.

    I dati di tipo carattere vengono compressi maggiormente rispetto ad altri tipi di dati.

  • Coerenza dei dati tra le righe in una pagina.

    In genere, se una pagina contiene molte righe in cui un campo contiene lo stesso valore, potrebbe verificarsi una significativa compressione per tale valore. Invece, per un database che contiene dati casuali o che contiene solo una grande riga per pagina, un backup compresso avrebbe pressoché le stesse dimensioni di un backup non compresso.

  • Crittografia dei dati.

    I dati crittografati vengono compressi molto meno rispetto ai dati equivalenti non crittografati. Ad esempio, se i dati sono crittografati a livello di colonna con Always Encrypted o con altra crittografia a livello di applicazione, la compressione dei backup potrebbe non ridurre significativamente le dimensioni.

    Per altre informazioni relative alla compressione di database crittografati con Transparent Data Encryption (TDE), vedere Compressione dei backup con TDE.

  • Compressione del database,

    Se il database è compresso, la compressione potrebbe ridurre di poco o non ridurre le dimensioni dei backup.

Compressione dei backup con TDE

A partire da SQL Server 2016 (13.x), impostando MAXTRANSFERSIZE su un valore maggiore di 65536 (64 KB) si abilita un algoritmo di compressione ottimizzato per i database con crittografia Transparent Data Encryption (TDE) che esegue innanzitutto la decrittografia di una pagina, la comprime e quindi ne esegue nuovamente la crittografia. Se il parametro MAXTRANSFERSIZE non viene specificato o se viene usato il valore MAXTRANSFERSIZE = 65536 (64 KB), la compressione di backup con i database con crittografia TDE comprime direttamente le pagine crittografate e potrebbe non produrre rapporti di compressione validi. Per altre informazioni, vedere Backup Compression for TDE-enabled Databases (Compressione dei backup per i database con TDE).

A partire da SQL Server 2019 (15.x) CU5, non è più necessario impostare MAXTRANSFERSIZE per abilitare questo algoritmo di compressione ottimizzato con TDE. Se viene specificato il comando di backup WITH COMPRESSION o la configurazione server Valore predefinito di compressione backup è impostata su 1, il valore MAXTRANSFERSIZE verrà automaticamente aumentato a 128 KB per abilitare l'algoritmo ottimizzato. Se MAXTRANSFERSIZE viene specificato nel comando di backup con un valore > 64K, il valore definito verrà rispettato. In altre parole, SQL Server non ridurrà mai il valore in modo automatico, ma lo aumenterà soltanto. Se è necessario eseguire il backup di un database con crittografia TDE con MAXTRANSFERSIZE = 65536, è necessario specificare WITH NO_COMPRESSION o assicurarsi che la configurazione server Valore predefinito di compressione backup sia impostata su 0.

Per altre informazioni, vedere BACKUP (Transact-SQL).

Allocazione di spazio per il file di backup

Per i backup compressi le dimensioni dei file di backup finale dipende dal livello di compressione dei dati e questo valore non è noto finché l'operazione di backup non viene completata. Per impostazione predefinita, quando si esegue il backup di un database mediante compressione, il motore di database utilizza pertanto un algoritmo di preallocazione per il file di backup. Tramite questo algoritmo viene preallocata una percentuale predefinita delle dimensioni del database per il file di backup. Se durante un'operazione di backup è necessaria una quantità di spazio maggiore, il motore di database aumenta le dimensioni del file. Se le dimensioni finali sono inferiori allo spazio allocato, al termine dell'operazione di backup il motore di database compatta il file in base alle dimensioni finali effettive del backup.

Per consentire l'aumento del file di backup solo di quanto necessario per raggiungere le relative dimensioni finali, utilizzare il flag di traccia 3042. Il flag di traccia 3042 fa in modo che durante l'operazione di backup venga ignorato l'algoritmo di preallocazione di compressione di backup predefinito. Questo flag di traccia è utile se è necessario risparmiare spazio allocando solo le dimensioni effettive necessarie per il backup compresso. L'utilizzo di questo flag di traccia può tuttavia comportare un effetto leggermente negativo sulle prestazioni, vale a dire un possibile aumento della durata dell'operazione di backup.

Attività correlate

Passaggi successivi

Panoramica del backup (SQL Server)
Flag di traccia (Transact-SQL)