Back(upp)en mit Rezept

Ein regelmäßiges Backup der von uns bearbeiteten Systeme lässt einen meist ruhig schlafen, korrekt? – Stimmt, wenn es denn ein Backup gibt und man darauf vertrauen kann!

In den meisten Projekten obliegt die Betriebsbereitschaft von Serversystemen der Verantwortung der IT-Abteilungen und das ist gut so. Es gibt regelmäßige Wartungspläne, ganze 19“-Racks voll mit Hardware sowie unterschiedliche Softwaretools, um automatisiert und möglichst effizient die wichtigen und sensiblen Daten zu kopieren, auf Medien auszulagern und damit vor Verlust zu schützen.

Die verschiedenen Verfahren sind nicht Bestandteil dieses Blogs, es sei nur so viel gesagt: Je nach Schutzbedarf und Datenmenge kann es kompliziert werden. Weiterführende Details und Hintergründe finden sich zum Beispiel beim Bundesamt für Sicherheit in der Informationstechnik (kurz: BSI) unter Abschnitt B1.4 des IT-Grundschutzkatalogs.

Was tun wir aber, wenn unser Business-Intelligence-Projekt von der Fachabteilung an der IT vorbei begonnen und eingeführt wird und unsere Server eben nicht dem Luxus einer geregelten Datensicherung unterliegen? Ein allgemeines Rezept als Trumpf im Ärmel zu haben; das zeigen wir auf den folgenden Seiten.

Szenario

Typischerweise wird in der IT mit Hilfe einer Risikoanalyse zunächst der Schutzbedarf ermittelt und ein dazu passendes Datensicherungskonzept für jedes System festgelegt. Für das hier betrachtete Szenario gilt:

• System ist nicht Bestandteil einer Datensicherung (weder das Betriebssystem, noch die darauf installierten Applikationen)
• dedizierte Hardware (keine Virtualisierung, kein redundantes System als Ersatz vorhanden)
• begrenzter interner Serverspeicherplatz
• nachgelagertes Reportingsystem (geringfügige Ausfallzeiten werden akzeptiert)

Unser Minimalziel für ein Backup muss es sein, nach einem Totalausfall der Hardware unsere DeltaMaster-Applikation in ihrem letzten produktiven Entwicklungsstand und ggf. auf alternativer Hardware wiederherstellen zu können. Das schützenswerteste Objekt sind daher unsere relationalen Datenbanken, denn die multidimensionale Welt kann unser DeltaMaster Modeler (kurz: Modeler) auf Knopfdruck wiederherstellen. Gehen die relationalen Datenbanken verloren und liegt kein Backup vor, ist die gesamte bisherige Entwicklung verloren und wir fangen wieder bei null an. Ein Umstand, den kein Kunde akzeptieren wird.

Das Rezept

Zunächst verdeutlichen wir uns die notwendigen Bestandteile, um unsere relationalen Datenbanken zu sichern (dabei lege ich Wert auf redundante Speicherorte und eine Historisierung):

• Erstellung notwendiger *.bak Dateien der zu sichernden relationalen Datenbanken
• zweiter Speicherort zur Ablage und Historisierung der Sicherungsdateien (externer Speicher bevorzugt, mindestens abweichende Festplattenpartition)
• „Intelligenter“ Verschiebe- und Löschmechanismus
• Automatisierungstool

Als erfahrene BI-Consultants wissen wir, dass zur Umsetzung dieses kleinen Rezepts eine Prise SQL, eine Handvoll Speicherplatz, ein Stück Batchprogrammierung und der beliebte SQL-Agent benötigt wird.

Generische Erstellung der Sicherungsdateien

Zur Erstellung der notwendigen Sicherungsdateien verwenden wir eine SQL-Prozedur, der zur besseren Skalierbarkeit ein Teil des Namens der Datenbanken und der Sicherungspfad übergeben werden kann.

