Sicherungskomprimierung (SQL Server)

  • Artikel

Gilt für: SQL Server

In diesem Artikel wird die Komprimierung von SQL Server-Sicherungen beschrieben, einschließlich Einschränkungen und Leistungseinbußen bei der Sicherungskomprimierung, Konfiguration der Sicherungskomprimierung sowie Komprimierungsverhältnis. Die Sicherungskomprimierung wird auf folgenden Editionen von SQL Server unterstützt: Enterprise, Standard und Developer. Jede Edition von SQL Server 2008 (10.0.x) und höhere Versionen können eine komprimierte Sicherung wiederherstellen.

Vorteile

  • Da eine komprimierte Sicherung kleiner als eine unkomprimierte Sicherung derselben Daten ist, wird für das Komprimieren einer Sicherung normalerweise weniger Geräte-E/A benötigt, und daher steigt die Sicherungsgeschwindigkeit in der Regel erheblich.

    Weitere Informationen finden Sie unter Auswirkungen der Sicherungskomprimierung auf die Leistungweiter unten in diesem Artikel.

Beschränkungen

Für komprimierte Sicherungen gelten die folgenden Einschränkungen:

  • Komprimierte und nicht komprimierte Sicherungen können nicht nebeneinander in einem Mediensatz bestehen.

  • Frühere Versionen von SQL Server können komprimierte Sicherungen nicht lesen.

  • Mit „NTbackup“ erstellte Sicherungen können nicht auf demselben Band gespeichert werden wie komprimierte SQL Server-Sicherungen.

Auswirkungen der Sicherungskomprimierung auf die Leistung

Standardmäßig steigt die CPU-Nutzung durch die Komprimierung erheblich, und die bei der Komprimierung zusätzlich verbrauchten CPU-Ressourcen können sich negativ auf gleichzeitige Vorgänge auswirken. Daher ist es u. U. sinnvoll, in einer Sitzung, bei der die CPU-Nutzung durch die Ressourcenkontrolle eingeschränkt ist, komprimierte Sicherungen mit niedriger Priorität zu erstellen. Weitere Informationen finden Sie unter Einschränken der CPU-Nutzung durch die Sicherungskomprimierung mithilfe der Ressourcenkontrolle (Transact-SQL).

Ab SQL Server 2022 (16.x) können Sie die integrierte Abladung und Beschleunigung verwenden, um Sicherungen zu komprimieren und die CPU-Ressourcen für die Sicherung auszulagern.

Wenn Sie genau wissen möchten, welche Leistung die Sicherungs-E/A erbringt, können Sie die Sicherungs-E/A zu und von den Geräten beurteilen, indem Sie die folgenden Arten von Leistungsindikatoren analysieren:

  • Windows-E/A-Leistungsindikatoren, wie die Indikatoren für physische Datenträger

  • Der Leistungsindikator Mediumsdurchsatz Bytes/Sekunde des Objekts SQLServer:Sicherungsmedium

  • Der Leistungsindikator Sicherungs-/Wiederherstellungsdurchsatz/Sekunde des Objekts SQLServer:Datenbanken

Informationen zu den Windows-Indikatoren finden Sie in der Windows-Hilfe. Informationen zur Arbeit mit SQL Server-Indikatoren finden Sie unter Verwenden von SQL Server-Objekten.

Berechnen des Komprimierungsverhältnisses einer komprimierten Sicherung

Zum Berechnen des Komprimierungsverhältnisses einer Sicherung verwenden Sie die Werte für die Sicherung in den Spalten backup_size und compressed_backup_size der Verlaufstabelle backupset wie folgt:

backup_size:compressed_backup_size

So gibt ein Komprimierungsverhältnis von 3:1 beispielsweise an, dass Sie etwa 66 Prozent an Speicherplatz sparen. Für eine Abfrage in diesen Spalten können Sie die folgende Transact-SQL-Anweisung verwenden:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

Das Komprimierungsverhältnis einer komprimierten Sicherung ist abhängig von den komprimierten Daten. Das erzielte Komprimierungsverhältnis kann durch eine Reihe von Faktoren beeinflusst werden. Zu den Hauptfaktoren gehören:

  • Die Art der Daten.

    Zeichendaten lassen sich stärker komprimieren als andere Arten von Daten.

  • Die Einheitlichkeit der Daten unter den Zeilen einer Seite.

    In der Regel enthält eine Seite mehrere Zeilen, in denen ein Feld den gleichen Wert aufweist. Dieser Wert kann möglicherweise sehr stark komprimiert werden. Dagegen ist bei Datenbanken mit Zufallsdaten oder nur einer großen Zeile pro Seite die komprimierte Sicherung beinahe so groß wie eine nicht komprimierte Sicherung.

  • Verschlüsselungsstatus der Daten

    Verschlüsselte Daten lassen sich deutlich weniger komprimieren als entsprechende unverschlüsselte Daten. Wenn die Daten beispielsweise auf Spaltenebene mit Always Encrypted oder einer anderen Verschlüsselung auf Anwendungsebene verschlüsselt wurden, wird die Größe von Sicherungen durch die Komprimierung möglicherweise nicht erheblich reduziert.

    Weitere Informationen zum Komprimieren von mit TDE (Transparent Data Encryption) verschlüsselten Datenbanken finden Sie unter mit TDE.

  • Komprimierungsstatus der Datenbank.

    Falls die Datenbank bereits komprimiert ist, ändert sich ihre Größe bei einer komprimierten Sicherung unter Umständen kaum oder gar nicht.

