Grundlagen für die Datensicherung von AG-VIP SQL über den SQL Server

Martin Richter [Grutzeck Software GmbH]
25.08.2021 08:23

Hintergrund

Datensicherung ist essentiell. Warum?

Nach einer Statistik werden Datenverluste verursacht zu

  • 60% durch Hardware Defekte unterschiedlicher Art
  • 25% durch menschliche Fehler und Fehlbedienung
  • 10% durch Software Fehler
  • 5% durch sonstiges (Viren etc.)

Ein falscher Import, ein falscher Löschvorgang, ein falsches Feld entfernt, eine Änderung an einem Hintergrundprozesse ... und ohne Datensicherung sind Ihrem dem Datenbestand evtl. Informationen verloren gegangen, die nicht wiederherstellbar sind.

Komplette Datensicherungen der entsprechenden Maschinen sind eine häufige Variante. D.h. virtuelle Maschinen oder physische Maschinen werden als Ganzes mit allen Festplatten gesichert. Meistens sind diese bei virtuellen Maschinen einfach einzurichten. Bei physischen Maschinen ist dazu spezielle Software nötig. (Hinweis: Eine SQL-Datenbank die benutzt wird, kann nicht kopiert werden). Der Nachteil ist, dass der Zugriff auf einen bestimmten Bestand zu einer bestimmten Zeit, nicht einfach möglich ist, da solche Sicherungen relativ selten laufen.

Sicherung über den MS SQL-Server sind hier der Beste weg. Über den SQL-Server lassen sich Sicherungen nach allen Möglichkeiten und Varianten einrichten.

Wichtig ist, dass die gesicherten Dateien natürlich auch in eine externe Datensicherung einbezogen werden, um auch gegen elementare Bedrohungen geschützt zu sein. Diese Sicherungen sollten auch außerhalb des Firmenstandortes gelagert werden.

Schutzfunktionen in AG-VIP SQL

Intern in AG-VIP SQL sollten aber auch alle Möglichkeiten genutzt werden, die vor Datenverlust durch Benutzer schützen. Das sind zum einen die eingebaute Papierkorb Funktion in AG-VIP und  das Änderungsprotokoll.

Die Daten im SQL Server

Eine SQL Datenbank besteht physisch gesehen aus mindestens 2 Dateien. Den Datendateien (Endung .mdf) und den sogenannten Transaktionslog-Protokolldateien (Im weiteren nur noch Protokolldateien genannt).

Die Datendateien (.mdf) enthalten die eigentlichen Tabellen und Daten und stellen immer den Zustand zum aktuellen Zeitpunkt dar.

In den Protokolldateien (.ldf) werden alle Transaktionen und Vorgänge protokolliert und mitgeschrieben. Diese Dateien sind essentiell wichtig, wenn man granulare häufige Schnappschüsse des Datenbestandes erreichen möchte. Also z.B. eine stündliche oder gar eine Sicherung alle 15 Minuten.

Wiederherstellungsmodell der Datenbank

Je nach Einstellung wird der Platz in der Protokolldatei sofort nach einer Transaktion wiederverwendet. Oder die Protokolldatei wächst immer weiter bis der Plattenplatz aufgebraucht ist.
Unser Support hat bei Kunden Datenbanken gesehen, bei denen die Datendateien ca. 200 MB groß waren. Aber Protokolldateien in der Größe von 600 GB!

Die Verwendung der Protokolldateien wird durch die Einstellung des Wiederherstellungsmodells in der Datenbank bestimmt. Gleichzeitig muss die Einstellungen für die Protokolldateien auch entsprechend der gewollten Backupstrategie angepasst werden.

Das Wiederherstellungsmodell der Datenbank kann über die Eigenschaften der Datenbank kontrolliert und verändert werden.

Dazu gehen Sie wie folgt vor:

  1. Starten Sie das MS-SQL Management Studio
  2. Wählen Sie die entsprechende Datenbank aus.
  3. Öffnen Sie die Eigenschaften der Datenbank (Rechter Mausklick -> Eigenschaften)
  4. Wählen Sie den Karteireiter Optionen.
  5. Hier werden drei Wiederherstellungsmodelle angeboten: Einfach, Vollständig und Massenprotokolliert. In diesem Artikel betrachte ich nur die Modelle Einfach und Vollständig.