CREATE PROCEDURE [dbo].[P_BC_Backup_Databases](@databasename_pattern sysname = '%', @backup_path varchar(1000)) AS

declare @databasename varchar(100)
declare @backupfilename varchar(1000)

declare db_cursor cursor for
	SELECT
		NAME
	FROM 
		SYS.databases
	WHERE
		NAME like @databasename_pattern
		AND NAME <> 'tempdb'

open db_cursor
fetch next from db_cursor
into @databasename

while @@FETCH_STATUS = 0
begin
	set @backupfilename = @backup_path + convert(varchar(8), getdate(), 112) + '_' +@databasename + '.bak'
	--'F:\Backup\'
	BACKUP DATABASE @databasename 
	TO DISK = @backupfilename
	WITH NOFORMAT
	,INIT
	,NAME = N'Vollständige Datenbanksicherung'
	,SKIP
	,NOREWIND
	,NOUNLOAD
	,COMPRESSION
	,STATS = 10

	fetch next from db_cursor into @databasename
end

close db_cursor
deallocate db_cursor
GO

Die Prozedur erstellt für alle Datenbanken (Resultset von db_cursor) jeweils ein eigenes Backupfile in der Nomenklatur _.bak im angegebenen Pfad. Dies ist später im Rahmen des Batchlaufs und der damit verbundenen Historisierung wichtig.

Ablageort

In dem hier beschriebenen Fall verwende ich die Ordner _Backup und _Sicherung als Sicherungsziele. Der Ordner _Backup dient als Ziel für die per SQL-Prozedur erstellten Sicherungsdateien, _Sicherung „simuliert“ den externen Speicherplatz und beinhaltet die Sicherungshistorie. Der Aufruf der nachfolgend erstellten Prozedur sieht also wie folgt aus:

Exec dbo.P_BC_Backup_Databases 'DeltaMaster%', 'C:\_Backup\'
GO

Hinweis: Wird kein Databasename_Pattern an die Prozedur übergeben, werden per Default alle Datenbanken außer die TembDB gesichert. Ein Dateipfad muss angegeben werden!

Und hier das Ergebnis auf dem lokalen System:

Abbildung 1: Lokal erstellte Sicherungsdateien

Tipp: Soll als externer Speicherort eine SSD-Festplatte über USB 3.0 (alles andere ist vermutlich nicht performant genug) verwendet werden, kann es vorkommen, dass diese nicht von SQL-Server „gesehen“ wird. Ursache ist das „Removeable-Bit“, welches vom Betriebssystem automatisch beim Anschließen der SSD gesetzt wird. Dies kann man umgehen, indem man mit dem kostenlosen Tool TrueCrypt eine verschlüsselte Partition auf der SSD erstellt. Bei der Konfiguration kann man angeben, ob die Partition „removeable“ sein soll oder nicht.

Intelligentes Verschieben

Theoretisch ist dieser Punkt nicht notwendig, denn wir haben bereits unsere relationalen Datenbanken gesichert und können täglich neue Backups von der Prozedur erstellen lassen. Das stimmt zwar, ist aber zu kurz gedacht, denn bei diesem Mechanismus dauert es nicht lange, bis der Server aufgrund von Speicherplatzproblemen stehen bleibt. Um das zu verhindern, verwenden wir ein kleines aber feines Batchskript namens „Delete.bat“ mit folgendem Inhalt:

echo off
xcopy /D /Y C:\_Backup\*.bak C:\_Sicherung\
c:
cd C:\_Backup\
for /f “skip=8 delims=” %%F in (‘dir *.* /B /O-N /A-D’) do del “%%F”
cd..
cd C:\_Sicherung\
for /f “skip=15 delims=” %%F in (‘dir *.* /B /O-N /A-D’) do del “%%F”

Erläuterung: Zunächst kopiert das Skript alle Dateien vom Typ BAK aus dem Verzeichnis _Backup nach _Sicherung und wechselt dann in das Verzeichnis _Backup.

