Der Microsoft SQL Server hält zahlreiche Hilfswerkzeuge für den Datenbankadministrator bereit. Leider sind die meisten dieser Werkzeuge nur für relationale Datenbanken verfügbar. Die OLAP-Datenbanken im Microsoft Analysis Server gehen häufig leer aus. Eines dieser Werkzeuge ist der sogenannte “Wartungsplan” zur regelmäßigen Sicherung von Datenbanken. In nachfolgendem Blogbeitrag wird aufgezeigt, wie die wichtigsten Komponenten dieses Werkzeugs auch auf OLAP-Datenbanken angewendet werden können.

Aufgaben von Wartungsplänen

Die im SQL Server integrierten Wartungspläne erfüllen, getreu ihrem Namen, zahlreiche Wartungsaufgaben rund um relationale Datenbanken. In nachfolgender Abbildung  ist eine Liste der typischen Aufgaben zu sehen:

Diese Aufgaben können für jede Datenbank frei kombiniert und konfiguriert werden. Durch die integrierte Zeitsteuerung ist eine regelmäßige, vollautomatische Wartung und vor allem Sicherung der relationalen Datenbank möglich.

Wartungspläne im OLAP-Umfeld

Warum bietet Microsoft eine solche Hilfe nun nicht auch für OLAP-Datenbanken an? Vermutlich, weil die Analysis-Services-Datenbanken zu 100% aus einer relationalen Datenbank aufgebaut werden. Alle Inhalte einer OLAP-Datenbank sind in den relationalen Snowflake-Tabellen vorhanden (selbst eingegebene Plandaten). Von daher kann man eine OLAP-Datenbank in jedem Fall aus der zugrundeliegenden relationalen Datenbank wieder aufbauen, solange man die relationale Datenbank regelmäßig sichert.

Das ist aber nur ein Teil der Wahrheit. Alle Metadaten der OLAP-Datenbank, wie z. B. die Sortierung einer Dimension, werden nicht in der relationalen Datenbank abgelegt. Würden diese verloren gehen, sind zwar alle Inhalte noch vorhanden, nicht jedoch die Meta-Informationen, wie diese Inhalte im OLAP-Modell darzustellen sind.

Das ist natürlich kein Problem, wenn man das OLAP-Modell mit einem Werkzeug wie dem DeltaMasterModeler aufgebaut hat, da dieser alle Meta-Daten ebenfalls relational ablegt und anschließend daraus ein OLAP-Modell generiert. Baut man jedoch das OLAP-Modell komplett manuell auf oder passt das DeltaMasterModeler-Modell nachträglich noch manuell an, ergibt sich die dringende Notwendigkeit für regelmäßige OLAP-Backups.

Aufgabenstellung OLAP-Wartungsplan

Abweichend zu den relationalen Wartungsplänen befasst sich der vorliegende Artikel lediglich mit dem Erstellen der regelmäßigen Sicherungen. Die anderen Möglichkeiten wie “Datenbankintegrität prüfen” oder “Datenbank verkleinern” haben für die OLAP-Datenbanken keine Relevanz.

Im Bereich der Sicherungen gibt es zwei Aufgaben zu bewältigen. Zum Einen das Erstellen von historisierten Backups, welche sich nicht überschreiben. Zum Anderen das “Aufräumen” des Sicherungsverzeichnisses, um nur eine bestimmte Anzahl von Sicherungsdateien parallel zu speichern.

Um die Aufgabenstellungen zu bewältigen und den OLAP-Wartungsplan später auch zeitgesteuert ausführen zu können, werden die SQL Server Integration Services verwendet. Diese bieten die notwendige Funktionalität, um die einzelnen Schritte umzusetzen.

Erstellung historisierter OLAP-Backups

Für die Erstellung der Backups benötigt man zunächst ein XMLA-Script, welches man in einem Integration-Services-Task verwenden kann. Dieses Script läßt sich am einfachsten direkt im SQL Server Management Studio generieren. Erzeugt man hier manuell ein Backup, läßt sich diese Konfiguration auch als Script anzeigen:

(Weiterführender Hinweis: Läßt der Dateibrowser im Sicherungsdialog nur die Speicherung in dem Programmverzeichnis zu, ist die Eigenschaft “AllowedBrowsingFolders” in den erweiterten Analysis Services Eigenschaften zu bearbeiten. Mit Pipe getrennt, lassen sich hier mehrere Ordner eintragen (z. B. S:\|C:\temp). Ein Neustart des Dienstes ist nicht notwendig)

Die automatische Generierung des Sicherungsdialogs erzeugt folgendes XMLA-Script:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>TestDB</DatabaseID>
       </Object>
       <File>C:\temp\OLAP_Backups\TestDB.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
</Backup>

Dieses Script kann anschließend in den SQL Server Integration Services innerhalb des Ablaufsteuerungselements “Analysis Services-Task ‘DDL ausführen’ ” verwendet werden:

