Meldung

DBA-Tipp: Das SQL Server Wiederherstellungsmodell – einfach erklärt
(Quelle: ASPICON GmbH)

Immer wieder gibt es Verwirrung über die verschiedenen Wiederherstellungsmodelle einer Datenbank und in welchen Fall man sie benutzen sollte.

In diesem Artikel werden wir auf die verschiedenen Modelle, ihre Vor- und Nachteile sowie die sinnvollsten Einsatzzwecke eingehen.

Einfaches Wiederherstellungsmodell (Simple Recovery Model)

Überblick

Wenn eine neue Datenbank angelegt wird, wird standardmäßig das einfache Wiederstellungsmodell vorausgewählt. Bei diesem Modell werden fertiggestellte Transaktionen direkt vom Transaktionslog abgeschnitten, sodass die Transaktionslogdatei nicht sehr groß wird. Man muss sich bei diesem Modell nur um die vollständigen 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.

Vorteil

Sie müssen sich nur um die vollständigen und eventuell noch um differenzielle Backups kümmern. Die Transaktionslogdatei verwaltet sich selbst und wird nicht sehr groß.

Nachteil

Sie können immer nur zu einem bestimmten erfolgreichen Backup zurückspringen. Auf einen Stand zwischen zwei Backups (bspw. zwischen dem Backup von Sonntag Abend und Montag Abend) können Sie nicht wiederherstellen.

Was ist zu beachten

Falls die Datenbank produktionskritisch ist und eine Wiederherstellung auch zu einem Zeitpunkt zwischen zwei Backups möglich sein soll, ist dieses Modell nicht geeignet.

Für einfache Datenbanken, bei denen man sich nur um die vollständigen Backups und evtl. noch um differenzielle Backups kümmern möchte, passt dieses Modell gut.

Empfohlener Anwendungsbereich

Das Modell eignet sich prinzipiell für jede Anwendung. Wir empfehlen Ihnen aber, das einfache Wiederherstellungsmodell für Datenbanken zu benutzen, die nicht operationskritisch und nicht größer als 10 GB sind.

Schematische Darstellung

Quelle: https://technet.microsoft.com/en-us/library/ms186289(v=sql.110).aspx

 

Vollständiges Wiederherstellungsmodell (Full Recovery Model)

Überblick

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

Vorteil

Sie können auf einen bestimmten Zeitpunkt zwischen dem ältesten und dem neusten Backup wiederherstellen. Dadurch können Sie ganz granular auf einem Zeitpunkt (bspw. Montag um 11:46 Uhr) wiederherstellen.

Ein weiterer Vorteil ist, dass im Desasterfall die Chance besteht, noch ein sogenanntes “Tail Backup” der Transaktionslogdatei zu fahren, sodass Sie die Datenbank bis kurz vor dem Crash wiederherstellen können.

Nachteil

Sie müssen unbedingt ein regelmäßiges Backup für das Transaktionslog einrichten. Ansonsten wird diese Datei immer größer und schreibt irgendwann den Speicherplatz voll.

Was ist zu beachten

Bei diesem Modell sollte unbedingt darauf geachtet werden, dass neben den vollständigen und möglicherweise differenziellen Backups auch ein Job für die Transaktionslog Backups eingerichtet ist. Falls man dies nicht macht, wird die Transaktionslog Datei mit der Zeit immer größer! Nur ein Backup der Transaktionslogdatei sorgt dafür, dass die schon getätigten Transaktionen abgeschnitten und bereinigt werden, sodass die Datei nicht zu groß wird.

Empfohlener Anwendungsbereich

Das vollständige Wiederherstellungsmodell eignet sich für produktionskritische und / oder große Datenbanken, die häufigen Transaktionen ausgesetzt sind. Dadurch gewinnen Sie die optimale Kontrolle über den Wiederherstellungszeitpunkt und sind für Desasterfälle besser gerüstet.

Schematische Darstellung

Quelle: https://technet.microsoft.com/en-us/library/ms186289(v=sql.110).aspx

 

Massenprotokolliertes Wiederherstellungsmodell
(Bulk-logged Recovery Model)

Überblick

Das massenprotokollierte Wiederherstellungsmodell ist eine Sonderform vom vollständigen Wiederherstellungsmodell. Die Besonderheit ist hierbei, dass sogenannte Bulk Transaktionen (BULK INSERT, SELECT INTO, …) nur minimal im Transaktionslog gespeichert 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ändigen Wiederherstellungsmodell im Transaktionslog gespeichert.

Vorteil

Sie bekommen einige Vorteile des vollständigen Wiederherstellungsmodells im Bereich Desaster Wiederherstellung (Taillog Backup). Bulk Operationen werden nur minimal geloggt und nehmen daher nicht so viel Platz im Transaktionslog weg.

Nachteil

Es besteht keine Möglichkeit für “point-in-time Recovery”. Außerdem können Sie nicht auf den letzten Stand zurücksichern, wenn vor dem Ausfall eine Bulk Operation gelaufen ist. Dann ist nur die Wiederherstellung zum letzten erfolgreichen Log Backup möglich.

Was ist zu beachten

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

Empfohlener Anwendungsbereich

Das Modell eignet sich insbesondere für Datawarehouse Umgebungen, bei denen hauptsächlich massenprotokollierte Transaktionen ausgeführt werden oder Datenbanken, die in regelmäßigen Abständen solchen umfangreichen Transaktionen ausgesetzt sind. Ansonsten ist das vollständige Wiederherstellungsmodell zu empfehlen.

Fazit

Welches Wiederherstellungsmodell gewählt wird, hängt stark von den Anforderungen an den tolerierbaren 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. Gerne unterstützen wir Sie bei der Backupplanung für Ihre Microsoft SQL Server Datenbanken.

Autor:   Björn Ohlrich, MCSE: Data Management and Analytics

Telefon: +49.371.909515 - 100

E-Mail: info@aspicon.de