Wiederherstellungsmodell Einfach

Datenbanken, die nach mit der Version 2.03.001 oder später angelegt wurden, werden im Wiederherstellungsmodell Einfach angelegt.

In diesem Modell werden alle Änderungen an der Datenbank im Transaktionsprotokoll aufgezeichnet und am Ende der Operation in die Datenbank übertragen.
Der Platz in der Protokolldatei wird anschließend wieder als verfügbar markiert.

D.h. bei großen Datenänderungen (zum Beispiel einem großen Import, oder einem Datenbankupdate) wächst die Protokolldatei entsprechend an. Die Größe bleibt auch nach der Operation erhalten, aber der Platz in der Datei wird  für die nächste Transaktion wiederverwendet.

Typischerweise haben Protokolldateien eine Größe zwischen 1-250 MB. Diese Protokolldateien können sofort jederzeit verkleinert werden auf eine Größe von ca. 1 MB.

Passende Backupstrategien sind:

  • Komplette Backups
  • Differentielle Backups
  • Kopie-Sicherung

In diesen Backup Modellen benötigen Sie immer nur die Backupdatei der Daten!
Ein separates Backup der Transaktionslog Dateien ist nicht notwendig.

 

Wiederherstellungsmodel Vollständig

Datenbanken die mit einer Version 2.02.002 oder davor angelegt wurden, werden im Wiederherstellungsmodell Vollständig angelegt.

In diesem Modell werden alle Änderungen an der Datenbank im Transaktionsprotokoll aufgezeichnet und am Ende der Operation in die Datenbank übertragen.
Im Unterschied zum Model Einfach wird der Speicherplatz in der Protokolldatei anschließend nicht wieder als verfügbar markiert. D.h. mit der nächsten Transaktion und Datenbankänderung wächst die Protokolldatei weiter. Der Platz in der Protokolldatei wird bei diesem Verfahren erst wieder freigegeben, wenn eine Sicherung des Transaktionsprotokolls durchgeführt wird.

D.h. ohne eine Sicherung des Transaktionsprotokolls wird im Wiederherstellungsmodell Vollständig die Protokolldatei immer weiterwachsen, bis Sie den gesamten Platz auf der Festplatte einnimmt und der SQL Server seinen Betrieb einstellt.

Im Modell Vollständig ist eine Sicherung der Protokolldateien zwingend notwendig.

Passende Backupstrategien sind:

  • Komplette Backups mit regelmäßigen Protokolldatei Sicherungen
  • Differentielle Backups mit evtl. regelmäßigen Protokolldateisicherungen
  • Kopie-Sicherung

In diesen Backup Modellen benötigen Sie immer die Backupdatei der Daten und je nach gewünschtem Wiederherstellungszeitpunkt die passenden Transaktionslog Dateien.

Sicherungsverfahren für die Daten

Unabhängig vom Wiederherstellungsmodell bietet der SQL Server mehrere Sicherungsverfahren an:

  • Vollsicherung,
  • Differentialsicherung,
  • Kopie-Sicherung.

Vollsicherung

Die Vollsicherung ist die Basis für alle Sicherungsformen. Die Vollsicherung enthält den gesamten Datenbestand zu einem bestimmten Zeitpunkt.

Die Vollsicherung ist auch immer der Ausgangspunkt und die Grundlage für alle anderen Sicherungsformen.

Für einen Restore-Vorgang mit einer Vollsicherung benötigen Sie genau eine einzige Datei: Die Datei der Vollsicherung. Der Restore-Vorgang ist einfach: Man gibt nur die Vollsicherung als Medium an, die importiert werden soll.

Differentialsicherung

Bei der Differentialsicherung wird immer zuerst ein vollständiges Backup erzeugt. Anschließend können eine beliebige Anzahl von differenziellen Sicherungen erfolgen.

Nach einer Vollsicherung führt jede Datenänderung an der DB dazu, dass bestimmte Teile der DB als geändert markiert werden. Diese werden dann in die differentielle Sicherung aufgenommen.

