Benachrichtigung – im Standard individuell

Dieser Beitrag stellt eine Benachrichtigungslösung für SQL-Agentjobs vor, die einheitlich und dennoch flexibel erweiterbar ist. Dafür sind drei technische Tricks notwendig, die Schritt für Schritt erläutert werden.

Am Ende jedes Projekts geht ein Business-Intelligence-System in den laufenden Betrieb über: Nach Abschluss der Testphase wird das Datawarehouse (kurz: DWH) sowie die darauf aufbauenden DeltaMaster-Anwendungen an den Regelbetrieb übergeben. Von nun an gilt es die periodischen, meist täglichen Prozesse möglichst fehler- und damit für den Endanwender störungsfrei zu betreiben. Oft müssen dafür diverse Prozesse des Datenbanksystems überwacht und kontrolliert werden. Typischerweise ist das eine manuelle Tätigkeit für Personen in der IT-Administration oder des jeweiligen Fachbereichs.

Besser wäre es, wenn die notwendigen Prozesse diesen Personenkreis benachrichtigen, sobald es zu einer Anomalie kommt. Das spart Zeit und ermöglicht es dem Fachbereich, sich auf die eigentlichen Aufgaben und Datenanalysen konzentrieren, statt sich der täglich wiederkehrenden Prüfung der Prozesse zu widmen.

Für genau diesen Fall haben wir im Projektumfeld ein Framework entwickelt, mit dem sich solche Benachrichtigungen standardisiert und dennoch flexibel in jedem DWH realisieren lassen, das mit DeltaMaster ETL oder nach ähnlicher Logik aufgebaut ist.

Voraussetzungen und Bestandteile

Folgende allgemeine Voraussetzungen müssen gegeben sein:

• Datenbanksystem MS SQL Server (ab Version 2012)
• Konfiguriertes Datenbank-Mail-Profil (nicht Bestandteil dieses Blogs)
• SQL-Agent als Automatisierungskomponente
• Logging-Informationen im Datenbanksystem
• Datenmodell auf Basis von DeltaMaster ETL (optional, wenn auf Teilprozesse verzichtet werden kann)

Die Lösung besteht lediglich aus zwei Komponenten:

1. Steuertabelle für Prozesse und allgemeine Voreinstellungen

• T_S_Maintain_Email

2. ein bis drei SQL-Prozeduren für den Versand der Benachrichtigungen (je nach Kundenwunsch)

• P_APP_Send_Error_Mail
• P_APP_Send_Success_Mail – optional
• P_APP_Send_Warning_Mail – optional

In diesem Beitrag gehen wir exemplarisch nur auf die Benachrichtigung im Fehlerfall ein. Die Prozeduren für das Verschicken von Warn- und Erfolgsmeldungen können mit identischer Struktur erstellt werden, lediglich mit anderer Definition des E-Mail Betreffs und des Inhalts. Benachrichtigungen im Fehlerfall gehören häufig zu den Anforderungen in unseren Projekten.

T_S_Maintain_Email

Die Steuertabelle enthält die zentralen Definitionen der zu erzeugenden E-Mails für den Betreff und Empfängerkreis sowie den Benachrichtigungsstatus des Prozesses (aktiv/inaktiv).

Als allgemeinen Projektstandard und damit „Grundstock“ sind folgende Einträge zu empfehlen:

Abbildung 1: Übersicht der konfigurierten Prozesse

Die Spalte „ID“ dient zur späteren Adressierung des gewünschten Prozessschritts und entspricht damit dem Übergabeparameter der Prozeduren. Das Feld „Beschreibung“ ist lediglich rein informativer Natur und verbessert die Lesbarkeit auf Datenbankebene.

P_APP_Send_xxx_Mail

Die zweite Komponente ist eine SQL-Prozedur, die sich um die Aufbereitung und Sammlung der Informationen kümmert, sowie um die Formatierung und den Versand der Benachrichtigungen pro definiertem Prozessschritt.

Für den in Abbildung 1 dargestellten Grundstock an Prozessen sind die Informationsquellen bereits vorhanden:

1. 1. Transformation -> über Log Summary/Details von DM-ETL
   2. Datenvalidierung -> über Validation Log von DM-ETL
   3. Allgemeines Monitoring -> über SQL-Server Systemtabellen
   4. Datenimport -> Standardfunktion und Dateisystem

Für individuelle Erweiterungen muss man sich lediglich die Frage stellen: Existiert für meinen Teilprozess eine entsprechende Log-Information und kann vom SQL-Server aus darauf zugegriffen werden?

Wenn die Antwort „Ja“ lautet, bietet es sich auch hier an, eine Benachrichtigung für den verantwortlichen Personenkreis einzurichten.

Der schematische Ablauf der SQL-Prozedur kann wie folgt zusammengefasst werden:

1. 1. Infotext zur Bedienung erstellen
   2. Ermitteln des „Callers“, von wo aus die Benachrichtigung zur Laufzeit aufgerufen wird
   3. Allgemeine Benachrichtigungsparameter aus der Steuertabelle lesen
   4. notwendige Log-Information je Prozessschritt sammeln und den Mailinhalt aufbereiten
   5. E-Mail verschicken

(Den allgemeinen SQL-Code betrachten wir an dieser Stelle nicht näher, da es sich um gängige praktische Anwendung von T-SQL handelt. Bei Bedarf stellen wir gerne ein Skript für die Einrichtung der E-Mailbenachrichtigung zur Verfügung.)

Schauen wir uns zunächst das Ergebnis auf einem beliebigen Kundensystem mit verschiedenen zeitgesteuerten Prozessen in Abbildung 2 an.

Abbildung 2: Jobübersicht

