Einführung
Bei einer AlwaysOn-Datenbankspiegelung von SQL Server kann es zu einem Mismatch der LSN (Log Sequence Number) kommen. Dieser Fehler tritt ein, wenn die Spiegeldatenbank nicht genügend Transaktionsprotokolldaten enthält, um die Protokollsicherungskette der Hauptdatenbank beizubehalten. Grund dafür ist ein nicht erstelltes Log-Backup der Hauptdatenbank, oder das Backup wurde in der Spiegeldatenbank nicht richtig wiederhergestellt. An diesem Punkt ist es schwierig, das fehlende Transaktionsprotokoll-Backup zu finden. Abhängig von der Häufigkeit, in dem das Transaktionsprotokolls gepflegt wird, werden Hunderttausende von Protokollen generiert und der Fehler kann auf jedem sekundären AlwaysOn-Datenbankserver liegen.
Mit dieser Anleitung leiten wir Sie durch die zwei mögliche Methoden, wodurch ein LSN Mismatch behoben werden kann und die AlwaysOn-Datenbanken wieder synchron laufen.
Beispielszenario
Nimmt man beispielsweise an, es gibt eine sehr große Datenbank (VLDB) mit einer Größe von 8 TB. Eine Sicherung dieser Datenbank wird in einem Disaster Recovery (DR)-Standort gespeichert, welches sich in einem anderen Rechenzentrum befindet. Aufgrund einer nicht übereinstimmenden LSN, ist die Datenbank am DR-Standort jedoch nicht mit der primären Datenbank synchronisiert.
Soll dieses Problem behoben werden, ist der übliche Ansatz, ein vollständiges oder differenzielles Backup der primären Datenbank zu erstellen und es am DR-Standort wiederherzustellen. Angesichts der Größe der Datenbank, würde jedoch das Erstellen eines Backups und deren Übertragung über das WAN zum DR-Standort zu viel Zeit und Ressourcen in Anspruch nehmen.
Hinzu kommt, dass die Sicherung der primären Datenbank in einem CIFS Share (Common Internet File System) in einem anderen Rechenzentrum gespeichert wird. Soll die Wiederherstellung der Sicherung über das WAN von einem externen Standort aus ausgeführt werden, würde das nur weiter die Leistung verringern und die benötigte Zeit in die Höhe treiben. Aus diesem Grund ist ein anderer Ansatz erforderlich, um die Datenbank am DR-Standort wieder mit der primären Datenbank zu synchronisieren.
Die Logs sind in unserem Beispiel folgendermaßen aufgebaut. In der ersten Log-Backup-File ist die erste LSN 100 und die letzte 200. In der zweiten File sollte die erste LSN 200 und die letzte 300 sein. Das dritte Backup sollte somit mit LSN 300 beginnen und darüber hinaus enden. Die Kette sollte so immer weiter geführt werden.
Wie wird die LSN in der Log-Backup-Chain verwendet
Jeder Eintrag des Transaktionsprotokolls einer Datenbank kann eindeutig durch eine LSN Kennung unterschieden werden. In diesen Einträgen stehen Änderungen, die an der Datenbank vorgenommen werden, um später diesen Verlauf nachverfolgen zu können. Jedes Backup dieses Protokolls umfasst dabei nur einen Teil der LSNs. Es wird mit der ersten LSN begonnen, die nicht in der vorherigen Sicherung enthalten war und endet mit der letzten LSN der aktuellen Sicherung.
In unserem Beispiel umfasst das erste Backup den LSN-Bereich von 100 bis 200. Das zweite Backup sollte somit mit der 200 beginnen, da dies die letzte LSN des ersten Backups war. Die letzte die LSN des Backups ist die 300. Folgt man diesem Schema, beginnt das dritte Backup mit der LSN 300 und endet irgendwo darüber. Das gilt auch für jedes weitere Backup.
Durch die Fortführung dieser Reihe, wird eine Kette von Log-Backups erstellt, bei der jedes Backup einen Teil des Transaktionsprotokolls beinhaltet. Die Kette kann dafür verwendet werden, um die Datenbank zu einem bestimmten Zeitpunkt wiederherzustellen, indem Log-Backups der Reihe nach angewendet werden.
Daher ist es sehr wichtig, die Integrität der Log-Backup-Chain aufrechtzuerhalten. Wenn es nur ein fehlendes oder beschädigtes Backup gibt, wird die Kette unterbrochen und alle Backups danach sind für eine Wiederherstellung unbrauchbar.
Wie findet man den LSN Bruch heraus und repariert ihn
Es gibt verschiedene Methoden, an dieses Problem heranzugehen. Die Wahl der Lösung ist dabei abhängig von der Situation und Art des Problems. Im Folgenden will ich die verschiedenen Methoden erklären und beispielhaft anwenden.
Die Methoden sollten nicht in einer Produktionsumgebung getestet werden:
Es stellt eine einfache Möglichkeit dar, die LSN in der Always On-Datenbank zu beschädigen.
Reproduzieren des Fehlers
- Zu Beginn wird eine Datenbank eines Sekundärknotens aus einer Always On-Gruppe entfernt.
ALTER DATABASE [dba3] SET HADR OFF;Ersetzen Sie hierbei [dba3] durch Ihre eigene Datenbank.
- Erstellen Sie weitere Log-Backups des primären Knotens.
DECLARE @MyFileName varchar(200)
SELECT @MyFileName=''\\Sharepatht\dba3_'' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.trn'
--select @MyFileName
BACKUP log dba3 TO DISK=@MyFileNameErsetzen Sie dabei in Zeile 2 den Dateipfad '\\Sharepatht\dba3_' durch Ihren eigenen.
- Versuchen Sie anschließend, die Datenbank erneut zum sekundären Knoten hinzuzufügen.
ALTER DATABASE [dba3] SET HADR AVAILABILITY GROUP = [AG-Test];Hierbei muss erneut [dba3] und [AG-Test] durch Ihre eigenen Namen ersetzt werden.
Wenn alles geklappt hat, sollte dieser Fehler ausgegeben werden.
Msg 1478, Level 16, State 211, Line 1
The mirror database, has insufficient transaction log data to preserve the log backup chain of the principal database.
This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.Verwenden Sie nun die Backup Tabelle in MSDB (Systemdatenbank in Microsoft SQL Server, die Metadaten und andere Informationen über die SQL Server-Instanz speichert), um den Backup Verlauf mit dynamischem SQL abzurufen.
Dafür muss die untenstehende Vorlage für Ihre eigene Umgebung angepasst werden.
DECLARE @dbname VARCHAR(50)
DECLARE @backup_type CHAR(1)
SET @dbname = 'YourDatabaseName'
SET @backup_type = 'F' -- F for Full, I for Differential, L for Log
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT TOP 10
CONVERT(CHAR(100), b.backup_start_date, 121) AS BackupStartDate,
CONVERT(CHAR(100), b.backup_finish_date, 121) AS BackupFinishDate,
b.database_name,
CASE b.[type]
WHEN ''D'' THEN ''Full''
WHEN ''I'' THEN ''Differential''
WHEN ''L'' THEN ''Log''
END AS BackupType,
b.backup_size/1024/1024 AS BackupSizeMB,
b.compressed_backup_size/1024/1024 AS CompressedBackupSizeMB,
b.is_copy_only,
b.first_lsn,
b.last_lsn,
b.checkpoint_lsn,
b.database_backup_lsn,
b.recovery_model,
b.server_name,
b.machine_name,
b.recovery_fork_guid,
b.database_version,
b.database_creation_date,
b.backup_set_uuid
FROM msdb.dbo.backupset b
WHERE b.database_name = ''' + @dbname + '''
AND b.[type] = ''' + @backup_type + '''
ORDER BY b.backup_finish_date DESC
'
EXEC sp_executesql @sql- In der SELECT-Anweisung können Sie die Spalten anpassen, um die benötigten Informationen des Backup Verlaufs abzurufen. Das obige Beispiel fragt das Start- und Enddatum der Sicherung, den Datenbanknamen, den Sicherungstyp, die Sicherungsgröße und weitere Informationen ab.
- Nachdem Sie die SELECT-Anweisung mit dynamischem SQL erstellt haben, können Sie diese mit der folgender Funktion ausführen.
sp_executesqlErste Methode
Schritt 1
- Zuallererst soll das neuste Transaktionsprotokoll Backup gefunden werden. Hierfür führen Sie ein dynamisches Backup-Skript (wie in der oben genannten Sektion erwähnt) auf allen AlwaysOn-Replikaservern aus.
- Daraufhin, versuchen Sie die Datenbank mit diesem Backup wiederherzustellen. Der Versuch wird nicht funktionieren, jedoch steht in der Fehlermeldung eine LSN. Diese LSN wird benötigt, um die Datenbank tatsächlich wiederherzustellen. Mit dem erstellten dynamischen Backup, kann folgender Befehl ausgeführt werden ( die DB und Speicherpfad muss erneut von Ihnen entsprechend angepasst werden).
restore database dba3 from disk= '\\share\dba3_2016-08-29 09-43-58.trn' with norecovery- Mithilfe der Fehlermeldung kann man feststellen, ob die LSN, bei der das Programm abbricht, zu neu oder zu alt für das Backup ist, mit dem der Wiederherstellungsversuch unternommen wurde. Die Fehlermeldung sieht ungefähr so aus:
Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 39000000023900001Interpretation der Fehlermeldung
Um die Fehlermeldung besser verstehen zu können, sind hier einige Beispiele aufgeführt.
In meinem Fall sind die LSN-Nummern:
39000000023900001 – Alter LSN
41000000017300001 – Erforderlicher LSN
41000000017600001 – Aktueller LSN
Fehler bei einem alten Log-Backup:
Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 39000000023900001,
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.- Es ist zu alt, um auf die Datenbank angewendet zu werden. Ein neueres Backup wird benötigt.
Fehler bei einem aktuellen Transaktionsprotokoll-Backup:
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 41000000017600001,
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.- Es ist zu neu, um auf die Datenbank angewendet zu werden. Ein älteres Backup wird benötigt.
Soll eine Datenbank zu einem bestimmten Zeitpunkt wiederhergestellt werden, muss in der Regel die Backup-Datei gefunden werden, die die LSN zum gewünschten Zeitpunkt enthält. Bei einer großen Menge an Backups wird es schwierig, die richtige Backup-Datei mit der erforderlichen LSN zu bestimmen.
Eine Möglichkeit, besagte Backup-Datei zu finden, ist der Befehl restore headeronly. Mit diesem Befehl werden die Header-Informationen des Backups abgerufen, einschließlich des ersten und letzten LSNs. Vergleicht man nun die Ausgabe von restore headeronly mit dem erforderlichen LSN, kann die Liste der möglichen Backups eingegrenzt werden.
“restore headeronly from disk = ‘yourbackupfile.bak’”Sobald die richtige Backup-Datei identifiziert wurde, kann mithilfe von restore database die Datenbank auf den gewünschten Zeitpunkt wiederhergestellt werden.
Zu erwähnen ist, dass in einigen Fällen die ersten und letzten LSNs der Befehle restore headeronly und restore database identisch sein können. Dies kann dann eintreten, wenn die gesamten Transaktionen des Backups an des Ende gehängt wurde, ohne dass eine Lücke oder Unterbrechung entsteht. In diesem Fall können Sie sicher sein, dass die Backup-Datei den erforderlichen LSN enthält.
Schritt 2
Führen Sie erneut das Backup-Skript mithilfe der aus Schritt 1 erhaltenen LSN aus. Diesmal wird eine Bedingung eingefügt, um die Transaktionsprotokoll-Backups abzurufen, die entweder den erforderlichen LSN enthalten oder davor entstanden sind. Zur Wiederherstellung, sollte das Backup mit der ältesten LSN gewählt werden.
BSP: Erforderlicher LSN aus Schritt 1: "41000000017300001"
Erweitern Sie das dynamische Backup-Skript um folgende Bedingung:
“and b.first_lsn<=41000000017300001”Um den LSN zu erhalten, der die erforderliche LSN oder eine ältere enthält, muss mit folgendem Befehl sortiert werden:
“order by b.first_lsn desc”Kopiere first_LSN in der ersten Zeile. Das ist der Punkt, an dem der Bruch ist.
Beispiel eines Fehlers:
Error: log backup that includes LSN “41000000017300001” can be restored.Schritt 3
Wie zuvor bereits erwähnt, müssen die Backup-Dateien der Reihe nach angewendet werden, wenn die Datenbank zu einem bestimmten Zeitpunkt wiederhergestellt werden soll. Der Grund dafür ist, dass jedes Transaktionsprotokoll-Backup eine Teilmenge der Datenbankänderungen enthält, die seit dem letzten Backup vorgenommen wurden. Daher, um alle Änderungen korrekt anzuwenden, müssen die Transaktionsprotokoll-Backups in der richtigen Reihenfolge angewendet werden, beginnend mit dem vollständigen Backup und gefolgt von der Sequenz der Transaktionsprotokoll-Backups.
Das zuvor erwähnte dynamische Backup-Skript kann so modifiziert werden, dass es nur die erforderlichen Backup-Dateien abruft und in der richtigen Reihenfolge anwendet.
Die nötige Bedingung ist:
"and b.first_lsn>=41000000016700001"Es werden nur die Transaktionsprotokoll Backups abgerufen, die die erforderliche LSN enthalten oder älter sind.
"ORDER BY b.backup_finish_date"Durch Hinzufügen von ORDER BY [FINISH TIME] zum Skript, werden die Backup-Dateien nach ihrem Abschlussdatum in aufsteigender Reihenfolge sortiert. Somit werden die Backups in der Reihenfolge angewendet, in der sie erstellt wurden.
Leistungspakete der Mainzer Datenfabrik
Als professioneller SQL Server Support und zertifizierter Microsoft Partner unterstützen wir Sie in allen Fragen und individuellen Problemen rund um Ihre Serverumgebung, egal ob vor Ort oder remote. Überzeugen Sie sich selbst von unserem vielfältigen Angebot und den individuellen Leistungspaketen.
