Nach den ersten zwei Folgen meiner Blogserie SQL Server 2014, Serverseitige Datenbankprogrammierung und Zugriffsberechtigungen verwalten, geht es in dieser dritten Folge darum, wie Sie direkt über den SQL Server aus Ihrer Datenbankanwendung heraus E-Mails versenden.

Immer wieder ergibt sich aus Datenbankanwendungen heraus die Notwendigkeit, eine Nachricht in irgendeiner Form jemandem zukommen zu lassen. Dafür bietet sich Datenbank-E-Mail geradezu an. Datenbank-E-Mail nutzt direkt SMTP, daher ist keine weitere Installation auf dem Server erforderlich. Mittels Datenbank-E-Mail können aus einer Anwendung heraus Mails versendet werden. Zusätzlich ist es die Voraussetzung für den Versand von administrativen Benachrichtigungen, zum Beispiel wenn ein Server-Agent-Job fehlschlägt.

Für den Einsatz von Datenbank-E-Mail müssen folgende Voraussetzungen gegeben sein:

  • Ein SMTP-Server für den Versand von Mails steht zur Verfügung.
  • Ein Konto zum Versenden von Mails ist auf dem SMTP-Server eingerichtet.
  • Beim Einsatz einer Firewall muss der Port für SMTP für den SQL Server ausgehend geöffnet sein, damit Mails an den SMTP-Server weitergeleitet und damit versendet werden können.
  • Und: Sie müssen eine lizenzierte Edition des SQL Server einsetzen, denn Datenbank-E-Mail steht bei der kostenlosen Express-Edition leider nicht zur Verfügung.

Die Vorteile für datenbankaffine Entwickler liegen auf der Hand. Sämtliche Abläufe bezüglich des Mailversands finden sich nicht in irgendwelchen Protokolldateien auf dem Mailserver, sondern sind, da Sie in eigenen Tabellen in der Systemdatenbank MSDB gespeichert werden, einfach über SQL-Anweisungen auswertbar. Zusätzlich erleichtert wird die Recherche im Bedarfsfall, wenn es wieder einmal heißt: „Ich habe dazu aber kein Mail erhalten.“, indem beim Versand eines Mails die entsprechende ID beim auslösenden Datensatz mit gespeichert wird. Ich erläutere, was ich damit meine, anhand eines einfachen Beispiels. Bei einer Statusänderung einer in der Datenbank gespeicherten Bestellung soll eine Nachricht an eine bestimmte Empfängergruppe gesendet werden. Dazu wird in der Anwendung automatisiert eine Nachricht über Datenbank-E-Mail versendet. Die dazu verwendete Systemprozedur liefert die ID des generierten Mails zurück. Diese ID trägt man in den Datensatz der Bestellung in der entsprechenden Tabelle ein. Zu einem späteren Zeitpunkt kann man ganz einfach anhand dieser ID nachverfolgen, ob und wann dieses Mail versendet worden ist.

Datenbank-E-Mail muss vor der erstmaligen Verwendung auf dem SQL Server einmalig konfiguriert werden. Dies lässt sich sehr einfach über den entsprechenden Assistenten aus dem SQL Server Management Studio heraus erledigen. Für Puristen besteht natürlich auch die Möglichkeit, die Konfiguration über den Aufruf entsprechender Systemprozeduren zu bewerkstelligen. Datenbank-E-Mail finden Sie im Objekt-Explorer des Management Studios unter Verwaltung. Den Konfigurations-Assistenten rufen Sie dort über das Kontextmenü mit dem Befehl Datenbank-E-Mail konfigurieren auf. Sie müssen dazu ein paar Schritte erledigen, bei der Erstkonfiguration verwenden Sie die Auswahl Datenbank-E-Mail einrichten, dann wird keiner der notwendigen Schritte ausgelassen und Datenbank-E-Mail auch am Server aktiviert. Einzelne Konfigurationen können Sie später über die weiteren Optionen erweitern und anpassen.

