Delen via

Back-upcompressie (SQL Server)

  • Artikel

Van toepassing op:SQL Server

In dit artikel wordt de compressie van BACK-ups van SQL Server beschreven, waaronder beperkingen, de prestaties van het comprimeren van back-ups, de configuratie van back-upcompressie en de compressieverhouding. Back-upcompressie wordt ondersteund in SQL Server-edities: Enterprise, Standard en Developer. Elke editie van SQL Server 2008 (10.0.x) en hoger kan een gecomprimeerde back-up herstellen.

Voordelen

  • Omdat een gecomprimeerde back-up kleiner is dan een niet-gecomprimeerde back-up van dezelfde gegevens, vereist het comprimeren van een back-up doorgaans minder I/O van apparaten en verhoogt daarom meestal de back-upsnelheid aanzienlijk.

    Zie Prestatie-impact van het comprimeren van back-ups verderop in dit artikel voor meer informatie.

Beperkingen

De volgende beperkingen gelden voor gecomprimeerde back-ups:

  • Gecomprimeerde en niet-gecomprimeerde back-ups kunnen niet naast elkaar bestaan in een mediaset.

  • Eerdere versies van SQL Server kunnen gecomprimeerde back-ups niet lezen.

  • NTbackups kunnen een tape niet delen met gecomprimeerde SQL Server-back-ups.

ZSTD-compressiealgoritmen geïntroduceerd in SQL Server 2025

Vanaf SQL Server 2025 (17.x) Preview is een nieuw compressie-algoritme, ZSTD, beschikbaar voor back-upcompressie. Dit algoritme is sneller en effectiever dan het vorige MS_XPRESS algoritme.

U kunt het ZSTD-algoritme gebruiken voor back-upcompressie op een van de volgende manieren:

  • Door de WITH COMPRESSION (ALGORITHM = ZSTD) optie op te geven in de opdracht BACKUP Transact-SQL voor een specifieke back-up.
  • Door de configuratieoptie voor het back-upcompressie-algoritme in te stellen op 3. Met deze optie stelt u het standaard algoritme voor back-upcompressie in op ZSTD voor alle back-ups die gebruikmaken van de WITH COMPRESSION optie.

Opmerking

Het ZSTD-algoritme is momenteel in preview en is alleen beschikbaar in SQL Server 2025 (17.x) Preview.

Invloed van de prestaties van het comprimeren van back-ups

Standaard verhoogt compressie het CPU-gebruik aanzienlijk en kan het extra CPU-verbruik dat door het compressieproces wordt verbruikt, een negatieve invloed hebben op gelijktijdige bewerkingen. Daarom kunt u gecomprimeerde back-ups met lage prioriteit maken in een sessie waarvan het CPU-gebruik wordt beperkt door Resource Governor. Voor meer informatie, zie Resource Governor gebruiken om het CPU-gebruik door back-upcompressie (Transact-SQL) te beperken.

Vanaf SQL Server 2022 (16.x) kunt u geïntegreerde offloading en versnelling gebruiken om back-ups te comprimeren en de CPU-resources voor de back-up te offloaden.

Als u een goed beeld wilt krijgen van de I/O-prestaties van uw back-up, kunt u de back-up-I/O naar of van apparaten isoleren door de volgende soorten prestatiecounters te evalueren:

Zie De Help van Windows voor informatie over Windows-tellers. Zie SQL Server-objecten gebruiken voor informatie over het werken met SQL Server-tellers.

De compressieverhouding van een gecomprimeerde back-up berekenen

Als u de compressieverhouding van een back-up wilt berekenen, gebruikt u de waarden voor de back-up in de backup_size en compressed_backup_size kolommen van de back-upgeschiedenistabel als volgt:

backup_size:compressed_backup_size

Een compressieverhouding van 3:1 geeft bijvoorbeeld aan dat u ongeveer 66% op schijfruimte bespaart. Als u query's wilt uitvoeren op deze kolommen, kunt u de volgende Transact-SQL-instructie gebruiken:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

