SQL Server: Wie Datenablage, Backup und Recovery funktionieren

oder: Warum läuft die Log-Datei voll?

Die Datenablage ist eine der wichtigsten Funktionen eines Datenbankservers: Nicht nur sollen die Daten zuverlässig auf der Festplatte landen, sondern das soll auch noch möglichst schnell geschehen. Und schließlich sollen die Daten meist nicht nur auf der Platte stehen, sondern in regelmäßigen Abständen zur Datensicherung auch auf ein Band oder ein anderes externes Medium kopiert werden.

Microsofts SQL Server setzt hierfür einige Mechanismen ein, die sich schon seit mehr als einem Dutzend Jahren nicht prinzipiell verändert haben. Trotzdem sind diese Grundlagen den meisten Admins weitgehend unbekannt. Dieser Artikel gibt ein wenig Überblick.

Das Protokoll-Prinzip

Mit vielen anderen Datenbanksystemen teilt sich SQL Server das Prinzip des “Write-ahead logging”, um die Konsistenz seiner Transaktionen auch über mögliche Systemausfälle hinweg zu gewährleisten. Hinter diesem hochtrabenden Namen verbirgt sich eine recht einfache Idee: SQL Server bestätigt die Annahme von Daten von einem Client erst dann, wenn er sie sicher auf die Festplatte geschrieben hat. Dabei bedient er sich allerdings noch eines – ebenfalls weit verbreiteten – Tricks: Der Server schreibt die Daten nicht sofort in die Datenbank-Datei. Solche Dateien können nämlich sehr groß werden, und sie sind intern in einer Weise organisiert, die insbesondere effiziente Lese- und Suchvorgänge unterstützt. Im Effekt muss der Datenbankserver daher die Daten innerhalb einer möglicherweise riesigen Datei an einer ganz bestimmten Stelle speichern, die er zunächst identifizieren muss, und gleich darauf muss er an eine ganz andere Stelle schreiben, um das nächste Datenpaket loszuwerden. Und das alles nur, um Daten zu speichern, die im nächsten Moment vielleicht schon wieder geändert sind.

Aus diesem Grund versuchen die meisten Datenbanksysteme, den Schreibzugriff auf die Datenbank-Datei so selten wie möglich auszuführen. Sie fassen mehrere einzelne Schreibvorgänge zu einem größeren zusammen, wobei sich oft auch der Effekt ergibt, dass Daten, die bereits von anderen Daten ersetzt sind, nur einmal und nicht mehrfach geschrieben werden müssen. Dieses Prinzip nennt man auch “Lazy Writer”.

Damit die Daten nun also einerseits sofort nicht-flüchtig auf der Platte stehen, andererseits aber der Zugriff auf die komplexe Datenbankdatei so selten wie möglich erfolgt, haben Datenbanken dieser Art eine zweite Datei, nämlich die Log-Datei (auch: Transaktionsprotokoll-Datei). Dort protokolliert der Server jede einzelne Transaktion ohne weitere Rücksicht auf spätere Lesevorgänge. Diese Log-Datei lässt sich also einfach sequenziell beschreiben: Alle Transaktionen protokolliert das System vollständig, indem es sie – vereinfacht gesagt – am Ende der Log-Datei anfügt.

Tatsächlich sind es also zwei Dateien bzw. zwei Sätze von Dateien, die eine derartige Datenbank zur Speicherung benötigt. Dabei wird deutlich, dass die Log-Datei ganz entscheidend über die Geschwindigkeit der Transaktionsverarbeitung (also aller Änderungen an der Datenbank) entscheidet, denn jede Änderung wird erst in der Datei protokolliert und dann bestätigt. Hochdynamische Datenbanken brauchen daher für die Log-Dateien ein Plattensystem, das eine hohe Verarbeitungsgeschwindigkeit für sequenzielle Schreibvorgänge ermöglicht. Es ist durchaus keine Seltenheit, dass man dafür Stripe-Sets aus kleinen, schnellen Platten einsetzt (gern gespiegelt – also z.B. RAID 10). Das ist allerdings längst nicht für jede Datenbank nötig, sondern nur für solche, die richtig hohe Schreibleistung brauchen. Die meisten Datenbanken für “übliche” Anwendungen (auch ERP-Systeme im Mittelstand) werden mit weniger I/O-Leistung auskommen und daher auf “normalen” Plattensystemen zufriedenstellend arbeiten.

