Bei der Durchführung einer Planung ist die Sicherheit der eingegebenen Werte kritisch. Dazu gehören Schutzmaßnahmen gegen versehentliches Löschen, zum Beispiel über Berechtigungen oder über die integrierte Rollback-Funktionalität in der Hybridplanung von DeltaMaster. Auch Back-ups sind ein essenzieller Bestandteil von Sicherungskonzepten in der Planung. Diese sorgen bei einem Systemausfall oder bei Datenverlusten außerhalb des Planungsprozesses dafür, dass so viele Planeingaben wie möglich wiederhergestellt werden können. Dieser Beitrag beschreibt ein Konzept, das auf Back-ups in verschiedenen Granularitätsstufen setzt und die Erstellungshäufigkeit zwischen laufenden Planungen anpasst, sodass ein gutes Verhältnis zwischen notwendiger Sicherheit und entstehender Datenmenge durch Back-ups erzielt wird.
Sicherungskopien (Back-ups) und eine damit verbundene Back-up-Strategie sind ein essenzieller Bestandteil von operativen und analytischen Datenbanksystemen. Sie beugen entweder dem Totalverlust von Daten oder dem Verlust von Zwischenständen vor. Ein ständiges Absichern sämtlicher Daten würde je nach Anwendungsgröße jedoch zu einer enormen Datenmenge führen und ist je nach Datenbank auch selten erforderlich. Aus diesem Grund verfügt Microsoft SQL Server über verschiedene sogenannte Wiederherstellungsmodelle („Recovery Models“), die auf Datenbankebene hinterlegt werden können. Diese sind im Detail in unserem Blog beschrieben.
In diesem Beitrag soll eine spezifische Back-up-Strategie für eine DeltaMaster-Planungsumgebung beschrieben werden, die auf dem vollständigen Wiederherstellungsmodell basiert.
Grundlagen des vollständigen Wiederherstellungsmodells
Ein vollständiges Wiederherstellungsmodell auf der Eingabedatenbank einer Planungsanwendung verspricht die Wiederherstellung von Daten zu beliebigen Zeitpunkten („Point-In-Time-Recovery“). Hierfür gilt es, verschiedene Back-up-Typen sinnvoll miteinander zu kombinieren. Zu unterscheiden sind hier:
vollständige Back-ups, bei denen die Datenbank in ihrer Gesamtheit auf einen Datenträger gesichert wird,
differenzielle Back-ups, die lediglich die Änderungen seit dem letzten vollständigen Back-up beinhalten und
Transaktionsprotokoll-Back-ups, die wiederum die Änderungen seit dem letzten differenziellen bzw. vollständigen Back-up umfassen.
Grafisch lässt sich diese Verschachtelung von Back-up-Typen wie folgt veranschaulichen:
Abb. 1: Verlauf von drei verschiedenen Back-up-Typen: Vollständig (F), Differenziell (D) und Log (L)
Kommt es nun zum Totalverlust des Datenbankservers, kann die Datenbank durch eine Kombination von Back-up-Typen wiederhergestellt werden. Soll beispielsweise die Datenbank zum Zeitpunkt L5 wiederhergestellt werden, so sind hierfür das vollständige Back-up F1, das differenzielle Back-up D2 und das Transaktionsprotokoll-Back-up L5 erforderlich. Damit die Back-ups in einem solchen Fall noch vorhanden sind, sollten diese auf einem vom Datenbankserver abweichenden Server gespeichert werden.
Ein weiterer zentraler Bestandteil des vollständigen Wiederherstellungsmodells ist das Protokollfragment bzw. im Englischen „Tail Log“. Dieses wird automatisch bei jeder Transaktion auf der Datenbank erweitert und ermöglicht eine Wiederherstellung zu einem exakten Zeitpunkt. Sobald ein Transaktionsprotokoll-Back-up („Log Back-up“) erstellt wird, wird das bisherige Protokollfragment geleert und beginnt ab dem Zeitpunkt des Transaktionsprotokoll-Back-ups erneut, sämtliche Transaktionen zu erfassen. Für die Wiederherstellung zu einem Zeitpunkt ist es erforderlich, dass ein vollständiges Back-up bzw. ein vollständiges sowie ein differenzielles Back-up vorliegen und zusätzlich keine Unterbrechung der Protokollkette („Log Chain“) vorhanden ist.
Dynamischer Rhythmus des Back-ups
Wie eingangs beschrieben, sind engmaschige Back-ups in der Eingabe-Datenbank grundsätzlich nur während einer laufenden Planung erforderlich. Zwischen einzelnen Planungsrunden gibt es in der Regel kaum Änderungen, sodass sich auch die Back-up-Häufigkeit deutlich reduzieren kann. Während der Planung wiederum können Log-Back-ups im Takt von wenigen Minuten erstellt werden. Um dieses Szenario abzubilden, richten wir exemplarisch drei SQL Server Agent Jobs ein, die jeweils eine bestimmte Art von Back-up vornehmen:
Abb. 2: SQL Server Agent Jobs für Back-ups
In den Jobs wird jeweils ein T-SQL-Statement ausgeführt, mit dem das Back-up erstellt wird. Dabei ist zu berücksichtigen, dass diese in einem realistischen Szenario auf einem Server erstellt werden sollten, der vom Datenbankserver abweicht. Hintergrund ist die Absicherung gegen den Katastrophenfall, dass der Datenbankserver vollständig ausfällt und verlorengeht – in diesem Fall sind Sicherungen auf dem Datenbankserver zwecklos. Die Sicherungen sollen jeweils komprimiert und auf Vollständigkeit geprüft werden. Außerdem ist die Berechtigung des SQL-Server-Dienstnutzers zu berücksichtigen: Dieser benötigt schreibenden Zugriff auf die Back-up-Verzeichnisse. Ist dies nicht der Fall, können die Befehle alternativ in jeweils ein SSIS-Paket aufgenommen werden, damit dieses im SQL Server Agent mit einem berechtigten Proxy-User ausgeführt werden kann.
Vollständiges Back-up
DECLARE @FileName nvarchar(500);
SET @FileName = N'C:\Back-ups\DeltaMaster_Entry_'+CONVERT(nvarchar(500), GETDATE(), 112) + REPLACE(CONVERT(nvarchar(8), GETDATE(), 114), ':', '')+N'_F.bak'
BACK-UP DATABASE [DeltaMaster_Entry] TO DISK = @FileName WITH NOFORMAT, NOINIT, NAME = N'DeltaMaster_Entry-Full_Back-up', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
DECLARE @Back-upSetId AS int
SELECT @Back-upSetId = position FROM msdb..Back-upset WHERE database_name=N'DeltaMaster_Entry' and Back-up_set_id=(SELECT MAX(Back-up_set_id) FROM msdb..Back-upset WHERE database_name=N'DeltaMaster_Entry' )
IF @Back-upSetId is null BEGIN RAISERROR(N'Fehler beim Überprüfen. Sicherungsinformationen für die Datenbank "DeltaMaster_Entry" wurden nicht gefunden.', 16, 1) END
RESTORE VERIFYONLY FROM DISK = @FileName WITH FILE = @Back-upSetId, NOUNLOAD, NOREWINDDifferenzielles Back-Up
HDECLARE @FileName nvarchar(500);
SET @FileName = N'C:\Back-ups\DeltaMaster_Entry_'+CONVERT(nvarchar(500), GETDATE(), 112) + REPLACE(CONVERT(nvarchar(8), GETDATE(), 114), ':', '')+N'_D.bak'
BACK-UP DATABASE [DeltaMaster_Entry] TO DISK = @FileName WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'DeltaMaster_Entry-Differential_Back-up', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
DECLARE @Back-upSetId AS int
SELECT @Back-upSetId = position FROM msdb..Back-upset WHERE database_name=N'DeltaMaster_Entry' and Back-up_set_id=(SELECT MAX(Back-up_set_id) FROM msdb..Back-upset WHERE database_name=N'DeltaMaster_Entry' )
IF @Back-upSetId is null BEGIN RAISERROR(N'Fehler beim Überprüfen. Sicherungsinformationen für die Datenbank "DeltaMaster_Entry" wurden nicht gefunden.', 16, 1) END
RESTORE VERIFYONLY FROM DISK = @FileName WITH FILE = @Back-upSetId, NOUNLOAD, NOREWINDLog Back-Up
DECLARE @FileName nvarchar(500);
SET @FileName = N'C:\Back-ups\DeltaMaster_Entry_'+CONVERT(nvarchar(500), GETDATE(), 112) + REPLACE(CONVERT(nvarchar(8), GETDATE(), 114), ':', '')+N'_L.bak'
BACK-UP LOG [DeltaMaster_Entry] TO DISK = @FileName WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'DeltaMaster_Entry-Log_Back-up', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
DECLARE @Back-upSetId AS int
SELECT @Back-upSetId = position FROM msdb..Back-upset WHERE database_name=N'DeltaMaster_Entry' and Back-up_set_id=(SELECT MAX(Back-up_set_id) FROM msdb..Back-upset WHERE database_name=N'DeltaMaster_Entry' )
IF @Back-upSetId is null BEGIN RAISERROR(N'Fehler beim Überprüfen. Sicherungsinformationen für die Datenbank "DeltaMaster_Entry" wurden nicht gefunden.', 16, 1) END
RESTORE VERIFYONLY FROM DISK = @FileName WITH FILE = @Back-upSetId, NOUNLOAD, NOREWINDZeitpläne
Der Zeitplan eines SQL Server Agent Jobs steuert dessen Ausführungshäufigkeit. Hauptidee dieses Beitrags ist es, diese Ausführungshäufigkeit davon abhängig zu machen, ob gegenwärtig eine Planung aktiv ist oder nicht. Ist die Planung nicht aktiv, könnte eine zeitliche Abstimmung der Jobs wie folgt aussehen:
- DeltaMaster_Entry_Full_Back-up: wöchentlich sonntags um 03:00 Uhr
- DeltaMaster_Entry_Differential_Back-up: keine Ausführung
- DeltaMaster_Entry_Log_Back-up: keine Ausführung
Während einer Planung kann im Zuge der Öffnung der Planung der Zeitplan der Jobs angepasst werden, sodass Back-ups nun wesentlich häufiger erstellt werden:
- DeltaMaster_Entry_Full_Back-up: wöchentlich sonntags um 03:00 Uhr
- DeltaMaster_Entry_Differential_Back-up: täglich Montag – Samstag um 03:00 Uhr
- DeltaMaster_Entry_Log_Back-up: täglich von 03:30 bis 23:59, jede Minute
Im beschriebenen Beispiel gibt es unabhängig von der laufenden Planung ein wöchentliches, vollständiges Back-up. Während der laufenden Planung kommen hierzu tägliche differenzielle Back-ups, die gemeinsam mit dem vollständigen Back-up den jeweiligen Stand des Tages wiederherstellen können. Zusätzlich wird im beschriebenen Fall in einem bestimmten Zeitraum jede Minute in einem Transaktionslog-Back-up gesichert. Damit lässt sich der Zustand der Datenbank minutengenau wiederherstellen. Die konkrete Ausgestaltung ist stets individuell vorzunehmen, die beschriebenen Intervalle stellen lediglich eine Anregung dar.
Das kurze Intervall der Log-Back-ups mag anfangs irritieren, da hierbei viele Dateien erzeugt werden. Ohne diese Back-ups würde im vollständigen Wiederherstellungsmodell jedoch ständig das Protokollfragment („Tail Log“) erweitert werden, was ebenfalls Speicherplatz beansprucht. Außerdem wird so die Zeit für die Erstellung einzelner Log-Back-ups minimiert, weshalb teilweise auch Experten sehr kurze Zeitintervalle hierfür empfehlen.
Wird die Planung geschlossen, kann wieder der Standard-Back-up-Rhythmus hergestellt werden (ein wöchentliches, vollständiges Back-up). Nach einiger Zeit sammeln sich so die verschiedenen Arten von Back-ups im entsprechenden Verzeichnis auf dem Back-up-Server an (vgl. Abbildung 3).
Abb. 3: Gesammelte Back-ups auf dem Back-up-Server
Unterschieden werden die Back-ups nach ihrer Endung: F, D und L. Auch die Dateigrößen sind sichtbar unterschiedlich, da die Transaktionslog-Back-ups erheblich kleiner sind.
Im Problemfall: Funktionsweise der Wiederherstellung
Bei einem Problemfall können grundsätzlich zwei Szenarien unterschieden werden:
Technischer Fehler (Datenbank- oder Serverausfall)
Inhaltlicher Fehler (Fehleingabe oder unbeabsichtigte Löschung von Daten)
Bei einem technischen Fehler gilt es, die Datenbank zum Zeitpunkt der letzten Sicherung wiederherzustellen. Hierfür müssen das letzte vollständige Back-up, das letzte differenzielle Back-up nach diesem vollständigen Back-up, sowie sämtliche Log-Back-ups nach dem letzten vollständigen bzw. differenziellen Back-up für die Wiederherstellung ausgewählt werden. Oft erfordern technische Fehler außerdem eine schnelle Wiederherstellung. Hierfür lässt sich ein Skript verwenden – ein Beispiel findet sich in den MS-SQL-Server-Tips.
Adaptiert auf unsere obigen Bezeichnungen und geringfügig erweitert ergibt sich folgendes Skript:
HUSE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @Back-upPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (Back-upFile NVARCHAR(255))
DECLARE @lastFullBack-up NVARCHAR(500)
DECLARE @lastDiffBack-up NVARCHAR(500)
DECLARE @Back-upFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'DeltaMaster_Entry'
SET @Back-upPath = 'C:\Back-ups\'
-- 3 – Start with removing other connections to database
SET @cmd = 'ALTER DATABASE ' + @dbName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
PRINT @cmd
-- 4 - get list of files
SET @cmd = 'DIR /b "' + @Back-upPath + '"'
INSERT INTO @fileList(Back-upFile)
EXEC master.sys.xp_cmdshell @cmd
-- 5 - Find latest full Back-up
SELECT @lastFullBack-up = MAX(Back-upFile)
FROM @fileList
WHERE Back-upFile LIKE '%_F.BAK'
AND Back-upFile LIKE @dbName + '%'
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
+ @Back-upPath + @lastFullBack-up + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd
-- 6 - Find latest diff Back-up
SELECT @lastDiffBack-up = MAX(Back-upFile)
FROM @fileList
WHERE Back-upFile LIKE '%_D.BAK'
AND Back-upFile LIKE @dbName + '%'
AND Back-upFile > @lastFullBack-up
-- check to make sure there is a diff Back-up
IF @lastDiffBack-up IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
+ @Back-upPath + @lastDiffBack-up + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBack-up = @lastDiffBack-up
END
-- 7 - check for log Back-ups
DECLARE Back-upFiles CURSOR FOR
SELECT Back-upFile
FROM @fileList
WHERE Back-upFile LIKE '%_L.BAK'
AND Back-upFile LIKE @dbName + '%'
AND Back-upFile > @lastFullBack-up
OPEN Back-upFiles
-- Loop through all the files for the database
FETCH NEXT FROM Back-upFiles INTO @Back-upFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
+ @Back-upPath + @Back-upFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM Back-upFiles INTO @Back-upFile
END
CLOSE Back-upFiles
DEALLOCATE Back-upFiles
-- 8 - put database in a useable state
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY'
PRINT @cmdDas Ergebnis der Skriptausführung sind die notwendigen Befehle, die es im SQL Server auszuführen gilt.
Abb. 4: Befehle im SQL Server
Nach der Ausführung wurde der letzte gesicherte Stand der Datenbank wiederhergestellt
Abb. 5: Wiederherstellung im SQL Server
Für die zweite Fehlerkategorie, die inhaltlichen Fehler, kann es verschiedene Ursachen geben. Nutzer könnten zunächst unbemerkt Fehleingaben getätigt haben, die Werte beispielsweise auf unerwünschte Bereiche verteilt haben. Selbes gilt auch für die versehentliche Löschung von Werten.
Insbesondere bei dringlichen Anpassungen von Planungsprozessen innerhalb einer laufenden Planung kann das unerwartete Nebeneffekte geben, die nicht sofort ersichtlich sind, sodass der Fehler mitunter erst Tage später auffällt. In diesem Fall ist eine Abwägung, ob die Planungsdatenbank zum gewünschten Zeitpunkt hergestellt werden soll, in der Regel nicht einfach, da hierbei auch korrekte Eingaben von anderen Nutzern rückgängig gemacht werden.
Im Fall von sofort bemerkten Fehleingaben ist es wichtig, den Zeitpunkt zu notieren und an die technische Betreuung der Planungsanwendung im eigenen Haus oder bei Bissantz zu melden. Die Planung sollte so bald wie möglich geschlossen werden, um Eingaben von weiteren Nutzern zu verhindern. Nach einer Prüfung der Auswirkungen der Fehleingaben kann dann entschieden werden, die Datenbank mit Hilfe der Back-ups zu einem bestimmten Zeitpunkt wiederherzustellen – idealerweise der letzte gesicherte Stand vor der Fehleingabe. Zur Wiederherstellung kann in diesem Fall analog zu obigem Skript verfahren werden, mit der Besonderheit, dass nur Log-Back-ups bis zu einem bestimmten Zeitpunkt berücksichtigt werden dürfen. Das obige Skript ließe sich dafür beispielsweise wie folgt in Schritt 7 anpassen:
-- 7 - check for log Back-ups
DECLARE Back-upFiles CURSOR FOR
SELECT Back-upFile
FROM @fileList
WHERE Back-upFile LIKE '%_L.BAK'
AND Back-upFile LIKE @dbName + '%'
AND Back-upFile > @lastFullBack-up
AND SUBSTRING(Back-upFile,19,12) <= '20231128114301'Wenn auch untertägig differenzielle oder vollständige Back-ups angefertigt werden, müssten die Bedingungen in den Schritten 5 und 6 entsprechend ergänzt werden.
Mit diesem Vorgehen kann ein inhaltlicher Fehler dahingehend korrigiert werden, dass die Datenbank auf den Zeitpunkt vor dem Fehler zurückgesetzt wird. Wie oben beschrieben, muss dabei beachtet werden, dass zwischenzeitlich getätigte Eingaben von anderen Nutzern erneut erfolgen müssen.
Fazit und Ausblick
Dieser Beitrag stellt einen Ansatz vor, der speziell auf Eingabe-Datenbanken im Rahmen der Planung mit DeltaMaster ausgerichtet ist. Durch den Einsatz der Enterprise-Architektur kann die Größe von Back-ups minimiert werden, da nur Nutzereingaben gesichert werden müssen. Zusätzlich wird die gesteigerte Notwendigkeit von Back-ups auf aktive Planungsrunden reduziert, da die Planungssteuerung mit der Back-up-Steuerung verzahnt wird. Die Intervalle für Sicherungen können dabei individuell angepasst werden.
Bei der Wiederherstellung wurde zwischen zwei Szenarien unterschieden, die jeweils leicht unterschiedliche Wiederherstellungsstrategien erfordern. Der Ansatz eignet sich insbesondere für Umgebungen, in denen nur bestimmte Zeiträume eines Jahres oder bestimmte Tage eines Monats für Planeingaben vorgesehen sind. Wird hingegen permanent geplant, gilt es, die resultierende Back-up-Größe mit der Bereitschaft zu etwaigem Datenverlust abzuwägen. Dies kann sich je nach Planungsbereich und zeitlicher Verfügbarkeit der Planenden stark unterscheiden.
Eine Weiterentwicklung des skizzierten Vorgehens ist eine „Aufräum-Routine“ im Back-up-Verzeichnis. Der beschriebene Ansatz sorgt für kontinuierliches Datenwachstum im Back-up-Verzeichnis. Mithilfe einer definierten Verwahrungszeit für Back-ups kann zusätzlich ein Prozess entwickelt werden, der das Back-up-Verzeichnis periodisch bereinigt und um „abgelaufene“ Sicherungssätze reduziert. Unabhängig von der konkret gewählten Back-up-Strategie sollte diese in jedem Planungsprojekt vorliegen. Sie kann entweder durch die IT des Kunden oder durch das Bissantz-Projektteam konzipiert und eingerichtet werden.
