SQL Server: E-Mail-Benachrichtigungen per PowerShell einrichten

Wie sonst auch in der IT ist ein Datenbankserver nicht frei von Fehlern oder Problemen. Um schnell auf Fehler reagieren zu können, ist es notwendig, schnell über Störungen des Betriebs informiert zu werden. Innerhalb des SQL Servers übernimmt der SQL Server Agent (nicht in der Express Edition vorhanden) diese Aufgabe.

Vorbereitungen

Damit der SQL Server Agent Benachrichtigungen versenden kann, muss “Datenbank-Mail” eingerichtet sein. Weitere Informationen zu Einrichtung von Datenbank-Mail findet man im Technet unter https://technet.microsoft.com/de-de/library/ms175887(v=sql.105).aspx. Ich plane auch zum Thema “Einrichtung von Datenbank-Mail über Powershell” noch einen Beitrag.

Des Weiteren muss der SQL Server Agent gestartet sein (Dienst-Starttyp Automatisch) und für den Mailversand vorbereitet. https://msdn.microsoft.com/de-de/library/ms186358.aspx

Verbindungsaufbau

Zunächst muss ein Verbindungsobjekt zur SQL-Server-Instanz angelegt werden. Hier sind die Unterschiede zwischen der Standardinstanz und einer benannten Instanz zu beachten.

Import-module sqlps -DisableNameChecking 

# Herstellen der Verbindung zum SQL Server

$sqlServer = 'SERVERNAME\INSTANZNAME' # bei benannten Instanzen
$sqlServer = 'SERVERNAME' # Bei der Standardinstanz
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$server = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($sqlServer)
$Job = $server.JobServer

Anlegen eines Operators

Operatoren sind im SQL Server Platzhalter für einzelne Personen oder Gruppen, an die Benachrichtigungen gesendet werden können. Für jeden Operator kann man definieren, für welche Aufträge und Fehler eine Benachrichtigung erfolgen soll. Hier soll nun ein Operator mit dem Namen MeinName erstellt werden.

$Name = "MeinName"
$Mail = "Mein.Name@Test-adresse.de"
$operator = New-Object Microsoft.SqlServer.Management.Smo.Agent.Operator -ArgumentList $Job, $Name
$operator.EmailAddress = $Mail
$operator.Create()

Wenn man mehrere Operatoren erstellen möchte, kann man den Codeabschnitt mehrfach hintereinander mit verschiedenen Werten für $Name und $Mail ausführen.

Warnungen erstellen

Damit ein Operator auch Benachrichtigungen zu Fehlern oder Störungen erhalten kann, müssen hierfür zunächst die gewünschten Warnungen einmalig erstellt werden. Im Beispiel werden für die Fehlerschweregrade 17 – 25 und für die Fehlernummern 823 – 825 die entsprechenden Warnungen angelegt.

for($i=17; $i -le 25;$i++)
{
    if(!($Job.Alerts.Contains("Fehler Level $i")))
    {
        $Warnung = New-Object Microsoft.SqlServer.Management.Smo.Agent.Alert -ArgumentList $Job, "Fehler Level $i"
        $Warnung.Severity = $i
        $Warnung.IsEnabled
        $Warnung.Create()
        Write-Host -ForegroundColor Green "Warnung Fehler Level $i angelegt"
    }
  }
if(!($Job.Alerts.Contains("823")))
{
      $Warnung = New-Object Microsoft.SqlServer.Management.Smo.Agent.Alert -ArgumentList $Job, "823"
      $Warnung.MessageID = "823"
      $Warnung.IsEnabled
      $Warnung.Create()
      Write-Host -ForegroundColor Green "Warnung 823 angelegt"
}
if(!($Job.Alerts.Contains("824")))
{
      $Warnung = New-Object Microsoft.SqlServer.Management.Smo.Agent.Alert -ArgumentList $Job, "824"
      $Warnung.MessageID = "824"
      $Warnung.IsEnabled
      $Warnung.Create()
      Write-Host -ForegroundColor Green "Warnung 824 angelegt"
}
if(!($Job.Alerts.Contains("825")))
{
      $Warnung = New-Object Microsoft.SqlServer.Management.Smo.Agent.Alert -ArgumentList $Job, "825"
      $Warnung.MessageID = "825"
      $Warnung.IsEnabled
      $Warnung.Create()
      Write-Host -ForegroundColor Green "Warnung 825 angelegt"
}

Operator mit Warnungen „verheiraten“

Nachdem man nun den Operator erstellt hat und alle Warnungen angelegt sind, muss man nur noch definieren, für welche Warnungen der Operator eine E-Mail bekommen soll. Im Beispiel sollen alle vorhandenen Operatoren für alle erstellten Warnungen eine Benachrichtigung erhalten. Dies lässt sich in der PowerShell sehr einfach über zwei Foreach-Schleifen realisieren.

# Auslesen aller vorhandenen Warnungen und Operatoren
$Alarme = $job.Alerts
$Operatoren = $Job.Operators

# Verbinden von allen Warnungen mit allen Operatoren

foreach($Operator in $Operatoren)
{
    foreach($Alarm in $Alarme)
    {
       $Alarm.AddNotification($Operator.Name,[Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail)
       Write-Host -ForegroundColor Green "Warung $alarm mit $operator verbunden"
    }
}

Wenn schon Verbindungen zwischen Operator und Warnung bestehen, wird ein Fehler ausgelöst. Dieser Fehler beeinflusst aber die weitere Funktion des Skripts aber nicht. Aus Gründen der besseren Lesbarkeit habe ich hier auf eine entsprechende Fehlerbehandlung verzichtet.

Das Skript wurde mit PowerShell 5 auf Windows Server 2012 R2 und Windows Server 2016 gegen SQL Server 2012, 2014 und 2016 getestet.