News

DBA Tipp: Unterschiedliche SQL Server Wiederherstellungsmodelle und ihre Anwendungsbereiche

Immer wieder gibt es Verwirrung über die verschie­denen Wiederherstellungsmodelle einer Datenbank und in welchem Fall man sie benutzen sollte. Die Wahl des Wiederherstellungsmodells hängt stark von den Anforderungen an den tolerier­baren Verlustzeitraum ab. 

Grundsätzlich unter­scheidet man im Microsoft SQL Server Bereich drei Modelle:

  • Das einfache Wiederstellungsmodell
  • Das vollständige Wiederherstellungsmodell
  • Das massen­pro­to­kol­lierte Wiederherstellungsmodell


In diesem Artikel werden wir auf die verschie­denen Modelle, ihre Anwendungsbereiche und Besonderheiten sowie die Vor- und Nachteile eingehen.

Einfaches Wiederherstellungsmodell

Wird eine neue Datenbank angelegt, ist standard­mäßig das einfache Wiederstellungsmodell (Simple Recovery Model) vorausge­wählt. Bei diesem Modell werden fertig­ge­stellte Transaktionen direkt vom Transaktionslog abgeschnitten, sodass die entspre­chende Logdatei nicht sehr groß wird. Du musst dich bei diesem Modell nur um die vollstän­digen Full- und (wenn nötig) um die Differential-Backups der Datenbank kümmern. Ein Backup der Transaktionslogdatei und damit eine Wiederherstellung zu einem bestimmten Zeitpunkt ist nicht möglich. 

Für einfache, nicht opera­ti­ons­kri­tische Datenbanken geeignet

Das Modell eignet sich vor allem für einfache Datenbanken, bei denen man sich nur um die vollstän­digen Backups und evtl. noch um diffe­ren­zielle Backups kümmern möchte. Wir empfehlen dir aber, das einfache Wiederherstellungsmodell für Datenbanken zu benutzen, die nicht opera­ti­ons­kri­tisch und nicht größer als 10 GB sind.

Falls die Datenbank produk­ti­ons­kri­tisch ist und eine Wiederherstellung auch zu einem Zeitpunkt zwischen zwei Backups möglich sein soll, ist dieses Modell nicht geeignet.

Vorteile:

  • Du musst dich nur um die vollstän­digen und eventuell noch um diffe­ren­zielle Backups kümmern.
  • Die Transaktionslogdatei verwaltet sich selbst und wird nicht sehr groß.

Nachteile:

  • Du kannst immer nur zu einem bestimmten erfolg­reichen Backup zurückspringen.
  • Auf einen Stand zwischen zwei Backups (bspw. zwischen dem Backup von Sonntag Abend und Montag Abend) kannst du nicht wiederherstellen.

Vollständiges Wiederherstellungsmodell

Das vollständige Wiederherstellungsmodell (Full Recovery Model)​ wird vornehmlich für größere, produktive Datenbanken genutzt. In dieser Konfiguration schreibt der SQL Server alle Transaktionen in ein Transaktionslog, das üblicher­weise aus einer Transaktionslogdatei besteht. Das Schreiben aller Transaktionen in eine Logdatei bietet den großen Vorteil, dass die Wiederherstellung (bei vorhan­denen Backups der Logdateien) der Datenbank auf nahezu jeden Zeitpunkt möglich ist (point-in-time recovery). 

Für große, produk­ti­ons­kri­tische Datenbanken geeignet

Das vollständige Wiederherstellungsmodell eignet sich für produk­ti­ons­kri­tische und / oder große Datenbanken, die häufigen Transaktionen ausge­setzt sind. Dadurch gewinnen Sie die optimale Kontrolle über den Wiederherstellungszeitpunkt und sind für Desasterfälle besser gerüstet.