Wenn man das Logging richtig behandelt (mehr dazu siehe unten), werden die Log-Dateien für die meisten Datenbanken im Vergleich zur vollständigen Datenbankgröße eher überschaubar sein. Es kann aber durchaus vorkommen, dass die Protokolldateien plötzlich stark anwachsen, und zwar dann, wenn es viele oder sehr große Transaktionen in kurzer Zeit gibt. Eine typische Situation dieser Art sind umfangreiche Import-Vorgänge in die Datenbank – jeder importierte Datensatz wird natürlich einzeln protokolliert.

Auf jeden Fall aber sind die Transaktionsprotokolle produktionskritisch: Ohne Protokoll keine Veränderung in der Datenbank. Als Hintergrund halte man sich den Sinn des Transaktionsprotokolls vor Augen: Es ist der ausschlaggebende Mechanismus, der gewährleistet, dass man sich auf die Datenbank verlassen kann. In einer komplexen Transaktion, die aus vielen einzelnen, aber zusammenhängenden Änderungen besteht, muss die Applikation gewährleisten, dass der Vorgang entweder vollständig oder aber gar nicht stattfindet. Man stelle sich vor, bei einer Bank-Überweisung, die aus den beiden Schritten “Abbuchen von 100.000 Euro von Konto X und Gutschreiben derselben Summe auf Konto Y” besteht, findet nur der erste Schritt statt, der zweite aber nicht. Die Bank freut sich – aber gleich zwei Kunden haben Grund zur Klage. Das Datenbanksystem muss also sicherstellen, dass eine unterbrochene Transaktion vollständig widerrufen wird, damit sie sauber neu ausgeführt werden kann. Umgekehrt muss eine Transaktion, die das System bestätigt hat, auch zuverlässig in der Datenbank vermerkt sein.

Daher kann bei einem transaktionsgeschützten System kein Vorgang ohne Protokoll stattfinden. Steht das Protokoll nicht zur Verfügung, ist der Server also gezwungen, die ganze Datenbank offline zu nehmen. Der korrekte Umgang mit den Log-Dateien ist also eine Kernaufgabe des Datenbank-Operating.

Wie SQL Server Änderungen verarbeitet

Hier noch einmal eine kurze (und vereinfachte) Zusammenfassung, wie SQL Server Änderungen an einer Datenbank verarbeitet.

1. Ein Client fordert eine bestimmte Änderung von Daten an.
2. SQL Server prüft, ob sich die betroffenen Daten (genauer: Die Speicherbereiche der Datenbank, auf denen sich die Daten befinden – auch als “Datenbank-Seiten” bezeichnet) bereits im Arbeitsspeicher befinden. Falls nicht, lädt er die Seiten ins RAM.
3. SQL Server protokolliert die Änderungen im Transaktionsprotokoll.
4. SQL ändert die Datenbank-Seiten im Arbeitsspeicher.
5. SQL Server gibt dem Client die Bestätigung über die Transaktion.
6. Beim nächsten sog. Checkpoint-Vorgang schreibt SQL Server die geänderten Datenbank-Seiten aus dem Arbeitsspeicher in die Datenbankdatei auf der Festplatte. Den Checkpoint vermerkt das System im Transaktionsprotokoll: Alle Transaktionen vor dem Checkpoint sind in der Datenbank fixiert, die betreffenden Transaktionen sind im Protokoll also nicht mehr aktiv.