Für die Überwachung müssen in jedem Auftrag ein oder mehrere Schritte (je nach Komplexität der Teilprozesse) zum Senden der E-Mails hinzugefügt werden. Der Aufruf und damit auch die Vervielfältigung der Benachrichtigung über beliebige Teilprozesse sind dabei immer gleich.

Abbildung 3: Exemplarischer Aufruf, um Informationen aus dem Ausführungsverlauf zu lesen

Durch zentrale Prozeduren sind die Abhängigkeiten bei Änderungen transparenter und das Risiko von ungewünschten Nebeneffekten deutlich geringer.

Das Ergebnis im Postfach der Empfänger lässt sich der Abbildung 4 entnehmen.

Abbildung 4: Beispielhafter Posteingang

Schaut man sich die einzelnen Ergebnisse im Detail an, erkennt man deutlich die standardisierten und die individuellen Teile der Lösung: Die Inhalte variieren, sind jedoch strukturell und aus Perspektive des DWHs einheitlich – und somit für jeden Bedarfsfall flexibel anpass- und erweiterbar.

Abbildung 5: Beispiele

Tipps und Tricks

Bei der Einrichtung der Benachrichtigungen gibt es ein paar technische Kniffe, die die Lösung besonders zweckmäßig machen.

Trick 1: Individualisierung des Betreffs

Wie kann man bereits im Betreff den Benachrichtigungsgrund und den Ursprung dem/der Empfänger*in mitteilen? Diese Anpassung des Betreffs ist wichtig, da durch das gezeigte Monitoring unter bestimmten Bedingungen relativ viele E-Mails generiert werden können.

Der Benachrichtigungsgrund ist kein Problem, da dieser in der Steuertabelle definiert ist. Aber was ist mit dem Ursprung? Hier muss der E-Mail mitgeteilt werden, durch welchen SQL-Agentjob sie gerade erzeugt wird. Die Lösung ist die Systemfunktion APP_NAME(). Diese liefert bei Ausführung den Anwendungsnamen der aktuellen Sitzung zurück, sofern diese Eigenschaft von der Anwendung festgelegt ist.

Abbildung 6: Ergebnis der Funktion in SSMS

Führt man die Funktion innerhalb eines SQL-Agentjobs als dynamisches SQL aus, enthält das Ergebnis die aktuelle JobID.

--ermitteln des Callers (welcher Job bin ich gerade selbst?)
	SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
	EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT

Dadurch ist die Quelle, also der gerade ausführende Job, mit einer einfachen String-Operation auflösbar und das Problem gelöst.

SELECT @jobname = MAX(jv.name) FROM  msdb.dbo.sysjobs_view jv WHERE jv.job_id = @jobID

Kleiner Nachteil: Führt man die Prozedur zu Testzwecken selbst im Management Studio aus, so wird der Jobname immer leer sein.

Trick 2: Benachrichtigungen über fehlende Datenlieferungen

Können die System-Betreuenden auch über fehlende Datenlieferungen benachrichtigt werden? Das ist vor allem bei Importprozessen sinnvoll, die auf Verzeichnissen „lauschen“ und nur im Bedarfsfall wirklich Daten importieren.

Mit der undokumentierten Systemprozedur xp_dirtree kann direkt aus SQL heraus das Dateisystem durchsucht werden.

Der Aufruf sieht wie folgt aus:

--Dateinamen dynamisch ermitteln
			CREATE TABLE #DirectoryTree (
				id int IDENTITY(1,1)
				,subdirectory NVARCHAR(512)
				,depth INT
				,isfile BIT);

			INSERT #DirectoryTree (subdirectory,depth,isfile)
			EXEC master.sys.xp_dirtree @directorypath,1,1;

Mit Übergabe des Suchpfads und einer entsprechenden Wildcard, kann anschließend in SQL direkt auf Ergebnisse im Dateisystem reagiert werden.

SELECT @filename = subdirectory
			FROM #DirectoryTree
			WHERE isfile = 1 
			AND subdirectory LIKE @filename_wildcard

Final muss der Aufruf der Benachrichtigung innerhalb des SQL-Agentjobs um die notwendigen Parameter erweitert werden:

Abbildung 7: Übergabeparameter für die Suchen im Dateisystem

Wichtig: Voraussetzung für den Einsatz von xp_dirtree ist, dass aufrufende User Mitglied der Serverrolle sysadmin sind. Außerdem sei darauf hingewiesen, dass undokumentierte Features von Microsoft unter Umständen ohne jeglichen Hinweis in künftigen Server-Versionen entfernt werden.

Das lässt sich als Tabelle innerhalb einer HTML-formatierten E-Mail lösen.

Abbildung 8: Code-Beispiel für die Ausgabe einer in HTML definierten Tabelle

Damit sind die notwendigen Log-Informationen aufbereitet und können mit Hilfe der SQL-Server Systemprozedur versendet werden:

EXEC msdb.dbo.sp_send_dbmail 
		@recipients= @EmailRecipient,
		@subject = @EmailSubject,
		@body = @TableHTML,  
		@body_format = 'HTML';

Fazit

Für automatische Benachrichtigungen zur Überwachung eines Datenbankservers im täglichen Betrieb empfehlen wir den Einsatz dieser Lösung auf Basis von E-Mails. Das Beispiel ist aus einem Kundenprojekt bei Bissantz entstanden – der Kunde überwacht damit aktuell rund dreißig SQL-Agentjobs mit acht unterschiedlichen Teilprozessen und Quellsystemen. In dem Projekt werden auch Erfolgs- und Warnmeldungen bei bestimmten Konstellationen generiert – und das vollkommen wartungsfrei.