Bei diesem Modell sollte unbedingt darauf geachtet werden, dass neben den vollstän­digen und mögli­cher­weise diffe­ren­zi­ellen Backups auch ein Job für die Transaktionslog-Backups einge­richtet ist. Ansonsten wird die Transaktionslogdatei mit der Zeit immer größer. Nur ein Backup der Logdatei sorgt dafür, dass die schon getätigten Transaktionen abgeschnitten und bereinigt werden, sodass die Datei nicht zu groß wird.

Vorteile:

  • Du kannst auf einen bestimmten Zeitpunkt zwischen dem ältesten und dem neusten Backup und damit ganz granular wiederherstellen.
  • Im Desasterfall besteht die Chance, noch ein sogenanntes “Tail Backup” der Transaktionslogdatei zu fahren, sodass du die Datenbank bis kurz vor dem Crash wieder­her­stellen kannst.

Nachteile:

  • Du solltest unbedingt ein regel­mä­ßiges Backup für das Transaktionslog einrichten. Ansonsten wird diese Datei immer größer und schreibt irgendwann den Speicherplatz voll.

Massenprotokolliertes Wiederherstellungsmodell

Das massen­pro­to­kol­lierte Wiederherstellungsmodell (Bulk-logged Recovery Model)​ ist eine Sonderform vom vollstän­digen Wiederherstellungsmodell. Die Besonderheit ist hierbei, dass sogenannte Bulk Transaktionen (BULK INSERT, SELECT INTO, …) nur minimal im Transaktionslog gespei­chert werden. Im Full Recovery Modus hingegen werden alle Transaktionen im Log hinterlegt. Dadurch wird die Transaktionslogdatei nicht so groß, wenn zum Beispiel nachts Importe laufen. Ansonsten werden alle normalen Transaktionen genau wie im vollstän­digen Wiederherstellungsmodell im Transaktionslog gespeichert. 

Besonders geeignet für Datawarehouse Umgebungen

Das Modell eignet sich insbe­sondere für Datawarehouse Umgebungen, bei denen haupt­sächlich massen­pro­to­kol­lierte Transaktionen ausge­führt werden oder Datenbanken, die in regel­mä­ßigen Abständen solchen umfang­reichen Transaktionen ausge­setzt sind. Ansonsten ist das vollständige Wiederherstellungsmodell zu empfehlen.

Falls du auf die Möglichkeit Wert legst, auf einen bestimmten Zeitpunkt zurück zu gehen, ist dieses Modell nicht geeignet, da es kein “point-in-time recovery” unterstützt.

Vorteile:

  • Du bekommst einige Vorteile des vollstän­digen Wiederherstellungsmodells im Bereich Desaster Wiederherstellung (Taillog Backup).
  • Bulk Operationen werden nur minimal geloggt und nehmen daher nicht so viel Platz im Transaktionslog weg.

Nachteile:

  • Es besteht keine Möglichkeit für “point-in-time Recovery”.
  • Du kannst nicht auf den letzten Stand zurück­si­chern, wenn vor dem Ausfall eine Bulk Operation gelaufen ist. Dann ist nur die Wiederherstellung zum letzten erfolg­reichen Log Backup möglich.

Fazit

Welches Wiederherstellungsmodell gewählt wird, hängt stark von den Anforderungen an den tolerier­baren Verlustzeitraum von Datenbankeinträgen und der Art der Befüllung der Datenbank ab. Bei jedem Modell gibt es einige Dinge in der Konfiguration zu beachten. Eine schema­tische Darstellung findest du übrigens hier.

Du hast weitere Fragen? Gerne unter­stützen wir dich bei der Backupplanung für deine Microsoft SQL Server Datenbanken.

Hier findest du weitere inter­es­sante DBA Tipps oder Posts zum Thema SQL Server aus unserem News und Insights Bereich.

icon-arrow_right_medium-violet-blue.svg

Share this article

Facebook 
Twitter 
LinkedIn 
XING 
WhatsApp 
Email