Für den Aufbau:

  • Konten und Profile: Für den Einsatz von Datenbank-E-Mail benötigen Sie in erster Linie Profile. Diese werden für den Versand verwendet. Einem Profil muss zumindest ein Konto zugeordnet sein. In einem Konto wird das für den Versand zu verwendende SMTP-Konto konfiguriert. Das Konto wird für den Versand verwendet, wenn das Profil aufgerufen wird. Der Sinn, einem Profil mehrere Konnten zuzuordnen liegt daran, dass ein weiteres Konto verwendet werden kann, wenn der Versand über das erste Konto nicht erfolgreich gewesen ist. Ich trachte daher in der Praxis immer darauf, dass sich die Konten auf unterschiedlichen SMTP-Servern befinden. Dadurch ist ein Versand in der Regel auch dann möglich, wenn der Hauptserver nicht verfügbar sein sollte. Wenn Sie möchten, können Sie ein derartiges Ersatzkonto auch mehreren Profilen zuordnen. Beim Versand wird aber immer nur das Profil angegeben, welches dann die dahinter definierten Konten in der festgelegten Reihenfolge für den Versand verwendet.
  • Profilsicherheit: Einzelne Profile können als öffentliche oder private Profile eingerichtet werden. Öffentliche Profile können von allen Benutzern, für die Datenbank-E-Mail zulässig ist, verwendet werden. Mit privaten Profilen kann jeweils immer nur der zugeordnete Benutzer Mails versenden. Ein öffentliches Profil kann zusätzlich als Standardprofil eingerichtet werden. Gibt es ein solches, wird dieses automatisch verwendet, wenn beim Versand kein Profil angegeben wird.
  • Systemparameter: Die verschiedensten allgemeinen Einstellungen sind über die Systemparameter konfigurierbar. Dies sind zum Beispiel die Anzahl der Wiederholungsversuche beim Senden, die maximale Größe oder verbotene Dateierweiterungen für Dateianhänge.

Alle Einstellungen werden in Systemtabellen in der Systemdatenbank MSDB gespeichert und können über SQL-Anweisungen bei Bedarf auch ausgelesen werden. Eine Übersicht zeigt die nachfolgende Tabelle.

Tabelle Inhalt
sysmail_account In dieser Tabelle werden alle erstellten Konten gespeichert.
sysmail_configuration Die im letzten Konfigurationsschritt eingestellten Systemparameter finden sich in dieser Tabelle.
sysmail_principalprofile Diese Tabelle enthält die Einstellungen zur Profil­sicher­heit. Hier finden sich zum Beispiel in der Spalte principal_sid die SID des Benutzers eines privaten Profils und in der Spalte is_default die Angabe, ob es sich um ein Standardprofil handelt.
sysmail_profile Diese Tabelle enthält die erstellten Profile.
sysmail_profileaccount Diese Tabelle speichert die Zuordnungen von Konten zu Profilen.
sysmail_server Die Informationen bezüglich der verwendeten SMTP-Server für jedes Konto finden sich in dieser Tabelle.

 

Nach erfolgter Konfiguration können Sie gleich einmal eine Test-E-Mail senden. Dies ist direkt aus dem Objekt-Explorer über den Befehl Test-E-Mail senden… möglich. Sie können dabei ein Profil auswählen, eine oder mehrere Zieladressen (mit Semikolon voneinander getrennt) eintragen und einen Betreff sowie Text eingeben.

Abbildung Test-E-Mail senden von SRVSQL2014

Der Versand von E-Mails erfolgt über die Systemprozedur sp_send_dbmail, die auch in der Datenbank MSDB zu finden ist. Wichtig ist, dass Benutzer, die E-Mails darüber versenden sollen, in dieser Datenbank die Mitgliedschaft in der Rolle DatabaseMailUserRole bekommen. Mitglieder der Datenbankrolle db_owner und der Serverrolle sysadmin haben diese Berech­ti­gung ohnehin. Die Prozedur sp_send_dbmail hat eine Reihe an Parametern, die wichtigsten sehen Sie im nachfolgenden Aufrufbeispiel.

DECLARE @mailid int
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sql_server_2014',
                             @recipients = 'klemens@konopasek.at',
                              @subject = 'Datenbank-E-Mail Test',
                              @body = 'Text der Nachricht ...',
                              @mailitem_id = @mailid OUTPUT

Der Parameter @mailitem_id liefert die ID der Nachricht zurück, die Sie bei Bedarf an entsprechender Stelle für spätere Recherchen abspeichern können. Sie finden dazu alle Informationen in der Tabelle sysmail_mailitems.

SELECT mailitem_id, recipients, subject, sent_date
 FROM msdb.dbo.sysmail_mailitems;

 

Sie finden in der Tabelle sysmail_mailitems noch weitere Informationen zum E-Mail-Versand; so neben dem Status auch die Angabe, über welches Profil und welches Konto der tatsächliche Versand erfolgt ist. Dadurch können Sie beispielsweise nachverfolgen, wann ein alternatives Konto zum Einsatz kam und ein Hauptkonto nicht funktioniert hat oder der zuständige SMTP-Server nicht erreichbar war. Von praktischer Bedeutung ist vor allem der Inhalt der Spalte sent_status der Tabelle sysmail_mailitems. Diese kann einen Wert von 0 bis 3 enthalten. Diese stehen für:

  • 0 = nicht gesendet
  • 1 = gesendet
  • 2 = Fehler beim Senden
  • 3 = Wiederholungsversuch steht an

