Ziel dieses Artikels ist einen Überblick über LogShipping zu geben und die Grundzüge der Einrichtung und des Betriebs zu skizzieren.

Auch wenn das LogShipping in der Enterprise Edition voll unterstützt wird, kann bereits das Prinzip (manuell) mit jeder Edition des SQL Server verwendet werden. LogShipping ersetzt kein (Full-)Backup sondern kann parallel dazu eingesetzt werden.

Das Ziel des LogShippings ist es eine Datenbank an einem zweiten Standort zu duplizieren und möglichst aktuell zu halten, ohne den Einschränkungen der Replikation zu unterliegen. So ist dieses Verfahren auch problemlos für Datenbanken von Fremdprodukten möglich. Im Notfall soll bei einem Totalausfall des ersten Systems das zweite System schnell verfügbar sein und der Datenverlust möglichst gering gehalten werden. Im Gegensatz zu einem hochverfügbaren Cluster der als "hot-standby" bezeichnet werden kann, erreicht man mit LogShipping lediglich ein "warm-standby" mit der Gefahr geringer Datenverluste im Crash-Fall.

Grundlagen

Eine Datenbank besteht aus zwei unterschiedlichen Teilen, den Daten und dem Transaktionslog. Alle Aktionen werden auf den Daten ausgeführt und im Transaktionslog protokolliert. So erzeugt z. b. ein

Create Table T(Feld integer)

eine leere Datenbank-Tabelle und gleichzeitig wird die Information im Transaktionslog abgelegt.

Das Prinzip des LogShippings beruht darauf, dass die Änderungen zu der Standby-Datenbank übertragen werden. Hierzu wird ein Backup des Logs gemacht und auf der anderen Datenbank per Restore angewendet. Diese Standby-Datenbank hinkt logischerweise zeitlich hinterher. Ausserdem ist sie im ReadOnly-Modus, damit Sicherungen weiterer Logs angewendet werden können. Hieraus ergibt sich auch die Einschränkung, dass Standby-Datenbanken nicht gesichert werden können. Die Quell-Datenbank auf dem Primary Server kann und sollte natürlich weiterhin normal gesichert werden.

Nicht immer sind der Zeitpunkt der Log-Sicherung und das Ende der Transaktionen identisch. So kommt es in der Regel vor, dass auch unvollständige Transaktionen (also ohne Commit) auf die Standby-Datenbank übertragen werden. Zu diesem Zweck existiert eine Undo-Datei, in der die unvollständigen Transaktionen protokolliert werden.

Im Fall des Recovery der Standby-Datenbank wird die Undo-Datei angewendet und die offenen Transaktionen werden zurückgerollt. Die Undo-Datei wird gelöscht und die Standby-Datenbank kommt aus dem Read-Only-Modus.

Einrichtung

Für die Einrichtung des LogShippings war ein ausführliches Beispiel mit Sourcen im Microsoft Backoffice 4.5 Ressource Kit enthalten. Alternativ kann man die Prozeduren auch selber schreiben z. B. mit Hilfe von Nigel Rivett.
Etwas vollständiger findet sich die Beschreibung der benötigten Prozeduren bei SQLServerCentral.

Full Backup

Vorraussetzung für das LogShipping ist natürlich, dass die Datenbank nicht im Wiederherstellungsmodus (Recovery-Modell) "Einfach" (Simple) betrieben wird. Die Datenbank sollte stattdessen möglichst das Wiederherstellungsmodell "Vollständig" haben. Dieses Modell ist übrigens für die meisten Produktionsdatenbanken zu empfehlen, damit im Tagesbetrieb zusätzliche Logsicherungen einen evtl. Datenverlust durch Hardware-, Software- oder Bedienungsfehler absichern. Diese zusätzlichen Sicherungen sind auch ohne LogShipping zu empfehlen.
Der erste Schritt bei der Einrichtung ist immer ein Full-Backup der Datenbank. Über

BACKUP DATABASE TO DISK …

wird eine Sicherung erzeugt, die auf den zweiten Server transportiert werden kann.

Restore

Das Backup wird auf dem Zielsystem eingespielt. Mit

RESTORE DATABASE ...
FROM DISK ...
WITH MOVE ...,
STANDBY ...

wird

• der Name der neuen Datenbank angegeben,
• dann der Pfad des Backups
• und die Pfade der Standby-Datenbank.
• Als letztes wird der Speicherort der Undo-Datei verlangt.

Die Standby-Datenbank kann natürlich auf anderen Pfaden liegen, als auf dem Quell-System. Aus Kostengründen kann z. B. für den Standby-Server eine weniger großzügige Plattenausstattung gewählt werden, solange man sich im Klaren darüber ist, dass man im Ernstfall über diesen Server seine Produktion betreiben muss.

