Automatisches Aktivieren von SQL-Server-Aufträgen nach einem Failover

Viele SQL-Server-Datenbanken werden im Zuge der Hochverfügbarkeit über zwei Instanzen gespiegelt. Zusätzlich gibt es in der Regel auf Instanzebene eine Vielzahl an SQL-Server-Aufträgen, welche im Zusammenhang mit gespiegelten Datenbanken stehen. Dieser Artikel beschreibt ein mögliches Verfahren zur automatisierten De-/Aktivierung von SQL-Server-Aufträgen im Kontext mit gespiegelten Datenbanken.

SQL-Server-Spiegelung
Durch das Spiegeln einer SQL-Server-Datenbank wird die Verfügbarkeit der Daten erhöht. Die Spiegelung wird auf Datenbankebene implementiert und kann ausschließlich für Datenbanken im vollständigen Wiederherstellungs­modell verwendet werden [1]. Die Konfiguration einer Spiegellösung wird in diesem Artikel nicht beschrieben [Q1]. WMI-Abfragen im SQL ServerMittels der SQL-Server-Warnungen kann auf verschiedene Ereignisse eines SQL Server reagiert werden. Hierzu kann unter anderem die WMI-Abfragesprache verwendet werden. Mittels WMI können Ereignistypen einer Instanz,einer Datenbank oder einem Datenbankobjekt abgefragt werden. So auch das Ereignis „Failover" einer SQL-Server-Datenbank. Damit auf dieses Ereignis reagiert werden kann, wird das WMI-Statement aus Abbildung 1 be­nötigt [Q2]. Unter [2] ist eine ausführliche Beschreibung zu dieser Abfrage zu finden.

Ausgangslage

In diesem Szenario sind zwei SQL Server 2012 Instanzen für die Datenbankspiegelung auf einem virtuellen Windows Server konfiguriert worden. Die erste Instanz hat den Namen SQL01. Die zweite Instanz heißt SQL02. Auf beiden Instanzen ist eine Datenbank Mirrored_DB vorhanden. Auf der Instanz SQL01 ist diese Datenbank zunächst die primäre Datenbank und auf der Instanz SQL02 die ge­spiegelte Datenbank.

Zusätzlich ist beispielhaft auf beiden Instanzen ein Auftrag definiert, welcher täglich ein Vollbackup der Datenbank Mirrored_DB durchführt. Dieser Auftrag hat den Namen Backup_DB_Mirrored_DB. Auf der Instanz mit der
primären Datenbank ist der Auftrag aktiviert und auf der sekundären Seite deaktiviert.

Dies hat den Hintergrund, dass eine gespiegelte Datenbank, welche aktuell als Spiegel fungiert, im Wiederherstellungsmodus NORECOVERY ist und somit auch nicht gesichert werden kann. Bei einem Failover muss der Administrator die Aufträge händisch auf der einen Seite deaktivieren und auf der anderen Seite aktivieren. Nach­folgend wird ein automatisiertes Deaktivieren und Aktivieren der betroffenen Aufträge beschrieben.

Einrichtung der Umgebung

Zu Beginn muss sichergestellt werden, dass beide SQL-Server-Agenten mit demselben Dienstkonto ausgeführt werden. In diesem Beispiel ist der lokale Server-Administrator für beide Instanzen inkl. Agent konfiguriert (siehe Abbildung 2). Ein Domänenkonto kann ebenso verwendet werden.
Für das geplante Vorhaben sind mehrere SQL-Server-
Objekte notwendig, welche nachfolgend aufgelistet sind:

• SQL-Server-Agent-Aufträge
• SQL-Server-Auftragskategorie
• SQL-Server-Warnungen (zwei pro Instanz)
• Aktivierter Service Broker [3]