In Klartext bekommen Sie den Status auch gemeldet, wenn Sie anstelle der Tabelle sysmail_mailitems die fast gleichnamige Sicht sysmail_allitems verwenden. Dies liefert an Mitglieder der Serverrolle sysadmin Informationen über alle Mails, für alle anderen über die Mails, deren Versand man selber beauftragt hat.

Wenn Sie aber lediglich wissen wollen, ob eine Mail versendet worden ist oder nicht, kön­nen Sie dazu auch die Spalte sent_date auswerten. Diese ist nämlich anfangs NULL und erhält erst bei erfolgreichem Versand einen Datumswert. Eine weitere Alternative stellen die Sichten sysmail_sentitems und sysmail_faileditems dar. Diese liefern aber nur die Mails mit dem entsprechenden Status. Dies bedeutet, man müsste unter Umständen zwei Anweisungen zur korrekten Bestimmung des Status absetzen.

Datenbank-E-Mail ist auch die Basis, wenn Sie administrative E-Mail-Warnungen versenden möchten. Typischerweise ist es sinnvoll, dass sich Administratoren auf diese Art und Weise benachrichtigen lassen, wenn bei Aufträgen des SQL Server-Agents Fehler auftreten. Dies sind zum Beispiel Aufträge, die zu festgelegten Zeiten bestimmte Datenbanken sichern.

Für den Einsatz von Warnmeldungen sind folgende Schritte notwendig:

  • Datenbank-Email ist die Basis für Warnmeldungen. Ist dies noch nicht konfiguriert, muss dies erfolgen. Sinnvollerweise richten Sie für Warnmeldungen außerdem ein eigenes Profil ein. Dieses muss weder öffentlich noch das Standardprofil sein.
  • Ein Mailprofil muss für den SQL Server-Agent aktiviert werden, damit dieser Mails versenden kann.
  • Operatoren müssen für den Server-Agent als Empfänger eingerichtet werden.
  • Warnmeldungen sind bei den einzelnen Aufträgen zu konfigurieren.

Alle diese Schritte lassen sich sehr einfach im Objekt-Explorer des Management Studios erledigen. Zum Aktivieren von Mail für den SQL Server-Agent öffnen Sie einfach seine Eigenschaften über das Kontextmenü direkt im Objekt-Explorer. Wechseln Sie dort auf die Seite Warnungssystem. Dort wählen Sie die Option Mailprofil aktivieren und wählen das dafür vorgesehene Mailprofil auf der Liste aus. Als Mailsystem ist Datenbank-E-Mail vorausgewählt und in der Regel auch die einzige Auswahlmöglichkeit. Mehr ist an dieser Stelle nicht zu tun und Sie können die Änderungen schon übernehmen.

Damit ist der Absender für die Warnmeldungen schon festgelegt, nun müssen noch entsprechende Empfänger eingerichtet werden. Wie bereits erwähnt, benötigen Sie dazu sogenannte Operatoren. Diese sind im Objekt-Explorer unter dem Server-Agent zu finden. Einen neuen Operator legen Sie über das Kontextmenü, entweder für den Ordner SQL Server-Agent oder direkt für den Ordner Operatoren an. Auf der Seite Allgemein vergeben Sie einen Namen und geben Sie gewünschte Empfänger-E-Mailadresse ein.

Abschließend bleibt nur mehr das Zuweisen des Operators zu den gewünschten Aufträgen. Dazu öffnen Sie die Eigenschaften des Auftrages und wechseln zur Seite Benachrichtigungen. Dort können Sie unter der Option Auszuführende Aktionen beim Abschluss des Auftrags die Auswahl E-Mail aktivieren und danach den Operator als Empfänger auswählen und eines der drei Ereignisse auszuwählen:

  • Bei erfolgreicher Auftragsausführung
  • Bei Auftragsfehler
  • Beim Abschluss des Auftrags

Ich setze Datenbank-E-Mail gerne ein, um aus meinen Applikationen Mails zu versenden. Da alle Informationen zu versendeten Mails aus der Datenbank MSDB auslesbar sind, können diese auch einfach im Frontend der betreffenden Anwendungen angezeigt werden. Generell sind Recherchen über versendete Mail sehr einfach, was für mich einen weiteren großen Vorteil darstellt. Zusätzlich verwende ich Datenbank-E-Mail für den Versand von administrativen Warnmeldungen. Damit ist Datenbank-E-Mail aus meiner Sicht sowohl für Entwickler als auch für Administratoren ein unverzichtbares Werkzeug.

Zahlreiche weitere Informationen, Beschreibungen zu Vorgansweisen sowie Tipps und Tricks finden Sie im aktuellen Buch SQL Server 2014 – der schnelle Einstieg.

Konopasek, SQL Server 2014Im nächsten Beitrag wird es über Datenbanksicherungen bzw. Backup und Recovery gehen. Bleiben Sie dabei, bis bald!