Jede nachfolgende differenzielle Sicherung umfasst dann nur noch die geänderten Daten, die seit der letzten Vollsicherung geändert wurden. D.h. jede neue differentielle Sicherung wird je nach Menge der Änderungen größer sein als die Datei zuvor.

Durch eine neue Vollsicherung werden alle Datenseiten wieder als umgeändert markiert.
Führt man also nach einer Vollsicherung direkt eine differenzielle Sicherung aus, dann ist diese Datei nahezu leer.

Wichtig sind immer nur die Vollsicherung und die letzte differenzielle Sicherung. Alle anderen differenziellen Sicherungen können benutzt werden, um Zwischenstände der Datenbank wiederherzustellen.

Für einen Restore-Vorgang benötigen Sie zwei Dateien: zuerst immer die Vollsicherung und die letzte Differentialsicherung. D.h. bei einem Restore-Vorgang muss die Vollsicherung und die Differentialsicherung direkt beim Restore angegeben werden. Der Restore erfolgt nicht nacheinander, sondern in einer Aktion.

Ist die Vollsicherung nicht mehr verfügbar, sind auch alle Differenz-Backupdateien wertlos.

Sicherung Transaktionsprotokolls

Die Sicherung des Transaktionsprotokolls ist nur im Wiederherstellungsmodell Vollständig möglich.

Das Transaktionsprotokoll wird gesichert und der Speicherplatz in der Protokolldatei wird wieder als verfügbar markiert. D.h. die Datei wird nicht verkleinert, aber intern wird eine neue Änderung der Datenbank den verfügbaren Platz wieder verwenden.

Nach der Sicherung des Transaktionsprotokolls lässt sich eine Protokolldatei auch verkleinern.

Die Transaktionslog-Sicherung umfasst nur die Änderungen an der Datenbank seit der letzten Vollsicherung bzw. seit der letzten Protokollsicherung. Die Größe der Sicherung richtet sich also ausschließlich nach der Menge der Datenänderungen.

Um einen Datensicherung zurückzuspielen ist es nötig bei einem restore Vorgang die Vollsicherung anzugeben und dazu alle weiteren Protokollsicherungen, die gemacht wurden. Alle Protokollsicherungen müssen lückenlos vorliegen. Alle Dateien werden im Restore Vorgang gemeinsam angegeben.

Kopie-Sicherung der Daten

Eine Kopie-Sicherung ist nichts anderes als eine Vollsicherung. Mit dem Unterschied, dass die Datenseiten in der Datenbank nicht zurückgesetzt werden und die nächste differenzielle Sicherung wieder die Daten sichert, die seit der letzten "echten" Vollsicherung geändert wurden.

Mit einer Kopie-Sicherung lässt sich also ein Schnappschuss der aktuellen Datenbank erzeugen ohne die Sicherungs-Sequenz selbst zu stören.

Kopie-Sicherung der Protokolldatei

Die Kopie-Sicherung des Transaktionsprotokolls ist nur im Wiederherstellungsmodell Vollständig möglich.

Wie bei der Kopie-Sicherung der Daten wird die Sicherungssequenz nicht gestört. Man erzeugt eine Kopie der Protokolldatei. Der interne Speicherplatz wird aber hierbei nicht freigegeben. Die Datei wird anschließend weiterwachsen, wenn es erneute Änderungen an der Datenbank gibt.

Die Kopie-Sicherung stört nicht die übliche Sequenz einer normalen Protokollsicherung.

Backupstrategien

Ausschließliche Verwendung von Vollsicherungen

Es werden ausschließlich Vollsicherungen erzeugt.
Das Wiederherstellungsmodel ist auf Einfach eingestellt. Eine Sicherung der Transaktionsprotokolle ist nicht notwendig.
Typisches Sicherungsinterval: 1-3 x wöchentlich.

Gut anwendbar:

  • Bei kleinen Datenbeständen
  • Bei Datenbeständen, bei denen eine Sicherung nicht in kurzen Abständen nötig ist.

Vorteile:

  • Einfache Rücksicherung.
  • Einfache Einrichtung.

Nachteile:

  • Wird das Sicherungsintervall kurz, kommen große Datenbestände zusammen.
  • Keine granulare Sicherung (in kurzen Zeitabständen) möglich.

 