Bei den Aufträgen handelt es sich zum einen um den Auftrag, welcher de/-aktiviert werden soll (hier: Backup_DB_Mirrored_DB). Ein zweiter Auftrag aktiviert bzw. deaktiviert den gewünschten Auftrag auf der jeweiligen Instanz. (hier: „De/-Aktivieren von mirrored Jobs"). Die Auftragskategorie dient dazu, dass nur solche Aufträge angepasst werden, welche mit der gespiegelten Datenbank im Zusammenhang stehen. Die Warnungen werden für die WMI-Abfragen auf beiden Instanzen benötigt, damit diese einen Failover auf der eigenen Instanz und des Spiegelpartners erkennen können.

Zu Beginn wird eine neue SQL-Server-Auftragskate­gorie mit dem Namen „AgentAufträge für Mirrored-DBs" auf beiden Instanzen konfiguriert. Das Erstellen einer Auftragskategorie ist unter [4] erklärt.

Im zweiten Schritt wird der gewünschte Auftrag, hier Backup_DB_Mirrored_DB, der neuen Auftragskate­gorie zugeordnet. Dies geschieht über die Eigenschaften des gewünschten Auftrages (siehe Abbildung 3). Kongruent zur Auftragskategorie sollte der Auftrag auf beiden Instanzen vorhanden und entsprechend identisch konfiguriert sein. Der zweite Auftrag („De/-Aktivieren von mirrored Jobs"), welcher neu erstellt wird, führt eine Aktivierung bzw.
Deaktivierung für die in der Auftragskategorie definierten Aufträge durch. Hier ist zu erkennen, dass mehrere Auf­träge einer Kategorie zugeordnet werden können. Somit ist es möglich, mehrere Aufträge bei einem Failover anzu­passen.

Das T-SQL-Skript aus Abbildung 4 aktiviert oder deaktiviert alle Aufträge, welche in der genannten Auftragskategorie enthalten sind, je nach Status der gespiegelten Datenbank. Das Statement aus Abbildung 4 muss in das Befehlsfenster des Auftrages eingetragen werden.

In dem T-SQL-Skript müssen zu Beginn kleine Anpassungen vorgenommen werden, falls sich der Datenbankname und die Auftragskategorie von diesem Beispiel unterscheiden. Die Werte der Variablen @Datenbank und @Kategorie müssen entsprechend der Umgebung angepasst werden. Falls die Konfiguration nicht abweicht, kann das Skript unverändert verwendet werden.

Für die Automatisierung werden im letzten Schritt zwei Warnungen auf jeder Instanz angelegt. Somit sind insgesamt vier Warnungen zu erstellen. Diese ermitteln per WMI-Abfrage, ob ein Failover auf dem jeweiligen Server und dessen Partner stattgefunden hat.

Unter dem SQL-Server-Agenten können neue Warnungen erstellt werden. Die erste Warnung überprüft, ob auf der
eigenen Instanz bzw. Datenbank ein Failover statt­gefunden hat. Hierzu sind in der Warnung folgende Einträge unter dem Reiter „Allgemein" vorzunehmen:

• Name: frei wählbar
• Typ: WMI-Ereigniswarnung
• Namespace: \\.\root\Microsoft\SqlServer\ServerEvents\<Instanzname>
• Abfrage: SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE
  WHERE State = 7 OR State = 8
  AND DatabaseName = 'Mirrored_DB'

Der Namespace bezieht sich bei der ersten Warnung auf die lokale Instanz, hier SQL01. Die Eintragung erfolgt
automatisch, sobald der Typ WMI-Ereigniswarnung gewählt wird (siehe Abbildung 5).

Auf dem Reiter „Antwort" muss die Checkbox „Auftrag ausführen" angehakt und der erstellte Auftrag „De/-Aktivieren von mirrored Jobs" ausgewählt werden. Optional kann zusätzlich ein Operator benachrichtigt werden (siehe
Abbildung 6).

Die zweite Warnung bezieht sich auf die sekundäre/entfernte Instanz. Diese kann identisch zur ersten Warnung konfiguriert werden. Die Warnung muss jedoch einen anderen Namen sowie Namespace haben. Der Name ist frei wählbar (z. B. Check_Failover_State (SQL02 remote)). Im Namespace muss die sekundäre Instanz angegeben
werden (siehe Abbildung 7).

Falls die sekundäre Instanz auf einem zweiten physischen Server konfiguriert ist, muss im Namespace, anstatt dem „Punkt" vor root, der DNS-Alias eingetragen werden:

\\<DNS_Alias_RemoteServer>\root\Microsoft\SqlServer\ServerEvents\<Instanzname>

Bei zwei physikalischen Servern benötigt der Benutzer des SQL-Server-Agenten zusätzlich noch spezielle Berechtigungen für den WMI-Dienst auf den beteiligten Servern. Das Festlegen von WMI-Berechtigungen für Benutzer, ist unter [5] erklärt. Folgende Berechtigungen sind für den Benutzer notwendig:

• Execute Methods
• Enable Account
• Remote Enable

Die beschriebene Konfiguration der Warnungen hat aktuell nur auf der ersten Instanz (SQL01) stattgefunden. Alle Schritte müssen abschließend noch auf der zweiten Instanz durchgeführt werden, sodass eine vollständige
Automatisierung stattfinden kann. Lediglich der Namespace und der Name, der beiden Warnungen auf der
sekundären Instanz, müssen jeweils von Lokal nach Remote getauscht werden. Bei zwei physischen Servern, muss der Namespace den entsprechenden DNS-Alias enthalten (siehe Abbildung 8).

Fazit

Mittels weniger Schritte kann das Arbeiten eines SQL-Server-Administrators im Bereich Spiegelung vereinfacht werden. Vor allem wenn ein Failover in der Nacht geschieht, ist nach dieser Konfiguration kein Eingreifen im
Zusammenhang mit den Agent-Aufträgen durch den Administrator mehr notwendig. Zusätzlich kann der Administrator über die WMI-Warnung auch per E-Mail benachrichtigt werden, sobald ein Failover auf den Datenbanken statt­gefunden hat.

Sebastian Herd
(info@ordix.de)

Links

[1] Microsoft Developer Network:
„Einrichten einer Sitzung für Datenbankspiegelung (SQL Server)":
https://msdn.microsoft.com/de-de/library/ms190941%28v=sql.110%29.aspx

[2] Microsoft TechNet: Phillip Garding, Microsoft Corporation:
„ Alerting on Database Mirroring Events" (07.04.2006):
https://technet.microsoft.com/en-us/library/cc966392.aspx

[3] Microsoft TechNet: „Aktivieren von Service Broker-
Nachrichtenübermittlung in Datenbanken (Transact-SQL)":
https://technet.microsoft.com/de-de/library/ms166086%28v=sql.105%29.aspx

[4] Microsoft Developer Network: „Erstellen einer Auftragskategorie":
https://msdn.microsoft.com/de-de/library/ms191128%28v=sql.110%29.aspx

[5] Microsoft TechNet (Windows Server):
„Autorisieren von WMI-Benutzern und Festlegen von Berechtigungen":
https://technet.microsoft.com/de-de/library/cc771551.aspx?f=255&MSPPError=-2147217396

Quellen

[Q1] Microsoft Developer Network: „Datenbankspiegelung (SQL Server)":
https://msdn.microsoft.com/de-de/library/ms189852%28v=sql.120%29.aspx

[Q2] Microsoft Developer Network:
„Verwenden von WQL mit dem WMI-Anbieter für Serverereignisse":
https://msdn.microsoft.com/de-de/library/ms180524%28v=sql.110%29.aspx

[Q3] Microsoft TechNet: „WMI Provider for Server Events":
https://technet.microsoft.com/de-de/library/ms180560%28v=sql.90%29.aspx

Abbildungen