De compressieverhouding van een gecomprimeerde back-up is afhankelijk van de gegevens die zijn gecomprimeerd. Een verscheidenheid aan factoren kan van invloed zijn op de verkregen compressieverhouding. Belangrijke factoren zijn:

  • Het type gegevens.

    Tekengegevens worden meer gecomprimeerd dan andere typen gegevens.

  • De consistentie van de gegevens tussen rijen op een pagina.

    Als een pagina meestal meerdere rijen bevat waarin een veld dezelfde waarde bevat, kan een significante compressie voor die waarde optreden. In tegenstelling tot een database die willekeurige gegevens bevat of die slechts één grote rij per pagina bevat, zou een gecomprimeerde back-up bijna net zo groot zijn als een niet-gecomprimeerde back-up.

  • Of de gegevens zijn versleuteld

    Versleutelde gegevens comprimeren aanzienlijk minder goed dan equivalente niet-versleutelde gegevens. Als gegevens bijvoorbeeld worden versleuteld op kolomniveau met Always Encrypted of met andere versleuteling op toepassingsniveau, kan het comprimeren van back-ups de grootte niet aanzienlijk verminderen.

    Zie Back-upcompressie met TDE voor meer informatie over het comprimeren van databases die zijn versleuteld met TDE (Transparent Data Encryption).

  • Of de database wordt gecomprimeerd.

    Als de database is gecomprimeerd, kan het comprimeren van back-ups de grootte ervan wellicht niet veel verkleinen, als het überhaupt iets doet.

Back-upcompressie met TDE

Vanaf SQL Server 2016 (13.x) maakt het instellen van MAXTRANSFERSIZEgroter dan 65536 (64 kB) een geoptimaliseerd compressie-algoritme mogelijk voor Transparent Data Encryption (TDE) versleutelde databases die eerst een pagina ontsleutelen, comprimeren en vervolgens opnieuw versleutelen. Als MAXTRANSFERSIZE niet is opgegeven of als MAXTRANSFERSIZE = 65536 (64 kB) wordt gebruikt, comprimeert de back-upcompressie bij TDE-versleutelde databases de versleutelde pagina's direct, wat mogelijk geen goede compressieverhoudingen oplevert. Zie Back-upcompressie voor databases met TDE-functionaliteitvoor meer informatie.

Vanaf SQL Server 2019 (15.x) CU5 is het instellen van MAXTRANSFERSIZE niet meer vereist om dit geoptimaliseerde compressie-algoritme met TDE in te schakelen. Als de back-upopdracht is opgegeven WITH COMPRESSION of de standaardserverconfiguratie voor back-upcompressie is ingesteld op 1, MAXTRANSFERSIZE wordt automatisch verhoogd naar 128K om het geoptimaliseerde algoritme in te schakelen. Als MAXTRANSFERSIZE is opgegeven in de back-upopdracht met een waarde > 64K, wordt de opgegeven waarde gehonoreerd. Met andere woorden, SQL Server vermindert nooit automatisch de waarde, maar verhoogt deze alleen. Als u een back-up wilt maken van een met TDE versleutelde database met MAXTRANSFERSIZE = 65536, moet u WITH NO_COMPRESSION opgeven of ervoor zorgen dat de standaardconfiguratie van back-upcompressie is ingesteld serverconfiguratie is ingesteld op 0.

Zie BACKUP (Transact-SQL)voor meer informatie.

Toewijzing van ruimte voor het back-upbestand

Voor gecomprimeerde back-ups is de grootte van het uiteindelijke back-upbestand afhankelijk van hoe comprimeerbaar de gegevens zijn en dit is onbekend voordat de back-upbewerking is voltooid. Bij het maken van een back-up van een database met behulp van compressie gebruikt de database-engine daarom standaard een algoritme voor vooraflocatie voor het back-upbestand. Met dit algoritme wordt een vooraf gedefinieerd percentage van de grootte van de database voor het back-upbestand toegewezen. Als er tijdens de back-upbewerking meer ruimte nodig is, groeit de database-engine het bestand. Als de uiteindelijke grootte kleiner is dan de toegewezen ruimte, verkleint de database-engine het bestand aan het einde van de back-upbewerking tot de werkelijke uiteindelijke grootte van de back-up.

Gebruik traceringsvlag 3042 om het back-upbestand alleen te laten groeien als dat nodig is om de uiteindelijke grootte te bereiken. Traceringsvlag 3042 zorgt ervoor dat de back-upbewerking het standaardalgoritme voor back-upcompressie overslaat. Deze traceringsvlag is handig als u ruimte wilt besparen door alleen de werkelijke grootte toe te wijzen die nodig is voor de gecomprimeerde back-up. Het gebruik van deze traceringsvlag kan echter een lichte prestatiestraf veroorzaken (een mogelijke toename van de duur van de back-upbewerking).

Gerelateerde taken

Volgende stappen

Overzicht van back-up (SQL Server)
traceringsvlaggen (Transact-SQL)