Was SQL Server nun als nächstes mit dem Transaktionsprotokoll tut, hängt davon ab, wie die Datenbank konfiguriert ist. Entscheidend ist dabei das “Recovery Model” (Wiederherstellungsmodell), das als Datenbankoption durch den Administrator konfigurierbar ist. Steht es auf “Vollständig” (Full), so lässt SQL Server alle inaktiven Transaktionen im Transaktionsprotokoll stehen und schreibt neue Transaktionen ans Ende der Protokolldatei. Erst wenn der Admin eine Protokollsicherung (Log Backup) ausführt, gibt SQL Server die “alten”, inaktiven Transaktionen frei, und das System kann den Platz innerhalb der Datei wieder nutzen. Der Vorteil: Auf diese Weise lassen sich die Transaktionsprotokolle auch zur Wiederherstellung nach einem Datenverlust einsetzen, denn sie enthalten ja sämtliche Änderungen am Datenbestand (siehe unten). Der Nachteil: Sorgt man nicht für ein regelmäßiges Backup der Logs, so wachsen diese endlos an, bis die Platte voll ist. Als Folge bleibt dann die ganze Datenbank stehen – denn ohne Protokoll kein Zugriff.

Im Modell “Einfach” (Simple) hingegen gibt SQL Server den Platz innerhalb der Datei bei jedem Checkpoint wieder frei. Dadurch kann die Datei nicht unkontrolliert anwachsen und stellt im Normalfall kein Risiko für die Plattenbelegung und den Online-Status der Datenbank dar. Allerdings lassen sich die Transaktionsprotokolle in diesem Fall auch nicht für das Restore von Daten einsetzen, weil sie ja nicht lückenlos vorliegen.

“Recovery” und “Restore” – hier nicht dasselbe!

Eine Besonderheit bringt der SQL Server mit: Die Begriffe “Recovery” und “Restore”, die man sonst oft synonym verwendet, bezeichnen hier völlig unterschiedliche Dinge. Man sollte sie auseinanderhalten, wenn man verstehen möchte, was SQL Server in welcher Situation tut. Ungünstigerweise übersetzt die meiste deutschsprachige Dokumentation beide Begriffe mit demselben Wort “Wiederherstellung” …

Recovery: Automatisch beim Systemstart

Der “Recovery”-Vorgang läuft immer automatisch beim Systemstart ab bzw. wenn SQL Server eine Datenbank online stellen soll. In diesem Moment muss das System sicherstellen, dass die Datenbank sich in einem konsistenten Zustand befindet. Dazu überprüft der Server das Transaktionsprotokoll der Datenbank, ob sich dort nach dem letzten Checkpoint noch Transaktionen befinden. Falls ja, schreibt das System die abgeschlossenen Vorgänge (“committed transactions”) in die Datenbank. Dies nennt sich “rollforward”. Die nicht abgeschlossenen Vorgänge, die also z.B. durch einen Ausfall des Servers unterbrochen wurden (“uncommitted transactions”) entfernt der Server aus dem Protokoll. Dies nennt man “rollback”.

Erst nach dem Abschluss dieses Prozesses kann SQL Server die Datenbank online schalten und für lesende oder schreibende Zugriffe freigeben. Da der Recovery-Prozess bei großen und sehr aktiven Datenbanken durchaus eine längere Weile dauern kann, gibt es in jüngeren Programmversionen des SQL Server einige Optimierungen dazu. Das Recovery erfolgt beispielsweise auch beim Failover eines SQL-Clusterservers, wodurch die Umschaltzeit auf den Failover-Knoten manchmal unerwartet lang dauern kann.

Restore: Manuell nach Datenverlust

Der “Restore”-Prozess bezeichnet die manuelle Wiederherstellung einer SQL-Server-Datenbank nach einem Datenverlust, also das Gegenstück zum Backup. Hierzu gibt es eine Reihe von Methoden, aus denen der Admin je nach Situation die geeignete auswählen muss.

Daten sichern und wiederherstellen

