Die Sicherung des Transaktionslogs ist eines der wichtigsten Dinge, die ein DBA einer Datenbank verstehen sollte. Den nur mit dem Transaktionslog können Datenbanken wieder bis zum Zeitpunkt des Crashs vollständig wiederhergestellt werden.
Zu diesem Thema habe ich unter http://www.insidesql.de/beitraege/administration/best_practice_fuer_log_files.html einen schönen Beitrag von Jonathan van Houtte gefunden, den ich hier nicht vorenthalten möchte.
Empfehlungen für Log Files für OLTP Datenbanken:
1: OLTP Produktionsdatenbanken sollten so gut wie nie die Datenbankoption “trunc. log on chkpt.” verwenden.
Die einzigen Vorteile dieser Einstellung sind:
- daß man sich keine Gedanken über die Wartung (Backup) des Transaktionsprotokolls zu machen braucht
- daß die “Gefahr”, kein Speicherplatz mehr zur Verfügung zu haben, weniger wahrscheinlich wird
Nachteile dieser Einstellung sind
- Schlechtere Performance aufgrund des laufenden Truncate Prozesses
- keine Point in time Wiederherstellungsmöglichkeit
- keine Möglichkeit, den Zustand bis zum Zeitpunkt des Fehlers wiederherzustellen
Trifft man die Entscheidung, daß eine Wiederherstellung des letzten vollständigen oder differentiellen Backup’s ausreichend ist, dann gibt man Erfahrung zugunsten von Bequemlichkeit auf.
2: Wird das Transaktionsprotokoll nicht gesondert gesichert, kann es auch nicht verkleinert werden. Ein vollständiges oder differentielles Backup verkleinert nicht das Transaktionsprotokoll.
3: Transaktionsprotokoll Backup’s sind losgelöst von anderen Backup’s und können unabhängig davon wiederhergestellt werden. Angenommen, Sie führen ein vollständiges Backup jede Nacht durch und sichern das Transaktionsprotokoll zweimal im Laufe des Tages. Finden Sie jetzt heraus, daß das letzte nächtliche Backup korrupt ist, können Sie das aus der Nacht zuvor wiederherstellen (WITH NORECOVERY!) und anschliessend die Log Backup’s wiederherstellen, um einen möglichst aktuellen Stand zu erreichen.
4: Das erste, was Sie in einem Disaster Fall machen sollten, ist, versuchen das Transaktionsprotokoll zu sichern. DBA’s, die ich unterrichte, werden darauf gedrillt.
5: Verwenden Sie SQL Server 7 und setzen die Einstellung “select into/bulkcopy” auf Wahr, um beispielsweise Daten aus einer Textdatei schnell zu laden, sollten Sie direkt danach die Einstellung wieder auf Falsch setzen, das Transaktionsprotokoll und dann die Datenbank sichern. SQL Server 2000 and höher können die Änderungen mit ihrem Bulk-Logged Recovery Model wiederherstellen. Allerdings nicht auf der einzelnen Vorgangsebene. Die Verwendung dieses Modells und Durchführung einer Bulk-Logged Operation verhindert Point in time Wiederherstellung (mit der dafür notwendigen Transaktionsprotokollsicherung) und limitiert Ihre Möglichkeiten, auf eine Korrumpierung Ihrer Datenbankdateien zu reagieren. Speichern Sie in der Datenbank unternehmenskritische dynamische Daten, sollten Sie sich genau überlegen, ob Sie die Fehlertoleranz des Full Recovery Models aufgeben zugunsten der Performance des Bulk-Logged Modells. Bedenken Sie dabei auch, daß auch SELECT INTO, CREATE INDEX, WRITETEXT und UPDATETEXT minimal protokollierte Operationen sind, genauso wie die offensichtlicheren BULK COPY und bcp.
6: Führen Sie die Transaktionsprotokollsicherung vor einem vollständigen oder differentiellen Backup durch statt danach. Sichern Sie das Transaktionsprotokoll nach der Datenbank, müssen Sie dieses Log Backup zusätzlich wiederherstellen, wenn Sie das Datenbankbackup wiederherstellen.
7: Verkleinern Sie nicht das Transaktionsprotokoll nur um des Verkleinerns willen. Die Protokolldateien sollten so dimensioniert sein, daß sie die maximale Menge an protokollierten Informationen zwischen zwei Backup Vorgängen aufnehmen können. Bestehen Sie auf der Verkleinerung, werden die Dateien vergrößert, wenn dies notwendig ist. Dies wiederum geht auf die Performance des Gesamtsystems, da Prozessorleistung für den Autogrow Vorgang benötigt wird und führt ferner zu Dateifragmentierung. (Gleiches gilt übrigens auch für Datendateien. Erwarten Sie, daß die Datenbank auf eine Größe von 20 GB anwachsen wird, sollten Sie auch diesen Speicherplatz direkt bei Erstellung der Datenbank allokieren).
8: Vergewissern Sie sich, daß Sie in einem Disasterfall in der Lage sind, eine Datenbank auf den aktuellsten Stand wiederherzustellen. Erstellen Sie eine Testdatenbank und warten diese exakt so, wie Ihre Produktionsdatenbanken. Führen Sie dann eine Notfallübung durch, indem Sie simulieren, daß die Hardware mit den Datendateien mit einem Fehler ausfällt. (Was ist das Erste, was Sie in einem solchen Fall machen? Punkt 4!). Überprüfen Sie Ihre Bandsicherungen und Wiederherstellungpläne wenigstens vierteljährlich, bzw, immer dann, wenn sich etwas in Ihrem Backup Prozess verändert. Sie können sich nur dann sicher sein, daß Ihre Backup funktionieren, wenn Sie sie ausprobieren und weiderherstellen; und sicherlich wollen Sie das nicht erst auf die harte Tour herausfinden. Üben Sie ebenso die Point in time Wiederherstellung. Ich bin dafür bekannt, die WHERE Klausel im DELETE Statement anzugeben.
9: Festplatten und andere Hardware werden irgendwann ausfallen. Die Frage ist nicht, ob, sondern, wann.
10: Sichern Sie die Datenbankdateien (mdf, ndf und ldf) erst dann, wenn Sie vorher die Datenbank mit sp_detach offline genommen haben oder den SQL Server Dienst gestoppt haben. Diese “Backup’s” sind nicht verwendbar mit differentiellen oder Transaktionsprotokollbackup’s. Auch wenn Sie später SQL Server verwenden, um sie zu sichern und mit No RECOVERY wiederherzustellen. Müssen Sie ein Backup Utility ohne nativen SQL Server Support verwenden, verwenden Sie SQL Server, um die Datenbanken und Transaktionsprotokolle auf Festplatte zu sichern (aber bitte auf einer separaten physikalischen Platte!) und verwenden anschließend Ihr Backup Utility, um diese Backup Dateien zu sichern. Sichern Sie auf einen anderen Server, beachten Sie bitte, daß der Service Account mit dem SQL Server oder der SQL Server Agent gestartet wird, ein Domänen User sein muß, der auch auf dem Remote Server angelegt sein muß. Zusätzlich sollte ein RESTORE VERFIYONLY angewendet werden, wenn auf einen Remote Server gesichert wird.
11: Löschen oder überschreiben Sie kein Backup, solange Sie nicht wenigstens zwei voneinander unabhängige Wiederherstellungsalternativen haben (siehe Punkt 3:).
12: Logging läßt sich nicht abschalten. Es ist ein integraler Bestandteil von SQL Server und garantiert die ACID Eigenschaften des Systems.
13: Verwenden Sie einen Write-Cache Disk Controller, stellen Sie sicher, daß sein Speicher redundant ist und batteriebetrieben. Lernen Sie, wie man seinen Speicher im Falle eines Disasters wiederherstellt. Deaktivieren Sie Caching auf den Platten selbst.
14: Stellen Sie sicher, daß die Transaktionsprotokolle auf redundanten physikalischen Platten sind und nicht auf dem gleichen Array wie die Daten. RAID 1 ist ideal für Transaktionsprotokolle, da ihr typischer I/O (im Gegensatz zu den Datendateien) sequentiell ist. Haben Sie mehrere Datenbanken gleichzeitig in Benutzung, kann es sinnvoll sein, jedes Transaktionsprotokoll auf einem eigenen RAID1 Platten Paar zu haben; anderenfalls könnte auf dem sequentiellen I/O ein random I/O werden.