Log-Sicherungen

Nach der Einrichtung der Standby-Datenbank ist ein Backup des Transaktionslogs notwendig. Dieses wird mit

BACKUP LOG …
TO DISK …

gezogen. Idealerweise ist der Pfad des Backups auf dem Quell-System (Primary Server) um eine größtmögliche Unabhängigkeit vom Standby-Server zu erreichen.

Auf dem Standby-Server wird nun regelmässig die aktuelle Sicherung des Logs angewendet. Mit

RESTORE LOG …
FROM DISK…
WITH STANDBY …

kann sowohl der Pfad der Sicherung als auch der Ort der Undo-Datei spezifiziert werden.

Da diese Schritte möglichst automatisiert erfolgen sollen, sollte der Vorgang durch entsprechende SQLServerAgent-Jobs unterstützt werden.
So soll z. B. das Backup der Logs automatisch auf dem Primary-Server erfolgen. Das Quell-System erhält also einen Wartungsplan welcher z. B. alle 15 Minuten das Log auf lokale Platten sichert.
Dieser Sicherungsjob kann z. B. über den Wartungsplan-Assistenten eingerichtet werden.

Übertragung

Zur weiteren Entkopplung der beiden Server werden die Logs zuerst vom Primary auf den Standby-Server kopiert und dann (in der Regel weniger häufig) angewendet.
Das Ziel-System (Secondary Server) erhält drei Jobs:

• der erste kopiert das Log auf die lokalen Platten
• der zweite führt das Restore durch
• der dritte prüft gelegentlich, ob die Datenbank nicht zu sehr hinterherhinkt

In der von mir verwendeten Version werden alle Jobs durch den Aufruf einer Stored Procedure erstellt. Dies sichert eine einheitlichen Erstellung der Jobs zu, insbesondere wenn man mehrere Standby-Datenbanken betreiben will und evtl. auch von mehreren Server her Daten übertragen will.

Zuerst wird ein LogShipping-Plan erstellt, dem später die einzelnen Datenbanken zugeordnet werden. Die Parameter für die erste Prozedur sind:

• Name des Plans
• Quell-Verzeichnis
• Ziel-Verzeichnis
• Kennzeichen, ob ein eigenes Verzeichnis je DB verwendet wird
• Anzahl der Minuten zwischen den Ladevorgängen
• Anzahl der Minuten zwischen den Kopiervorgängen

Im zweiten Schritt werden die Datenbanken dem Plan hinzugefügt. Die Datenbanken werden später auch immer in dieser Reihenfolge abgearbeitet. Falls Datenbanken unabhängig voneinander verarbeitet werden soll, muss man mehrere Pläne definieren. Die hier verwendeten Parameter sind:

• Name des Plans
• Name der Quell-Datenbank
• Name der Ziel-Datenbank
• Verzögerung für das Laden (z. B. 120 Minuten um im Fehlerfall Analysen betreiben zu können)
• Kennzeichen, ob alle Logs geladen werden sollen
• Name des Primary-Servers
• Aufbewahrungsfrist für geladene Logs

Kontrolle

Der bereits angesprochen Kontroll-Job dient zur Überprüfung der Differenz zwischen

• Zeitpunkt Backup des zuletzt geladenen Logs
• und aktueller Zeit

Falls die Zeitdifferenz zu groß (z.B. 30 Minuten) ist, wird eine Fehlermeldung verschickt. Diese gibt das tatsächliche Delta in Minuten an.

Die Ermittlung des Deltas erfolgt so:

DECLARE @history_id int
select  @history_id = (select max(restore_history_id)
                       from   msdb..restorehistory
                       where  destination_database_name = 'Name der DB')
select  datediff(mi,
(
select backup_start_date
from   msdb..backupset
where  backup_set_id =
(
select backup_set_id
from   msdb..restorehistory
where  restore_history_id = @history_id
)
),
getdate())

Hierbei ist @history_id die ID des zuletzt angewendeten Backup-Sets der betrachteten Datenbank.

Zusätzlicher Nutzen

Die Möglichkeit einen anderen Namen für die Standby-Datenbank zu wählen öffnet einem z. B. den Weg auf ein und demselben Server das LogShipping durchzuführen. Dies könnte z. B. für Testfälle interessant sein oder eine schnelle Verlagerung der Datenbankdateien auf andere Platten ermöglichen, falls nur ein kleines Wartungsfenster zur Verfügung steht. Zu gegebenem Zeitpunkt erfolgt einfach das

