Datenbank Mirroring mit T-SQL

In diesem Artikel widmen wir uns dem Vorgang, wie man eine Datenbank auf eine zweite Instanz spiegeln kann. Zur Implementierung werden in diesem Fall T-SQL Skripte verwendet. Bevor es zur eigentlichen Implementierung geht, wollen wir jedoch darauf eingehen, welche Eigenschaften das System haben muss, damit eine reibungslose Implementierung ermöglicht wird und wie die Funktionsweise einer solchen Spiegelung ist.

Wie funktioniert Mirroring

Will man eine Datenbank spiegeln, wird auf einem zweiten SQL-Server eine Art Kopie der ersten Datenbank erstellt. Diese ist mit der originalen, oder auch Prinzipal-Datenbank, verknüpft. Jede Änderung, die auf dem Prinzipalserver ausgeführt wird, wird auf die Kopie, auch die Mirror-Datenbank, übertragen. Dieser Mirrorserver ist von außen schreib- und lesegeschützt. Er dient somit nur als betriebsbereiter Standbyserver, falls der Prinzipalserver ausfallen sollte. Es gibt eine Möglichkeit, über Datenbank-Snapshots o.ä. auf die Datenbank zuzugreifen. Jedoch schauen wir uns diesen Fall hier nicht an.

Beim Erstellen des Mirroring, kann man zwischen zwei Commit-Modi wählen. Mit dieser Einstellung kann man angeben, in welchen Arbeitsschritten Änderungen auf die Mirrordatenbank übertragen werden. Da es beim Mirroring um Datensicherung geht, wird standardmäßig der synchrone Modus benutzt. In diesem wartet die originale Datenbank so lange, bis sie eine Antwort der Gespiegelten bekommt, bevor eine Änderung vorgenommen wird. Somit sind zu jeder Zeit die Daten gesichert. Diese Sicherheit geht jedoch auf Kosten der Leistung. Soll das System schneller arbeiten, kann der asynchrone Modus benutzt werden. Hier wartet der Prinzipalserver nicht auf eine Antwort des Mirrors. Fällt jetzt jedoch der Prinzipalserver aus, muss mit Datenverlust gerechnet werden.

Wenn diese Situation, des Datenbankausfalls eintritt, kann ein manuelles Failover ausgeführt werden. Die kopierte Datenbank wird zur Originalen und lässt den Zugriff auf die Inhalte zu. Somit ist eine Erreichbarkeit der Daten gewährleistet. Wenn nun die ausgefallene Serverinstanz die Verbindung wiederherstellen kann, schlüpft sie in die Rolle des Mirrors. Dieser Rollentausch kann beliebig oft ausgeführt werden.

Es gibt jedoch auch die Möglichkeit, einen automatischen Failover zu ermöglichen. Hierfür muss eine dritte Instanz dazwischen geschaltet werden, der sogenannte Witness. Der Witness steht mit beiden Datenbanken in Verbindung, besitzt aber selbst keine Kopie. Wenn der Mirror die Verbindung zum Prinzipal verliert, wird der Witness benachrichtigt. Da er ebenfalls mit dem Prinzipal verbunden ist, kann er entscheiden, ob ein automatischer Failover notwendig ist, oder nicht. Denn wenn der Witness ebenfalls die Verbindung verloren hat, steht mit Sicherheit fest, dass der Prinzipal ausgefallen ist. In jedem anderen Fall liegt das Problem beim Mirror, bzw. es besteht kein Handlungsbedarf.

Ein Client kann, wie oben schon erwähnt, nur auf den aktuellen Prinzipalserver zugreifen. Dies erfolgt initial über den Connection-String, der die Zugangsparameter zu dem Prinzipalserver enthält. Wenn der Client nun die Verbindung zum SQL-Server wiederherstellen möchte, gelingt dies nur, wenn der im Connection-String angegebene Server erreichbar ist und dieser der Prinzipalserver ist. Falls es zum Failover gekommen ist, wird der Client keine Verbindung mehr herstellen können, da die gespeicherten Servernamen nicht aktualisiert werden. Damit das System trotzdem verfügbar bleibt, können die Verbindungsparameter des Failover Partners zusätzlich in den Connection-String aufgenommen werden. Tritt der Fall ein, dass der initiale Server nicht erreichbar ist, wird versucht, den Failover Partner zu erreichen. Dadurch kann die Verbindung auch nach einem Failover hergestellt werden.

Vergleich zur Availability Group