Sicherungskomprimierung mit TDE

Ab SQL Server 2016 (13.x) ermöglicht die Einstellung MAXTRANSFERSIZE größer als 65.536 (64 KB) einen optimierten Komprimierungsalgorithmus für mittels Transparent Data Encryption TDE) verschlüsselte Datenbanken, der eine Seite entschlüsselt, komprimiert und dann wieder verschlüsselt. Wenn MAXTRANSFERSIZE nicht angegeben ist, oder wenn MAXTRANSFERSIZE = 65536 (64 KB) verwendet wird, komprimiert die Sicherungskomprimierung mit TDE-verschlüsselten Datenbanken die verschlüsselten Seiten direkt und gibt möglicherweise keine guten Komprimierungsraten aus. Weitere Informationen finden Sie unter Backup Compression for TDE-enabled Databases (Sicherungskomprimierung für TDE-fähige Datenbanken).

Ab SQL Server 2019 (15.x) CU5 muss MAXTRANSFERSIZE nicht mehr festgelegt werden, um diesen optimierten Komprimierungsalgorithmus mit TDE zu aktivieren. Wenn der Sicherungsbefehl WITH COMPRESSION angegeben ist, oder wenn die Serverkonfiguration backup compression default auf 1 festgelegt ist, wird MAXTRANSFERSIZE automatisch auf 128.000 erhöht, um den optimierten Algorithmus zu aktivieren. Wenn MAXTRANSFERSIZE für den Sicherungsbefehl mit einem Wert > 64 KB angegeben wird, wird der angegebene Wert berücksichtigt. Anders ausgedrückt: SQL Server verringert den Wert nie automatisch, sondern erhöht ihn nur. Wenn Sie eine TDE-verschlüsselte Datenbank mit MAXTRANSFERSIZE = 65536 sichern müssen, müssen Sie WITH NO_COMPRESSION angeben oder sicherstellen, dass die Serverkonfiguration backup compression default auf 0 festgelegt ist.

Weitere Informationen finden Sie unter BACKUP (Transact-SQL).

Speicherplatzzuordnung für die Sicherungsdatei

Bei komprimierten Sicherungen ist die Größe der finalen Sicherungsdatei davon abhängig, wie stark die Daten komprimiert werden können, und dies ist erst bekannt, wenn der Sicherungsvorgang abgeschlossen ist. Wenn eine Datenbank daher mithilfe einer Komprimierung gesichert wird, verwendet die Datenbank-Engine standardmäßig einen Vorabzuordnungsalgorithmus für die Sicherungsdatei. Dieser Algorithmus ordnet der Sicherungsdatei vorab einen vordefinierten Prozentsatz der Größe der Datenbank zu. Wenn während des Sicherungsvorgangs mehr Platz benötigt wird, lässt die Datenbank-Engine die Datei wachsen. Wenn die finale Größe kleiner als der reservierte Platz ist, verkleinert die Datenbank-Engine die Datei am Ende des Sicherungsvorgangs auf die tatsächliche finale Größe der Sicherung.

Verwenden Sie das Ablaufverfolgungsflag 3042, damit die Sicherungsdatei nur so viel größer werden kann, wie erforderlich, um die finale Größe zu erreichen. Durch das Ablaufverfolgungsflag 3042 wird bewirkt, dass der Sicherungsvorgang den standardmäßigen Vorabzuordnungsalgorithmus für die Sicherungskomprimierung umgeht. Dieses Ablaufverfolgungsflag ist nützlich, wenn Sie Speicherplatz einsparen müssen, indem Sie nur die tatsächliche für die komprimierte Sicherung benötigte Größe zuordnen. Dieses Ablaufverfolgungsflag kann jedoch eine leichte Leistungseinbuße (eine mögliche Verlängerung des Sicherungsvorgangs) verursachen.

Zugehörige Aufgaben

Nächste Schritte

Backup Overview (SQL Server)
Ablaufverfolgungsflags (Transact-SQL)