• Detachen der alten Datenbank (sp_detach_db)
• Recovern der neuen Datenbank (restore des letzten Logs mit recovery)
• Umbenennen der neuen Datenbank (sp_renamedb)

Die ganze Aktion dauert dann deutlich weniger als 5 Minuten auch wenn die Datenbank viele GB groß ist.

Ein weiterer Nutzen ist, dass zur Lastverteilung die Standby-Datenbank auch für rein lesende Abfragen verwendet werden kann. Lediglich zum Zeitpunkt des Restores der Log-Sicherungen benötigt der SQLServer exklusiven Zugriff auf die Datenbank.

Failover

Recovern

Im Ernstfall oder beim regelmässigen Test des Ernstfalls geschieht nach dem letzten Laden eines Logs das Recovern über den Befehl:

RESTORE DATABASE ... WITH RECOVERY

Anwendungen neu ausrichten

Die Datenbank-Anwendungen müssen ihre Connections ab jetzt zum Secondary-Server aufbauen. Bei Totalverlust des Primary-Servers ist es natürlich auch denkbar den Secondary-Server den Namen und die IP-Adresse des Primary-Servers übernehmen zu lassen.

Logins

Achtung: Im Ernstfall kann kein Wizard zur Übertragung der Logins verwendet werden, da der Primary-Server dann nicht mehr verfügbar ist.

Falls Windows-Authentifizierung verwendet wird und die Logins ebenfalls auf dem Secondary-Server angelegt wurden, können die Anwender sofort wieder arbeiten, nachdem die Anwendungen auf den neuen Datenbankserver ausgerichtet wurden.

Falls SQL-Logins verwendet wurden, sind diese auf dem Secondary-Server mit dem (möglichst) aktuellen Kennwort anzulegen und in jeder Datenbank sind die Logins erneut mit dem User zu verbinden. (sp_change_users_login)

Eine Möglichkeit sich täglich Skripte für die Logins anzulegen ist folgende:

Windows-Logins

• Nicht vorhandene Logins werden angelegt
• Zu allen Windows-Logins werden die Default-Datenbank und -Sprache definiert
• Die Berechtigungen sind in den Datenbanken vorhanden und werden automatisch mit den vorhandenen Windows-Logins verknüpft.

Erzeugung der Skripte

select 'if not exists (select * from master..syslogins where name = N''' + loginname+''')'
 + char(10) + 'exec sp_grantlogin N''' + loginname + ''''
 + char(10) + 'exec sp_defaultdb N''' + loginname+''', N''' + dbname +''''
 + char(10) + 'exec sp_defaultlanguage N''' + loginname + ''', N''' + language + ''''
 + char(10) + 'GO'
from master..syslogins
where isntuser = 1

SQLServer-Logins

• Es werden alle SQL-Logins gelöscht, die anschließend neu angelegt werden sollen, damit auch Passwort-Änderungen übertragen werden.
• Die neuen Logins werden mit dem Passwort des Primary Servers angelegt. Falls also eine Mischung von Entwicklung und Standby-Server vorgesehen ist, so würde dieses Skript die Passwörter des Primary Servers (Produktion) auf dem Entwicklungsrechner anlegen.
• ACHTUNG: Das Mapping von SQLServer-User auf Login muß trotzdem noch für jede Datenbank per sp_change_users_login erfolgen.

Erzeugung der Skripte

SELECT 'EXEC sp_droplogin '''+loginname+''''
 +char(10)+ 'EXEC sp_addlogin @loginame ='''+loginname+'''
 , @passwd =', CONVERT(VARBINARY(256), password),
 char(10)+ ', @defdb ='''+dbname+''', @deflanguage ='''+language+''', @encryptopt = ''skip_encryption'''
 +char(10)+'GO'
FROM master..syslogins
WHERE isntuser = 0
and isntname = 0
and isntgroup = 0

Übertragung der Skripte

Die Übertragung kann z. B. auf folgende Weise erfolgen:

1. Jeden Tag werden die Skripte auf dem Primary Server erzeugt und in eine Tabelle geschrieben
2. Anschließend werden diese per osql als SQL-Text-Dateien auf dem Standby-Server im Standard-Log-Verzeichnis zur Verfügung gestellt.

Schlussbemerkung

Das Prinzip des LogShippings ist unkompliziert und relativ einfach selber zu implementieren. Je nach gewünschtem Automatisierungsgrad ist hier mehr oder weniger Aufwand notwendig.
Man gewinnt neben einer fast aktuellen Kopie der Datenbank auch die Sicherheit über ein aktuelles Backup zu verfügen. Denn die Log-Sicherungen können auch auf jedes aktuelle Full-Backup angewendet werden, welches weiterhin als Tagessicherung erstellt werden sollte.