Bei den vielen Möglichkeiten stellte sich uns die Frage, inwiefern sich das Ganze von einer Always-On Availability Gruppe (AO AG) unterscheidet. Bei einem Datenbank Mirroring, wird jeweils genau eine Datenbank gespiegelt. Im Vergleich dazu, können zu einer AO AG mehrere Datenbanken hinzugefügt werden. Außerdem hat man bis zu acht sekundäre Instanzen, während es zu einer Prinzipal-Datenbank nur genau eine Kopie geben kann. Die AO AGs sind somit eine Verbesserung des Mirroring, welche mithilfe von Clustering flexibler sind. Microsoft hat bereits angekündigt, dass in zukünftigen Versionen ein einfaches Datenbank Mirroring nicht mehr unterstützt werden soll. Wieso sollte man trotzdem ein Mirroring benutzen?

Um eine AO AG zu implementieren, müssen sich die Server vorher in einem Cluster befinden. Allein dadurch steigt die Komplexität der Anforderungen an das System. Zudem sind die Kosten für ein Mirroring geringer und die Einrichtung unkomplizierter. Aufgrund dessen kann das Mirroring als eine lightweight Alternative zu den gängigen AlwaysOn Verfahren gesehen werden. Mit wenigen einfachen Erweiterungen kann man auch hier eine hohe Verfügbarkeit mit automatischen Failovern erreichen.

Einen weiteren Vorteil hat das Mirroring gegenüber den AO AGs. Die Spiegelung ist ebenfalls mit domänenfremden Servern möglich. Damit das erreicht wird, müssen die Zugriffe über Zertifikate freigegeben werden. Es gibt auch eine Lösung für AO AGs, jedoch beinhaltet dies einen manuellen Eingriff in die DNS Einstellungen.

Voraussetzungen

Damit eine Implementation der gespiegelten Datenbanken möglich ist, gibt es ein paar Voraussetzungen an das System.
Auf den Servern sollten dieselben SQL Versionen laufen. Falls ein Witness dazu geschaltet werden sollte, muss die Version seines Servers kompatibel mit der der Datenbankserver sein.
Die Spiegelung unterstützt nur das vollständige Wiederherstellungsmodell.
Es sollte sichergestellt sein, dass auf dem Spiegelserver genügend Speicherplatz für die Datenbank ist.
Bei der Initialisierung der Spiegeldatenbank muss dies aus dem Backup der Prinzipaldatenbank mit NORECOVERY geschehen.
Es können nur Benutzerdatenbanken gespiegelt werden.

In unserem Beispiel wollen wir die Spiegelung einer Datenbank mithilfe von T-SQL zeigen. Hierfür haben wir ein simples Grundgerüst gewählt. Beide Server sind in derselben Domain (TestDomain.de). Der Prinzipalserver (TestServer1) hat einen eigenen User (TestUser1), sowie eine Datenbank (TestDB). Der Spiegelserver (TestServer2) hingegen hat nur den eigenen User (TestUser2). Die User sollten in ihren eigenen Datenbanken ein Login hinterlegt haben.

Es sollte dringend darauf geachtet werden, dass im Configuration Manager der Server, sowohl beim SQL Server, als auch beim Server Agent der richtige User in der “Log On As” Spalte hinterlegt ist (am besten hier jeweils TestUser1 und TestUser2 angeben). Das kann ansonsten später zu Fehlern führen.

Es sollte ebenfalls darauf geachtet werden, dass TCP bei den Servern freigeschaltet ist und die entsprechenden Ports in der Firewall freigegeben wurden. Das sind standardmäßig, um auf die Datenbank zuzugreifen, Port TCP/1433, sowie Port TCP/5022.

Implementierung des Datenbank Mirroring

Zuerst soll die Verbindung zwischen den Datenbanken erstellt werden, bevor ein Witness dazwischen geschaltet wird. Es wird vorausgesetzt, dass die Datenbanken über beide Server aufgerufen werden können. Ist dies der Fall, beginnt man auf dem geplanten Prinzipalserver. Damit der Spiegelserver Zugriff auf die Datenbank bekommt, muss als erster Schritt dem User, in unserem Fall TestUser2, ein Login gewährt werden. Hierfür wird ein Endpoint angelegt, der die Rolle des Servers angibt.

Jeder folgende Befehl wird als master ausgeführt. Hierfür kann man das Drop-Down-Menü in SSMS benutzen, oder zu Beginn jedes Skriptes USE master GO schreiben.

-- Erstellen des Endpoints für Mirroring
CREATE ENDPOINT Mirroring
  STATE=STARTED
  AS TCP (LISTENER_PORT=5022)
  FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