Für Datensicherung und -wiederherstellung bietet SQL Server mehrere Methoden, die teilweise auf dem Transaktionsprotokoll beruhen. Alle Methoden eignen sich für das Online-Backup, ohne dass SQL Server angehalten werden muss.

Vollständiges Backup

Das vollständige Backup sichert alle Seiten (Pages) der Datenbank auf das Sicherungsmedium und liest diese dazu direkt von der Festplatte. Durch eine leistungsfähige Priorisierung sorgt der Prozess dafür, dass Änderungen an Datenbankseiten erst dann erfolgen können, wenn die Seiten bereits gesichert wurden. Das führt natürlich dazu, dass während des Sicherungsvorgangs nicht die gesamte Performance des Datenbankservers zur Verfügung steht. Trotzdem bildet die vollständige Datensicherung die Grundlage für alle Sicherungsverfahren und sollte daher regelmäßig ausgeführt werden.

Dieser Sicherungstyp lässt sich über die grafischen Verwaltungstools oder per SQL-Kommando ausführen. Die Grundsyntax zur Ad-hoc-Sicherung in eine Datei lautet:

BACKUP DATABASE NameDerDatenbank TO DISK = 'C:\Pfad\Dateiname.bak'

Differenzielles Backup

Das differenzielle Backup sichert alle Seiten der Datenbank, die sich seit dem letzten vollständigen Backup geändert haben. Der eigentliche Sicherungsprozess entspricht dem der vollständigen Sicherung. Aufgrund des Prinzips wird der Umfang der Datensicherung tendenziell um so größer, je länger das Full Backup zurückliegt.

Auch dieser Sicherungstyp lässt sich grafisch oder per Code ausführen. Die Grundsyntax ist in diesem Fall:

BACKUP DATABASE NameDerDatenbank TO DISK = 'C:\Pfad\Dateiname.bak'
WITH DIFFERENTIAL

Transaktionsprotokoll-Backup

Die Datensicherung der Transaktionsprotokolle erfolgt auf gänzlich andere Weise. Hier sichert der Server nicht die eigentlichen Datenbank-Seiten, sondern den Inhalt des Transaktionsprotokolls. Dafür gibt es drei zwingende Voraussetzungen:

1. Das Wiederherstellungsmodell der Datenbank ist “Vollständig” (oder “Massenprotokolliert”, was aber noch einige Besonderheiten nach sich zieht)
2. Es hat mindestens eine vollständige Datenbank-Sicherung stattgefunden, seit der Modus “Vollständig” aktiviert wurde
3. Die Transaktionsprotokoll-Sicherungen erfolgen in einer Reihe ohne Unterbrechung. (Sollte einmal eine Unterbrechung nötig sein – etwa weil das Log vollgelaufen war und manuell gekürzt werden musste –, so ist zunächst wieder eine vollständige Datenbank-Sicherung nötig.)

Die Transaktionsprotokoll-Sicherung entspricht dem “inkrementellen Backup” in klassischen Umgebungen, denn sie bezieht sich immer auf die vorangegangene Protokollsicherung. Je häufiger man einen solchen Sicherungsprozess ausführt, desto geringer ist tendenziell der Umfang der einzelnen Sicherung. Damit eignet sich das Verfahren auch für Strategien mit sehr hoher Frequenz, beispielsweise alle 15 Minuten.

Natürlich lässt sich auch dieses Verfahren grafisch oder per Kommando ausführen. Die Syntax ist etwas anders als bei den anderen Varianten:

BACKUP LOG NameDerDatenbank TO DISK = 'C:\Pfad\Dateiname.bak'

Wiederherstellung

Die Wiederherstellung einer Datenbank hängt bezüglich des einzusetzenden Verfahrens von zahlreichen Faktoren ab – dazu gehören natürlich die Methoden, die für das Backup genutzt wurden. Da aber auch viele andere Besonderheiten zu berücksichtigen sind, beschränken wir uns hier absichtlich auf einige grundlegende Hinweise.