• for /f “skip=8 delims=” %%F in ( )               : für jede Ausgabe (delims={nichts}) aus dem Befehl in Klammern die Anweisung hinter do ausführen
                                                                           skip=8, die ersten 8 Ergebnisse überspringen
• ‘dir *.* /B /O-N /A-D’                                     : Befehl DIR mit den Optionen
• /B                                                                     : nur Dateinamen
• /O-N                                                                : sortiert nach Namen, das – vor N bedeutet hier absteigend
• /A-D                                                                 : nur mit bestimmten Attributen
                                                                           D steht für Verzeichnisse, das – entfernt die Verzeichnisse aus dem dir-Ergebnis
• do del “%%F”                                                 : führe Befehl del aus, %%F wird durch die Dateinamen ersetzt

Anschließend wird der gleiche Befehl für das Verzeichnis _Sicherung angewendet, lediglich die Anzahl der zu überspringenden Dateien variiert. Da wir in der SQL-Prozedur das Datum in integer-Schreibweise dem Namen voranstellen und innerhalb des dir-Befehls absteigend nach Namen sortieren, ergibt sich eine lückenlose Historie der Sicherungsdateien. Über eine einfache Anpassung von skip=x kann gesteuert werden, wie weit die Historie zurückreicht.

Der große Vorteil von diesem Vorgehen ist, dass der verwendete Festplattenplatz nicht stetig weiter belegt wird und damit irgendwann zum limitierenden Faktor wird.

Einige von Ihnen fragen sich vielleicht, warum überhaupt zwei Speicherorte gewählt werden. Über den bereits aufgeführten Punkt bzgl. des Verbrauchs von Speicherplatz hinaus ist man so in der Lage, die Datensicherungen als Kopie außerhalb des Servers redundant vorzuhalten (für den schlimmsten Fall z. B. durch Brand oder Wasserschaden), trotzdem aber die aktuellste Sicherung auf dem Server für eine mögliche Wiederherstellung ohne großen Zeitverlust bereit zu halten.

Ergebnis im externen Speicherort:

Abbildung 2: Externer Speicherort mit historischen Sicherungsdateien

Automation

Zu guter Letzt erstellen wir einen SQL-Agentjob mit genau zwei Schritten für die tägliche Ausführung:

• Search and Backup Databases
  • Aufruf der unter Punkt 2.1 erstellten SQL-Prozedur
•  Move and Delete
  • Ausführung des Batchskripts aus Punkt 2.3

Abbildung 3: Auszuführende Schritte

Bei Schritt 2 sollte unbedingt die Verwendung eines Proxys für den Aufruf des Betriebssystembefehls (CmdExec), da im Dateisystem Berechtigungen für die Ordner und Dateien vorhanden sein müssen. Durch die Verwendung eines Proxys genau für den Benutzer können notwendige Berechtigungen differenzierter definiert werden. Verwendet man keinen Proxy, wird das Batchskript unter den Benutzerinformationen des SQL-Agent Dienstes ausgeführt.

Abbildung 4: Batchskript mit Proxy Ausführung

Fazit

Das hier aufgezeigte „Rezept“ versteht sich als Hilfsmittel, wenn keine kundeneigene Backupstrategie für die beteiligten Datenbankserver vorliegt. Dennoch halte ich dieses Vorgehen für einen eleganten Weg, um als BI-Berater ruhiger schlafen zu können. Eine derartige Datenbanksicherung ist das Minimum an Ausfallsicherheit, die implementiert sein sollte. Das hier aufgezeigte Rezept ist zumindest in einer 1 bis 2-Systemlandschaft in maximal 10 Minuten integriert, diese Zeit sollte man sich nehmen, um für einen GAU (größten anzunehmenden Unfall) gerüstet zu sein.

Weiterführende Links/ Quellen

Bundesamt für Sicherheit in der Informationstechnik (Abschnitt B1.4):

• Datensicherungskonzept
• Löschen Dateien per Batch