Daniel Snellen

Bereits bei der Erstellung eines DWH sollte man sich auch Gedanken über die richtige Backup Strategie machen, da diese sonst auch schnell vergessen werden kann.

Ein falsches bzw. nicht konfiguriertes Backup kann dazu führen, dass das Transaction Log die Festplatte vollständig  beschreibt und die betroffene Datenbank nicht mehr verfügbar ist. 
Aus diesem Grund werde ich hier den Zusammenhang zwischen den Transaction Log und dem Recovery Model der Datenbank beschreiben.

Funktion des Transaktion Log

Das Transaktion Log dient zur Ausfallsicherheit. Abhängig vom Recovery Model verhält sich das Transaction Log unterschiedlich.
Zuerst werde ich die allgemeine Funktion des Transaktion Log beschreiben, später im Abschnitt Recovery Model werde ich dann auf die Besonderheiten eingehen.

Das Transaction Log tritt nur bei Schreibvorgängen in Aktion, z.B. insert, update usw.

Nehmen wir eine Insert Transaktion.
Diese Transaktion wird vom SQL Server angenommen. Bevor er diese in die Datenbank übernimmt, wird sie in den Arbeitsspeicher (RAM) und in das Transaction Log geschrieben.
Dieser Vorgang geschieht gleichzeitig und hat das Ziel die Wartezeit für den Benutzer/Prozess zu reduzieren.

Sollte nun z.B. ein Stromausfall eintreten, bevor die Transaktion in die Datenbank übernommen wurde, wäre der Inhalt im RAM verloren und die Datenbank inkonsistent.

Das Transaction Log stellt in diesem Fall die Ausfallsicherheit für den Arbeitsspeicher da.
Solange eine Transaction nicht in die Datenbank übernommen wurde, ist diese im Log auch noch als “aktiv” gekennzeichnet.
Sollte z.B. nach dem Stromausfall der Server wieder starten, erkennt der SQL Server, dass es noch nicht abgeschlossene Transaktionen im Log gibt und überführt diese zuerst in die Datenbank bevor diese wieder erreichbar ist.

Im Normalbetrieb werden die im Arbeitsspeicher abgelegten Transaktionen sobald wie möglich in die Datenbank übernommen; je nach Auslastung des SQL Servers kann es jedoch auch einige Zeit dauern.
Eine genaue Zeitangabe kann jedoch nicht gemacht werden.

Sobald eine Transaktion in die Datenbank übernommen wurde, wird diese aus dem Arbeitsspeicher gelöscht und im Transaction Log als “übernommen” markiert.

wird der SQL Server geplant beendet, so werden zunächst alle Transaktionen in die Datenbank übernommen und anschließend der Dienst beendet.

Wie nun mit den “fertigen” Transaktionen im Log weiter verfahren wird, das ist abhängig vom Recovery Model.

Recovery Model

Der MS SQL Server kennt drei Recovery Modele Simple, Full und Bulk-logged standardmäßig ist bei allen neuen Datenbanken Full eingestellt. Diese Einstellung für neue Datenbaken kann angepasst werden indem das Recovery Model der model-Datenbank geändert wird.
Diese Datenbank dient als Vorlage für alle neuen Datenbanken.

Die Einstellung des Recovery Model kann jederzeit auch im laufenden Betrieb geändert werden, beachten Sie jedoch, dass diese Änderung auch Auswirkung auf Ihre Backup Strategie hat und pro Datenbank eingestellt wird.

image

Simple

Diese Model ist, wie der Name schon sagt, das einfachste. Sobald eine Transaktion in die Datenbank übernommen und im Log quittiert wurde, wird diese auch im Log gelöscht.
Das bedeutet auch, dass das Log nur einen geringen Speicherplatz Festplatten benötigt.

Wenn für eine Datenbank gar kein Backup eingerichtet werden soll, muss für diese Datenbank das Recovery Model Simple ausgewählt werden, weil ansonsten das Transaction Log die Festplatte vollschreibt und der SQL Dienst gestoppt wird.

Full

Im Gegensatz zu “Simple” werden die Transaktionen nach erfolgreicher Übernahme in die Datenbank nicht gelöscht, sondern nur quittiert.
Das Transaction Log wird nur durch ein Transaction Log Backup gelöscht.

D.h., in diesem Model sollte immer ein Datenbank- und Transaction Log Backup durchgeführt werden, damit die Festplatten nicht vollgeschrieben werden.

Dieses Model hat einen großen Vorteil: Es kann zur minutengenauen Wiederherstellung der Datenbank z.B. bei einem Festplattenausfall, Fehlfunktion sowie Falscheingabe genutzt werden.
Hierfür ist jedoch notwendig, dass die Transaction Logs häufig gesichert werden. Man kann sagen, je geringer der Datenverlust sein darf, desto häufiger muss das Transaction Log gesichert werden. Um die Wiederherstellungszeit gering zu halten, sollte jedoch mindestens einmal Täglich eine Datenbanksicherung durchgeführt werden.

Bulk-logged

Das “Bulk-logged” Model ist fast identisch mit “Full”. Es unterscheidete sich nur insofern, dass Bulk Insert nur minimal protokoliert werden, so dass diese nicht einzeln wieder hergestellt werden können.

Backup Strategie

Für die richtige Backup Strategie gibt es leider keine “Out of the Box” Lösung, weil jede Datenbank andere Anforderungen hat.
Auch kein Backup zumachen kann eine Strategie sein.

Wichtig ist nur, dass das Recovery Model entsprechend an die Strategie angepasst wird.