Überblick
Falls Sie eine neue Datenbank anlegen, wird diese, sofern Sie keine Änderungen an der ‘model’-Systemdatenbank vorgenommen haben, im Full-Recovery-Modell betrieben. Und das ist auch gut so.
Bei unseren Assessments machen wir aber oft die Erfahrung, dass die Datenbanken der analysierten MSSQL Server Instanz im Simple Recovery Modell betrieben werden. Diese Betriebsart muss der Administrator explizit einrichten, so dass sich uns die Frage stellt, ob dem Kunden diese Situation bekannt ist und warum er diesen unsicheren Betriebsmodus verwendet.
Datenbankdateien und Recovery Modell
Sofern Sie sich schon einmal die Dateien angesehen haben, die eine Datenbank ausmachen, werden Sie festgestellt haben, dass diese immer zumindest aus einer Datei mit der Endung ‘.mdf’ und einer Datei mit der Endung ‘.ldf’ bestehen. Die mdf-Datei (“Datendatei”) wird dabei für die Speicherung Ihrer Tabellen verwendet und die ldf-Datei (das “Transaktions-Log” oder kurz “Tlog”) zur Protokollierung Ihrer Daten- und DB-Objekt-Änderungen. Die ldf-Datei besteht intern aus mehreren sogenannten virtuellen Logfiles (VLF), deren Anzahl und Größe variieren. Die VLFs werden zyklisch mit Transaktions-Protokollinformationen beschrieben, wobei ältere bereits beschriebene VLFs erst überschrieben werden können, wenn bestimmte Voraussetzungen erfüllt sind. Diese Voraussetzungen werden durch das konfigurierte Recovery-Modell bestimmt. In jeder Datenbank kann eines der drei Recovery-Modelle verwendet werden:
- Full Recovery Modell (Default)
- Simple Recovery Modell
- Bulk-Logged Modell (hier nicht betrachtet)
Checkpoints
Ein Checkpoint stellt eine DB-interne Aktion dar, bei der alle durch bereits abgeschlossene Transaktionen veränderten Datenblöcke auf die Platte, also in das Datenfile, geschrieben werden.
Das Full Recovery Modell
Im Full Recovery Modell können VLFs wiederverwendet werden wenn folgende Bedingungen erfüllt sind
- Die Transaktion die in das VLF geschrieben hat ist beendet
- Es ist ein Checkpoint erfolgt
- Das Transaktionsprotokoll wurde gesichert
Sind nicht alle drei Bedingungen erfüllt, so führt dies entweder zum Anhalten der Datenbank oder zu einem unkontrollierten Anwachsen des TLog. Insbesondere Bedingung 3 bedeutet, dass regelmäßig Sicherungen der Transaktionsprotokolle erfolgen müssen.
Existieren nun
- eine vollständige Sicherung der Datenbank
- eventuelle differentielle Sicherungen
- eine lückenlose Anzahl regelmäßiger Sicherungen der TLogs
so kann im Problemfall eine Wiederherstellung der Datenbank zu jedem beliebigen Zeitpunkt zwischen der letzten vollständigen Sicherung und dem Erkennen des Problemfalls erfolgen. Diese Möglichkeit wird auch als Point-In-Time Recovery bezeichnet.
Das Simple Recovery Modell
Im Simple Recovery Modell entfällt die dritte Bedingung und es ist nicht möglich, eine TLog-Sicherung durchzuführen. Die VLFs können also schneller wiederverwendet werden, was die Gefahr des unkontrollierten TLog-Wachstum verringert, aber den Nachteil hat, dass eine Wiederherstellung nur genau zu dem Zeitpunkt erfolgen kann, an dem die letzte vollständige oder differentielle Sicherung erfolgt ist. Erfolgt also täglich nach Geschäftsschluss eine vollständige DB-Sicherung und tritt am folgenden Tag kurz vor Geschäftsschluss ein Problem auf, so ist die gesamte Tagesarbeit verloren und das kann nur in sehr seltenen Fällen, nämlich wenn man die entsprechenden Daten jederzeit aus anderen Quellen wiederherstellen kann, vermieden werden.
Wie ermittle ich das Recovery Modell meiner Datenbanken?
Um zu ermitteln, wie die Datenbanken in Ihrer MSSQL Instanz betrieben werden, können Sie das folgende Statement verwenden:
Die folgende Abfrage zeigt das Ergebnis für den hier behandelten Testfall mit zwei Datenbanken in einer MSSQL Instanz. Hierbei handelt es sich um die von Microsoft bereitgestellte Testdatenbank AdventureWorksLT2022, die im einen Fall im Full- (Suffix _FR) und im anderen Fall im Simple-Recovery-Modus (Suffix _SR) betrieben wird.
In der Spalte recovery_model
sehen Sie die instanz-interne Codierung, in der Spalte recovery_model_desc die Bedeutung der jeweiligen Codierung (recovery_model_description
).
Situation bei Entwicklungs- und Testdatenbanken
Im Fall, dass im Rahmen eines MSSQL Assessments festgestellt wurde, dass eine Datenbank im Simple Recovery Modell betrieben wird und der Kunde darauf hingewiesen wurde, erhalten wir in einigen Fällen die Antwort, dass es sich dabei ja “nur” um Entwicklungs- oder Testdaten handele. Aber auch solche Daten sind aus unserer Sicht wichtig. Oder möchten Sie Ihrem Mitarbeiter erklären, dass er einen komplexen Testfall, den er zuvor aufwändig eingerichtet hat, erneut aufbauen muss, weil auf regelmäßige, automatische TLog-Sicherungen verzichtet wurde?
Datenbankwiederherstellung
Wie bereits weiter oben erwähnt, wurden zur Demonstration zwei identische Datenbanken AdventureWorksLT2022_FR (Full Recovery) und AdventureWorksLT2022_SR (Simple Recovery) angelegt. Diese werden über DB-Jobs der MaintenanceSolution von Ola Hallengren regelmäßig gesichert. Sicherungen sind folgendermaßen eingerichtet (Bezeichnungen entsprechen der Funktionalität und der Standard-Benennung aus dem “Hallengren-Script”):
- Freitags um 11:05 - DatabaseBackup - USER_DATABASES - FULL
- Montags bis Donnerstags um 15:10 - DatabaseBackup - USER_DATABASES - DIFF
- Montags bis Freitags zwischen 8:00 und 16:00 im 5-Minuten Rhythmus
Ausgehend von den Daten der Tabelle [customer_load
] wurde in beiden Datenbanken eine Tabelle [Kundenauswertung] mit ca. 3000 Datensätzen erstellt.
Über einen Datenbankjob wird minütlich die datetime-Spalte [modifiedDate
] auf das aktuelle Datum / die aktuelle Uhrzeit aktualisiert über:
Recovery in AdventureWorksLT2022_SR
Führt man im SQL Server Management Studio (SSMS) das Recovery für die Datenbank AdventureWorksLT2022_SR aus, so erhält man das folgende Bild:
Als Zeitpunkte für das Recovery am 27.07.2024 13:00 (die heutige differentielle Sicherung erfolgt erst in ca. 2 Stunden) sind lediglich der 21.06.2024 (Vollständiges Backup) und die jeweiligen Zeitpunkte des differentiellen Backups am 24., 25. und 26.06.2024 verfügbar und wenn beispielsweise der 26.06.2024 15:00 (das heutige differentielle Backup ist noch nicht gelaufen) eingestellt wird, springt der rote Marker auf die spätestmögliche differentielle Sicherung (am 25.06.2024 15:10).
Recovery in AdventureWorksLT2022_FR
Analog die Recovery-Möglichkeiten in der Datenbank mit dem Full Recovery Modell:
Hier ist jeder beliebige Zeitpunk vom ersten vollständigen Backup bis zum aktuellen Zeitpunkt möglich. Eine (kleine) Besonderheit tritt auf, wenn man tatsächlich den aktuellen Zeitpunkt für das Recovery wählt. Hier ist in der Regel noch eine letzte TLog-Sicherung, (“Tail-Log-Sicherung”), erforderlich, die die Instanz aber im Rahmen des Recovery-Prozess automatisch durchführt. Diese Situation wird am oberen Bereich des Backup-Fensters angezeigt und ist in der folgenden Abbildung dargestellt. In der Abbildung wurde zur Verdeutlichung der Zeitspanne zwischen dem letzten TLog-Backup und dem aktuellen Transaktionslog-Inhalt, der noch über die Tail-Log-Sicherung erfolgt, das “Timeline Interval” von “Week” auf “Hour” geändert:
Dateigrößenbetrachtung der beiden Datenbanken
Für die hier angestellte Größenbetrachtung der Datendateien wurde der Job zur regelmäßigen TLog-Sicherung für einige Zeit deaktiviert, und durch vorherige Kontrolle sichergestellt, dass die TLog-Datei bei Bedarf erweitert werden kann. Anschließend wurden die o.a. Update-Statements zur Aktualisierung des [modifiedDate] mit Hilfe des Query Stress Simulators 100 mal mit 100 parallelen Threads, also 10.000 mal ausgeführt. Die folgenden beiden Tabellen zeigen
- die seit dem Einrichten der jeweiligen Datenbanken stabilen Dateigrößen
- die Dateigrößen nach Durchführung der 10.000 Updates
nameAdventureWorksLT2022_DataAdventureWorksLT2022_LogAdventureWorksLT2022_DataAdventureWorksLT2022_LognameAdventureWorksLT2022_DataAdventureWorksLT2022_LogAdventureWorksLT2022_DataAdventureWorksLT2022_Log
Fazit
Datenverlust schmerzt, nicht nur in der Produktion. Wählen Sie grundsätzlich das Full Recovery Modell für Ihre Datenbanken und weichen Sie von dieser Regel nur in den extrem seltenen Fällen ab, in denen eine Point-In-Time Restaurierung Ihrer Datenbank aus anderen Quellen als den Backups schneller möglich ist.
Wenn Sie mehr über den beschriebenen Fall erfahren möchten, stehen Ihnen unsere Expert:innen gerne zur Seite. Vereinbaren Sie einfach ein unverbindliches Beratungsgespräch über unser Kontaktformular. Wir freuen uns von Ihnen zu hören!