News
DBA-Tipp: Microsoft SQL Server Backupkomprimierung zur Verkleinerung der Backups

Die Information ist ein schnelllebiges Gut. Jeden Tag werden wir mit hunderten Informationen zugemüllt. Deshalb sind wir bestrebt uns auf das Wesentliche zu konzentrieren und nur substantiell nachhaltige Informationen bereitzustellen.

Icon Unternehmen

Regelmäßige Backups gehören zu den wichtigsten Maßnahmen, um zu verhindern, dass unternehmenskritische Daten verloren gehen - sei es durch den Ausfall einer Platte mit einigen Datenbankdateien oder aber auch durch das unbeabsichtigte Löschen/Überschreiben einiger Daten/Tabellen.

Backups großer Datenbanken sind ebenfalls groß und können das vorgesehene Backup-Zeitfenster schnell sprengen. 

Der Microsoft SQL Server bietet seit der Version 2008 die Möglichkeit einer Backupkomprimierung an. Während anfangs die Enterprise Edition benötigt wurde, ist diese Funktion seit der Version 2008 R2 auch in der Standard Edition enthalten.

Alternativ kann für großen Tabellen/Indizes auch die Tabellenkomprimierung verwendet werden, um den Speicherbedarf der Datenbank und schlussendlich der Backups zu senken. Bis zum SQL Server 2016 RTM war hierfür die Enterprise Edition obligatorisch. Seit dem SQL Server 2016 SP1 wird die Tabellenkomprimierung auch in der Standard-Edition unterstützt.

Um die Backupkomprimierung der Instanz standardmäßig zu verwenden, muss lediglich der Haken bei „Compress Backup“ unter „Database Settings“ in den Eigenschaften der Instanz gesetzt werden. 

 

Alternativ kann die Sicherung auch für jedes Backup einzeln konfiguriert werden. Dafür wird einfach die gewünschte Option im Dropdown „Set backup compression“ im Wizard „Back Up Database“ unter „Backup Options“ ausgewählt.

 

Das Einsparpotential kann über die folgende Abfrage ermittelt werden:

SELECT bset.database_name
 , CASE bset.type  
      WHEN 'D' THEN 'Database'
      WHEN 'L' THEN 'Log'
      WHEN 'I' THEN 'Differential'
   END AS backup_type
  , CONVERT(DATETIME2(0), bset.backup_start_date)
    AS backup_start_date
  , CONVERT(DATETIME2(0), bset.backup_finish_date)
    AS backup_finish_date
  , DATEDIFF (s, bset.backup_start_date, bset.backup_finish_date)
    AS 'Time To Backup (sec)'
  , ROUND(CONVERT(FLOAT, bset.compressed_backup_size/1024/1024), 2)
    AS 'Compr. Size (MB)'
  , ROUND(CONVERT(FLOAT, bset.backup_size/1024/1024), 2)
    AS 'Uncompr. Size (MB)'
  , CONVERT(VARCHAR(255),
      ROUND(
   100 * CONVERT (FLOAT, bset.compressed_backup_size) / CONVERT
   (FLOAT, bset.backup_size)
        , 2
 )
    ) + '%' AS 'Compr. Ratio'
FROM msdb.dbo.backupset AS bset
WHERE database_name = 'AdventureWorks2012'
ORDER BY
   bset.database_name,
   bset.backup_finish_date

Es erscheint nachfolgende Ausgabe:

 

Falls eine zu sichernde Datenbank allerdings mittels Transparent Data Encryption (TDE) verschlüsselt wurde, wird der Speicherplatz durch die Backupkomprimierung bis einschließlich SQL-Server 2014 nicht gesenkt. 

Erst ab SQL Server 2016 wird die Komprimierung der Backups von TDE-verschlüsselten Datenbanken unterstützt. Dabei ist zu beachten, dass der Parameter MAXTRANSFERSIZE auf mindestens 65537 gesetzt werden muss. Dieser kann wie über T-SQL wie folgt gesetzt werden.

BACKUP DATABASE [AdventureWorks2012]
  TO  DISK = N'\\BackupSrv\MSSQL\AW_compressed.bak'
    WITH
      COMPRESSION,
      MAXTRANSFERSIZE = 65537
GO

Fazit:
Eine Komprimierung der Backups ist in vielen Fällen lohnenswert, wenn der zur Verfügung stehende Speicherplatz begrenzt ist. Unabhängig davon sollten Backupgröße und -dauer regelmäßig überprüft werden, um etwaige Probleme vermeiden zu können. Gern stehen Ihnen unsere Microsoft SQL Server Spezialisten zur Verfügung. Zögern Sie nicht, sich gleich mit uns in Verbindung zu setzen.