-- Erstellen eines Logins für den User der Spiegelinstanz
CREATE LOGIN [TESTDOMAIN\TestUser2] FROM WINDOWS
GO

-- Gewähre dem User Zugriff zum Endpoint
GRANT CONNECT ON ENDPOINT::Mirroring TO [TESTDOMAIN\TestUser2]
GO

Sobald das ausgeführt wurde, müssen die Einstellungen analog auf dem Spiegelserver ausgeführt werden.

-- Erstellen des Endpoints für Mirroring
CREATE ENDPOINT Mirroring
  STATE=STARTED
  AS TCP (LISTENER_PORT=5022)
  FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

-- Erstellen eines Logins für den User der Prinzipalinstanz
CREATE LOGIN [TESTDOMAIN\TestUser1] FROM WINDOWS
GO

-- Gewähre dem User Zugriff zum Endpoint
GRANT CONNECT ON ENDPOINT::Mirroring TO [TESTDOMAIN\TestUser1]
GO

Nachdem beiden Usern auf den jeweils anderen Servern Zugriff gewährt wurde, wird sich nun um die Datenbank gekümmert. Wie schon beschrieben, muss sich die Datenbank im vollständigen Wiederherstellungsmodus befinden. Wenn dies schon der Fall ist, kann der Befehl übersprungen werden. Anschließend wird ein Backup erstellt. Wichtig ist, dass der angegebene Speicherort ein freigegebener Ordner ist.

Es kann zu einem Fehler kommen, wenn vergessen wurde, die Sicherheitseinstellungen für alle oder nur den User freizugeben.

-- Einstellen des Recovery Models (kann übersprungen werden)
ALTER DATABASE TestDB SET RECOVERY FULL
GO

-- Erstellen eines Backups von der DB und des Logs
BACKUP DATABASE TestDB TO DISK='\\Backups\TestDB\TestDB.bak'
GO

BACKUP LOG TestDB TO DISK='\\Backups\TestDB\TestDB.trn'
GO

Mit diesem Backup wird nun auf dem Spiegelserver die Datenbank wiederhergestellt. Wichtig ist, dass dies mit NORECOVERY passiert. Danach wird beiden Datenbanken die jeweils andere als Partner zugewiesen.

Beim Schritt der Wiederherstellung kann der Befehl variieren. Die Version unten funktioniert auf jeden Fall, wenn eine neue leere Datenbank angelegt wurde. Wenn jedoch bereits mit der Datenbank gearbeitet wurde, empfiehlt sich, die Wiederherstellung über SSMS zu machen. Im Restore Fenster muss lediglich das Backup als Device gewählt werden.

-- Wiederherstellen der Datenbank auf dem Spiegelserver
RESTORE DATABASE TestDB FROM DISK=N'\\Backups\TestDB\TestDB.bak'
  WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO

RESTORE LOG TestDB FROM DISK = N'\\Backups\TestDB\TestDB.trn'
  WITH NORECOVERY
GO

-- Setze den Partner auf dem Spiegelserver
ALTER DATABASE TestDB SET PARTNER = 'TCP://TESTSERVER1.TestDomain.de:5022'
GO

Nun muss nur noch der Partner auf dem Prinzipalserver gesetzt werden und das Mirroring ist fertig.

-- Setze den Partner auf dem Prinzipalserver
ALTER DATABASE TestDB SET PARTNER = 'TCP://TESTSERVER2.TestDomain.de:5022'
GO

Jetzt sollte das Mirroring mit dem synchronen Modus fertig eingestellt sein. Will man zum asynchronen Modus wechseln, kann dies mit folgendem Befehl erreicht werden.

-- Einstellen des asynchronen Modus
ALTER DATABASE TestDB SET PARTNER SAFETY OFF
GO

(Mirror, Synchronized / Restoring…)

Verifizierung der Implementierung

Soll jetzt überprüft werden, ob die Implementierung erfolgreich war, gibt es hierfür zwei Wege.

Wenn die Datenbank in SSMS refreshed wird, sollte bei der Datenbankinstanz auf dem Prinzipalserver in Klammern (Principal, Synchronized) stehen. Analog steht bei der Spiegeldatenbank (Mirror, Synchronized / Restoring…).
Man kann sich den mirroring_state_desc, sowie die mirroring_role_desc über eine Query ausgeben lassen. Wenn im state ‘SYNCHRONIZED’ steht, wurde das Mirroring erfolgreich implementiert. Bei der role wird abhängig, von welcher Instanz der Befehl ausgegeben wurde, entweder ‘PRINCIPAL’ oder ‘MIRROR’ stehen.