Wie oben ersichtlich, wurde natürlich vorher ein neuer OLAP-Verbindungsmanager angelegt, namentlich “OLAP_DB”.

Das Paket kann nun bereits ausgeführt werden und erzeugt ein nutzbares OLAP-Backup. Allerdings ist bisher der Name des Backups noch statisch, was es zu ändern gilt. Ebenfalls sollte der Speicherpfad zwecks Wiederverwendung des Pakets dynamisch gehalten werden. Um beides zu erzielen, werden Variablen und sogenannte “Expressions” eingesetzt. Zunächst werden zwei neue Variablen erstellt. Eine enthält den Speicherpfad, eine wird zum Generieren des dynamischen Scripts verwendet:

Beim Erstellen der Variablen ist darauf zu achten, in welchem Bereich sie erstellt werden. In gezeigtem Beispiel sind die Variablen global gültig für das ganze Paket erstellt worden.

Die Variable für den Speicherpfad “sBackupDir” wird fix mit dem gewünschten Pfad befüllt. Die zweite Variable “sBackupDDL” wird über eine Expression dynamisch befüllt:

Bei der Verwendung von Expressions in Variablen ist darauf zu achten, den Parameter “EvaluateAsExpression” auf TRUE zu stellen. Andernfalls wird die Expression nicht interpretiert.

Die Expression selbst wird hier noch einmal explizit dargestellt. Der Trick in dem Ausdruck ist recht einfach. Es wird lediglich die Variable “sBackupDir” für die Bestimmung des Pfads verwendet. Anschließend wird über die “getdate()”-Funktion aus dem aktuellen Datum der Zeitstempel für den Dateinamen generiert. Der Dateiname kann natürlich nach eigenen Wünschen angepasst werden (z. B. Verwendung von Bindestrichen). Die mehrfach verwendeten Ausdrücke “(DT_WSTR, x)” dienen lediglich der Typkonvertierung von einem numerischen Wert in einen Text:

<Backup xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">
       <Object>
       <DatabaseID>TestDB</DatabaseID>
       </Object>
       <File>"+
       @[Benutzer::sBackupDir]  + (DT_WSTR, 4) year(getdate()) + RIGHT("0" + 
       (DT_WSTR,2) month(getdate()),2) + RIGHT("0" + (DT_WSTR,2) day(getdate()),2) + 
       "_TestDB.abf
       </File>
       <AllowOverwrite>true</AllowOverwrite>
</Backup>

Abschließend muss man den DDL-Task noch umkonfigurieren, so dass er nicht den zuvor hinterlegten statischen Befehl sondern das dynamische Script aus der Variablen verwendet:

Nun sollte das Pakte lauffähig sein und Backup-Dateien nach dem Schema YYYYMMDD_<DBName>.abf im definierten Verzeichnis erstellen.

Housekeeping

Im nächsten Schritt muss nun dafür gesorgt werden, dass das Backupverzeichnis regelmäßig aufgeräumt wird und alte Backup-Dateien gelöscht werden.

Der erste Ansatz könnte nun sein, schlicht den relationalen Task des Wartungsplanes “Wartungscleanup” dafür zu verwenden, da dieser genau dies für relationale Backups tut. Das würde im Paket folgendermaßen aussehen:

Leider funktioniert dieser Ansatz nicht, obwohl die Konfiguration durchaus sinnvoll aussieht. Der Wartungscleanup ruft im Hintergrund die Prozedur master.dbo.xp_delete_file auf. Diese Prozedur ist lediglich in der Lage, “echte” relationale Backups sowie deren Transaktionslogs zu löschen. Hierfür wird vor dem Löschen der tatsächliche Typ der Datei geprüft (unabhängig vom Dateinamen). Folglich muss das sog. Housekeeping manuell gebaut werden.

Was muss also getan werden? Es muss eine Schleife über alle existierenden Backupfiles in dem Backupverzeichnis implementiert werden. Innerhalb der Schleife muss bei jeder Datei das Alter geprüft und entschieden werden, ob diese gelöscht werden kann oder nicht. Folglich gliedert sich die Aufgabe in drei Teilaufgaben: Schleife, Altersprüfung und bedingtes Löschen.

Für die Schleifenprogrammierung steht seit den SQL Server Integration Services eine leicht zu konfigurierende Komponente zur Verfügung: der “Foreach-Schleifencontainer”. In diesem verwenden wir wiederum den variabel definierten Speicherpfad der Variablen “sBackupDir”:

Den Dateinamen der aktuellen Schleife speichern wir in einer neuen Variablen “sBackupLoopFile”:

Die Altersprüfung ist etwas komplexer und bedarf gleich mehrerer neuer Variablen:

Zunächst ist in der Variable “iDaysPreserve” zu konfigurieren, wie viele Tage in die Vergangenheit die Dateien gespeichert werden sollen. Aus dieser Variable wird anschließend das Zeitfenster ermittelt, über das die Backupdateien gesichert werden sollen. Dieses Zeitfenster wird durch die Variablen “iDateStartPreserve” (bzw. “dDateStartPreserve”) und “iDateEndPreserve” gebildet, wobei das Ende immer dem aktuellen Datum entspricht. Der Anfang wird durch das aktuelle Datum abzüglich der konfigurierten Tage zum Speichern (“iDaysPreserve”) errechnet. Die beiden Startvariablen in den unterschiedlichen Datentypen sind lediglich aus Gründen der Fehleranalyse in zwei Variablen aufgetrennt. Im Folgenden sind alle Expressions der einzelnen Variablen gelistet:

“iDateEndPreserve” bildet das aktuelle Datum als Zahlwert ab:

(DT_I4) YEAR(GETDATE())*10000+ MONTH(GETDATE())*100+ DAY(GETDATE())

“dDateStartPreserve” ermittelt den Start das Sicherungsfensters mit Hilfe des aktuellen Datums und der DATEADD-Funktion:

DATEADD("D", - @[Benutzer::iDaysPreserve]+1,GETDATE())

“iDateStartPreserve” verwendet das ermittelte Startdatum aus der Variablen “dDateStartPreserve” und konvertiert den Wert analog zu “iDateEndPreserve” in einen Zahlwert:

YEAR(@[Benutzer::dDateStartPreserve])*10000+ MONTH(@[Benutzer::dDateStartPreserve])*100+ DAY(@[Benutzer::dDateStartPreserve])

Das Datum der Datei in der aktuellen Schleife wird vereinfachend aus dem Dateinamen und nicht aus den echten Dateiattributen ermittelt. Die Vereinfachung kann bedenkenlos angewandt werden, da die Dateinamen innerhalb des Pakets selbst generiert werden. Die Variable “iDateLoopFile” bildet sich aus nachfolgendem Ausdruck, welcher mangels Verfügbarkeit der LEFT-Funktion den SUBSTRING-Befehl verwendet:

(DT_I4) SUBSTRING( @[Benutzer::sBackupLoopFile] , 1, 8 )

Der Name der zu löschenden Datei wird noch gesondert gespeichert, weil hier abweichend von dem reinen Dateinamen in der Schleife, der vollqualifizierte Name (Pfad + Dateiname) benötigt wird. Da das Datum immer aus den linken 8 Zeichen des reinen Dateinamens ermittelt wird, ist in der Variablen “sBackupLoopFile” der Pfad nicht gespeichert. Die Variable “sBackupLoopFileToDel” wird folgendermaßen befüllt:

@[Benutzer::sBackupDir] +  @[Benutzer::sBackupLoopFile]

Damit sind alle Informationen zur Altersprüfung vorhanden und das bedingte Löschen der Datei ist der letzte notwendige Schritt. Zum Löschen von Dateien gibt es in den Integration Services einen Task “Dateisystem”, der sich für die Aufgabenstellung anbietet. Der Task kann diverse Dateisystemoperationen durchführen, unter anderem das Löschen von Dateien. Der Task wird dementsprechend konfiguriert und die Variable “sBackupLoopFileToDel” als zu löschende Datei zugewiesen:

Bindet man diesen Task nun in den Foreach-Schleifencontainer ein, wird die Operation innerhalb der Schleife ausgeführt. Das Paket würde folgendermaßen aussehen:

Dem aufmerksamen Leser fällt natürlich auf, dass noch eine Kleinigkeit fehlt: die Bedingung. In der gezeigten Konfiguration würden einfach alle Dateien aus dem Verzeichnis gelöscht. Daher muss man noch einen kleinen Trick anwenden, um das Löschen nur auszuführen, wenn die Datei älter als 5 Tage ist. Dies geschieht über das Definieren eines einfachen Dummy-Tasks mit dessen Hilfe man dann die Rangfolgeeinschränkungen eines Workflow-Pfeils nutzen kann. Als Dummy-Task dient uns die Ausführung eines simplen SQL-Statements “Select 1″:

Von diesem Dummy ausgehend, kann man nun einen Workflow-Pfeil zu dem Lösch-Task ziehen und auf dem Workflow eine Rangfolgeeinschränkung definieren (Doppelklick auf den grünen Workflow-Pfeil):

Als Einschränkungs-Ausdruck wird folgender Befehl verwendet:

@[Benutzer::iDateLoopFile] < @[Benutzer::iDateStartPreserve]

Die Rangfolgeeinschränkung führt dazu, dass der Lösch-Task nur ausgeführt wird, wenn das Datum der aktuellen Datei kleiner ist als das errechnete Startdatum des Zeitfensters. Ist das nicht der Fall, wird das Löschen nicht ausgeführt. Das fertige Paket ist hier noch einmal dargestellt:

Das Löschen der alten Dateien ist bewusst vor dem neuen Backup platziert, um sich die Schleife für die im Paket neu erzeugte Datei zu sparen.

Das fertige Paket kann man schließlich mit ganz normalen Bordmitteln über den SQL Server Agent schedulen und regelmäßig zeitgesteuert ausführen.