Wiederherstellung eines “Full Backup”

Der einfachste Restore-Vorgang ist die Wiederherstellung von einer vollständigen Datensicherung. Dadurch setzt man die Datenbank auf den Stand zurück, den sie beim Backup hatte. Alle Daten also, die zwischen dem Backup und dem Eintritt des Schadens geändert wurden, sind damit verloren.

“Maximale” Wiederherstellung

Um mehr als nur das letzte “Full Backup” wiederherzustellen, benötigt man als Datenbasis ein differenziertes Sicherungssystem, idealerweise kombiniert aus vollständigen und protokollbasierten Sicherungen; je nach Größe der Datenbank ggf. ergänzt um differenzielle Sicherungen. Im Fall des Falles verläuft der prinzipielle Restore-Vorgang so:

1. Wiederherstellen des letzten Full Backup
2. Falls vorhanden, wiederherstellen des letzten Differenziellen Backup
3. Wiederherstellen aller Transaktionsprotokolle, die nach dem Differenziellen Backup gesichert wurden

Wichtig dabei: Schritt 3 muss lückenlos erfolgen. Wichtig ebenso: In diesem Prozess darf der sog. “Recovery-Prozess” (siehe oben) erst nach dem letzten wiederhergestellten Transaktionsprotokoll erfolgen, er muss also vorher unterdrückt werden.

Weitere Details finden sich in der Online-Hilfe des SQL Server. Die nötigen Schritte des Restore-Prozesses sollte man regelmäßig und ausführlich üben!

Ein bisschen Best Practice

Die folgenden Hinweise verstehen sich ausdrücklich nicht als feste Empfehlungen für jede Situation, sondern nur als Faustregeln für typische, überschaubare Anforderungen.

• Für eher “einfache” Datenbanken ist es durchaus sinnvoll, das Wiederherstellungsmodell auf “Einfach” zu stellen (Recovery Model “Simple”). Auf diese Weise ist sichergestellt, dass die Log-Dateien nicht überlaufen. Die nachteilige Folge ist, dass man von solchen Datenbanken nur vollständige Backups (sowie differenzielle Backups) erzeugen kann, die Transaktionsprotokolle lassen sich in diesem Zustand nicht sichern und damit auch nicht für die Wiederherstellung einsetzen. Für viele Datenbanken ist das aber auch gar nicht nötig: Rein administrative Datenbanken etwa enthalten oft nur Konfigurationsinformationen oder statistische Daten. Im Fall eines Falles wäre es hier durchaus denkbar, nur auf den Stand des letzten Full Backup zurückkehren zu können.
• Für wirklich kritische Datenbanken hingegen ist das Wiederherstellungsmodell “Vollständig” besser geeignet – allerdings auch nur dann, wenn man einen dazu passenden Backup- und Restore-Plan implementiert (siehe oben). Ein typisches Beispiel sind hochdynamische ERP-Datenbanken, für die ein Unternehmen den Datenverlust so gering wie möglich halten muss.
• Vorsicht: Sobald das Wiederherstellungsmodell auf “Vollständig” steht, ist man verpflichtet, für ein regelmäßiges Log-Backup zu sorgen, weil sonst die Log-Dateien unweigerlich voll laufen werden. Hierzu also:
  • Direkt im Anschluss an das Umschalten auf das “vollständige Wiederherstellungsmodell” eine Komplettsicherung (Full Backup) der Datenbank erzeugen
  • Direkt danach einen regelmäßigen Log-Backup-Plan einrichten (min. einmal täglich, vielleicht öfter) – hierzu eignet sich der SQL Server Agent
  • Den entsprechenden Recovery-Plan dafür entwerfen und dokumentieren
  • Das Log Volume ständig überwachen, ob es nicht trotzdem vollläuft (je nach Transaktionsart kann es durchaus auch mal zu einem plötzlichen deutlichen Anwachsen der Logs kommen, z.B. bei Daten-Imports in eine Datenbank)