SELECT mirroring_state_desc, mirroring_role_desc
FROM sys.database_mirroring
(Principal, Synchronized)

Wird nun manuell ein Failover eingeleitet, sollten die Rollen der Instanzen wechseln. Da kein Witness existiert, kann der Failover jedoch nicht automatisch erfolgen.

-- Einleiten eines manuellen Failovers
ALTER DATABASE TestDB SET PARTNER FAILOVER
GO

Hinzufügen einer Witnessinstanz

Wenn das Mirroring soweit steht, kann an dieser Stelle ein Witness hinzugefügt werden. Hierfür wird eine weitere Serverinstanz benötigt. Da der Witness jedoch keine Version der Datenbank besitzen wird, sondern nur als Vermittler dient, sollte lediglich der Login des Users hinterlegt sein (analog zu den anderen Usern). Im Folgenden wird der Witnessserver als TestServer3 und der User als TestUser3 aufgeführt.

Wie zuvor beim Mirroring, muss ebenfalls ein Endpoint konfiguriert werden. Auf diesen Endpoint sollen die User der anderen Serverinstanzen Zugriff bekommen.

Wie bereits bei den ersten Servern, sollte auch hier darauf geachtet werden, dass der Port 7022 in der Firewall freigegeben ist.

-- Erstellen des Enpoints
CREATE ENPOINT Mirroring
  STATE=STARTED
  AS TCP (LISTENER_PORT=7022)
  FOR DTABASE_MIRRORING (ROLE=WITNESS)
GO

-- Erstellen der Logins der anderen User
CREATE LOGIN [TESTDOMAIN\TestUser1] FROM WINDOWS
GO

CREATE LOGIN [TESTDOMAIN\TestUser2] FROM WINDOWS
GO

-- Gewähre den Usern Zugriff zum Endpoint
GRANT CONNECT ON ENPOINT::Mirroring TO [TESTDOMAIN\TestUser1]
GO

GRANT CONNECT ON ENPOINT::Mirroring TO [TESTDOMAIN\TestUser2]
GO

Sobald den Usern auf dem Witnessserver Zugriff gewährt wurde, muss der User des Witness Zugriff auf die anderen Server erhalten. Hierzu wird folgendes Skript analog auf beiden Servern ausgeführt.

-- Erstellen des Logins des Witness Users
CREATE LOGIN [TESTDOMAIN\TestUser3] FROM WINDOWS
GO

-- Gewähre Zugriff auf Endpoint
GRANT CONNECTON ENDPOINT::Mirroring TO [TESTDOMAIN\TestUser3]
GO

Wenn dies ebenfalls erledigt ist, wird im letzten Schritt der Witness auf der Datenbank hinterlegt. Hierfür muss auf dem Prinzipalserver der TCP-Port des Witness angegeben werden.

-- Hinterlgen der Witness-Verbindung
ALTER DATABASE TestDB
  SET WITNESS = 'TCP://TESTSERVER3.TestDomain.de:7022'
GO

Somit ist auch die Implementierung einer Witness Instanz abgeschlossen. Wenn der Prinzipalserver manuell ausgeschaltet wird, sollten die Rollen automatisch wechseln.

Verifizierung der Implementierung

Soll überprüft werden, ob der Witness richtig funktioniert, kann, wie oben beschrieben, ein manueller Ausfall des Prinzipalserver eingeleitet werden. Eine andere Möglichkeit ist, sich den mirroring_witness_name über eine Query ausgeben zu lassen. Wenn alles geklappt hat, sollte hier die TCP-Adresse (TCP://TESTSERVER3.TestDomain.de:7022) ausgegeben werden. Die Query lässt sich nur auf dem aktuellen Prinzipalserver ausführen.

SELECT mirroring_witness_name
FROM sys.database_mirroring
WHERE mirroring_state IS NOT NULL

Fazit

Mit wenigen Schritten lässt sich recht einfach eine einzelne Datenbank schnell über ein Mirroring System sichern. Somit ist die Datenbank auch bei Ausfall noch verfügbar. Wird das System dazu noch um einen Witness erweitert, ist die Verbindung zudem zu einem automatischen Failover fähig. Dadurch wird eine günstigere und unkompliziertere Version zu einer Always-On Availability Group möglich.

Möchten Sie weitere Informationen oder Hilfestellung zum Thema des Datenbank Mirrorings erhalten, stehen Ihnen unsere Experten gerne zur Verfügung. Kontaktieren Sie uns dafür gerne über Kontaktformular oder rufen Sie uns gerne an.