Verwendung Vollsicherungen und Differenzialsicherungen

Es werden regelmässig Vollsicherungen erzeugt. Das Wiederherstellungsmodel ist auf Einfach eingestellt.
Eine Sicherung der Transaktionsprotokolle ist nicht notwendig.
Typisches Sicherungsinterval: 1 x wöchentlich eine Vollsicherung. 1-2 x täglich eine differenzielle Sicherung.

Gut anwendbar:

  • Bei kleinen bis mittleren Datenbeständen
  • Bei wenigen Datenänderungen
  • Wenn eine schnelle einfache Rücksicherung nötig ist.

Vorteile:

  • Einfache Rücksicherung. (Rücksicherung der letzten Vollsicherung + Rücksicherung der passenden Differenzialsicherung)
  • Übersichtliche Anzahl von Dateien. Nur zwei Dateien werden für eine Rücksicherung benötigt.
  • Oft verwirrend, weil Vollsicherung und Differentialsicherung oft mit der gleichen Dateinamenerweiterung (.bak) erzeugt werden.

Nachteile:

  • Wird das Sicherungsintervall kurz, kommen große Datenbestände zusammen.
  • Keine granulare Sicherung (in kurzen Zeitabständen) möglich bzw. nur mit großem Volumen.
  • Große Datenänderungen am Anfang des Sicherungszyklus vergrößern auch alle folgenden Differentialsicherungen.
  • Die Differentialle Sicherung kann so lange dauern wie ein Vollsicherung (Belastung des SQL Servers ist größer)

 

Verwendung von Vollsicherungen mit Sicherung der Transaktionsprotokollen

Es werden regelmäßig Vollsicherungen erzeugt. Das Wiederherstellungsmodel ist auf Vollständig eingestellt.
Typisches Sicherungsintervall: 1 x wöchentlich eine Vollsicherung. Sicherung des Transaktionsprotokolls im 15min - 4 Stunden Zyklen.

Gut anwendbar:

  • Bei jeder Form von Datenbestand
  • Sicherungsvolumen ist klein.
  • Dieses Sicherungsformat ist für alle AG-VIP SQL Nutzer geeignet und wird von uns favorisiert.

Vorteile:

  • Extrem kompaktes Sicherungsformat, da nur die Änderungen seit der letzten Vollsicherung aufgezeichnet werden.
  • Auch sehr kurze Sicherungsintervalle sind möglich (alle 5 Minuten)
  • Belastung des SQL Servers ist extrem gering während des laufenden Betriebes.
  • Damit werden extrem granulare Sicherungen möglich.

Nachteile:

  • Komplexe Rücksicherung. (Rücksicherung der letzten Vollsicherung + Rücksicherung aller notwendigen Transaktionsprotokollen). Bei einem Sicherungsintervall von 15min, können in einer Woche bis zu 400 Dateien zusammenkommen.

 

Verwendung von Vollsicherungen, zusätzlich mit Differenzialsicherungen und mit Sicherung der Transaktionsprotokollen

Es werden regelmäßig Vollsicherungen erzeugt. Das Wiederherstellungsmodel ist auf Vollständig eingestellt.
In regelmässigen Abständen werden zusätzlich Differentialsicherungen erzeugt.
Typisches Sicherungsintervall: 1 x wöchentlich eine Vollsicherung. Differentielle Sicherung 1x täglich, Sicherung des Transaktionprotokolls im 15min - 4 Stunden Zyklen.

Gut anwendbar:

  • Bei jeder Form von Datenbestand
  • Wenn Sicherungsvolumen keine Rolle spielen.

Vorteile:

  • Einfacheres Rückspielen von Datensicherungen durch die Differentialsicherungen (2 Dateien: Voll- und Differentialsicherung, dazu die entsprechenden Transaktionssicherungen)

Nachteile:

  • Komplexe Rücksicherung. 
  • Dateiform Chaos (welche Sicherung ist welche Datei)

 

Weitere Artikel zu dem Thema

 

Tags: Backup, Datensicherung, SQL
Durchschnittliche Bewertung: 0 (0 Abstimmungen)

Kommentieren nicht möglich