22.03.2019

Microsoft SQL Server Reporting Services (SSRS): Scale-out zusammen mit AlwaysOn-Clustertechnologie

Ein Kunde wollte gerne seine Reporting Services hochverfügbar bereitstellen, wobei ein besonderes Augenmerk den zeitgesteuerten Abonnements (Subscriptions) galt, welche auch nach einem Ausfall weiter zugestellt werden sollten. Da dieses Problem bestimmt auch für den einen oder anderen Leser von Interesse ist, soll es im Folgenden einschließlich der entwickelten Lösung genauer vorgestellt werden.

Die Problemstellung

Zwei Enterprise-Lizenzen waren vorhanden, also lag es nahe, die Bereitstellung für horizontale Skalierbarkeit zu wählen. Ergänzend zur Verfügbarkeit der Reporting Services wurden auch die Datenbanken hochverfügbar gemacht, indem sie in eine AlwaysOn-Verfügbarkeitsgruppe aufgenommen wurden. Um Lizenzen zu sparen, war die Überlegung, die Reporting Services mit auf die zwei Knoten des AlwaysOn-Clusters zu installieren.

Nun war also der Reporting Service via AlwaysOn Listener erreichbar (DNS Shared Resource), alternativ zu diesem Szenario hätte man den Reporting Service auch auf separate Server installieren können. Hierzu wären aber zwei zusätzliche Enterprise-Lizenzen notwendig gewesen, außerdem hätte man ein geteiltes IP/DNS-via-Network-Load-Balancing-Cluster (NLB) konfigurieren müssen, da es nicht möglich ist, NLB und AlwaysOn auf den gleichen Clusterknoten zu betreiben.

Aus dieser Konstellation ergaben sich allerdings Probleme mit den Subscriptions.

Nach einem Ausfall oder manuellen Verschieben der AlwaysOn-Verfügbarkeitsgruppe, welche die Reporting-Services-Datenbanken (ReportServer und ReportServerTempDB) beinhaltet, wurden die vom Reporting Service angelegten Jobs im SQL Agent nicht mehr ausgeführt.

Dies ist ein bekannter Umstand und wird im Scale-out-Modus normalerweise mit einem Neustart des Report-Service-Diensts des aktiven Knotens behoben. Des Weiteren bereinigt man den ausgefallenen Host, nachdem er wieder online ist, indem man die alten Jobs mit der Kategorie „ReportServer“ löscht.

Dies ist natürlich keine wirkliche Hochverfügbarkeit und deshalb habe ich nach einer Lösung gesucht.

Mehr dazu im folgenden Abschnitt.

Die Lösung

Ich muss nun also auf den Ausfall, das Neustarten oder den manuellen Failover zwecks Wartung oder Ähnlichem reagieren. Hierzu habe ich im Internet auch einige interessante Ansätze gefunden, komme aber doch zu einer eventgesteuerten Lösung.

Wichtig war für mich die Erkenntnis, dass ein Wechsel der Verfügbarkeitsgruppe von primary auf secondary und umgekehrt immer einen Eventlogeintrag im Applikationslog mit der ID 1480 in der folgenden Form erzeugt: „The availability group database "ReportServer" is changing roles from "RESOLVING" to "SECONDARY" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.”

Der aufmerksame Leser wird merken, dass der Eintrag nicht „Primary to Secondary“ lautet, sondern „Resolving to Secondary“.

Ich möchte aber nur auf die Änderungen reagieren, also wenn ein Knoten ein neuer primärer wird oder eben ein ehemals primärer den Zustand auf sekundär wechselt. Diese Erkenntnis wird gleich für das PowerShell-Skript wichtig werden, mit dem wir auf die Zustände „Ausfall“ oder „manueller Failover“ reagieren.

Um dies zu realisieren, habe ich auf beiden Clusterknoten in der SQL-Instanz des ReportServers einen Alarm angelegt, welcher auf die Fehlernummer 1480 reagiert.

Ich filtere hier auf die ReportServerTempDB mit der Option „Raise alert when message contains:“ und dem Text „The availability group database "ReportServerTempDB" is changing roles from“, um Mehrfach-Auslösungen zu vermeiden, da immer beide SSRS-Datenbanken das Event erzeugen.

Als Reaktion wähle ich einen Job aus, welcher das folgende PowerShell-Skript ausführt:

#Prereq:

#Install-module -Name sqlserver -AllowClobber



##Case 1: Switching to primary -> Restart ReportServer-Service ##



if ((Get-EventLog -LogName "Application" | Where-Object {$_.timewritten -gt (get-date).AddMinutes(-15) -and $_.EventID -eq "1480"} | Where-Object {$_.Message -like '*to "PRIMARY"*' -and $_.Message -notlike '*"PRIMARY" to "PRIMARY"*' }).count -gt 0) {



Restart-Service -Name "ReportServer"

}



##Case 2: Switching to secondary -> Delete Jobs ##



if ((Get-EventLog -LogName "Application" | Where-Object {$_.timewritten -gt (get-date).AddMinutes(-15) -and $_.EventID -eq "1480"} | Where-Object {$_.Message -like '*to "SECONDARY"*' -and $_.Message -notlike '*"SECONDARY" to "SECONDARY"*'}).count -gt 0) {



Get-ChildItem -Path "SQLSERVER:\SQL\<Servername Placeholder>\default\jobserver\jobs" | Where-Object {$_.Category -eq "Report Server"} | Remove-Item

}

Das Skript prüft, welcher Wechsel stattgefunden hat (indem es im Eventlog nach der EventID sucht), und startet entweder den Report Service neu oder löscht die Jobs, je nachdem, ob der Wechsel auf primär oder sekundär war. Ausgeschlossen werden alle Fälle, in denen ein Eintrag von primär auf primär oder sekundär auf sekundär erfolgt, hier soll keine Aktion stattfinden. Um das Skript in der Praxis zu verwenden, muss das PowerShell-SQL-Server-Modul installiert werden.

Dies löst vollständig unser Problem des manuellen Failovers, allerdings würden bei einem Ausfall nun auf dem ausgefallenen Server die Jobs zurückbleiben.

Dies habe ich noch schnell über einen Job im SQL Agent gelöst, welcher beim Starten des SQL-Agent-Diensts ausgeführt wird und auch ein PowerShell-Skript ausführt.

Details zum Job:

$Condition_Check = $False

while ($Condition_Check -eq $False){

if (((Invoke-Sqlcmd -Query "select sys.fn_hadr_is_primary_replica ('ReportServer')" -ServerInstance <Servername Placeholder>| select -ExpandProperty Column1).gettype() | select -ExpandProperty name) -ne "Boolean"){

$Condition_Check = $False



}elseif ((Invoke-Sqlcmd -Query "select sys.fn_hadr_is_primary_replica ('ReportServer')" -ServerInstance <Servername Placeholder>| select -ExpandProperty Column1) -eq $False) {



Get-ChildItem -Path "SQLSERVER:\SQL\<Servername Placeholder>\default\jobserver\jobs" | Where-Object {$_.Category -eq "Report Server"} | Remove-Item

$Condition_check = $True

}

}

Das Skript wartet zu Beginn darauf, dass der gerade erst gestartete SQL-Server-Dienst die AlwaysOn-Verfügbarkeitsgruppe wieder in einem Zustand hat, wo geprüft werden kann, ob sie primär oder sekundär ist. Dazu dient der While-Block.

Danach prüft es, ob es sich um die sekundäre oder primäre Replik der AlwaysOn-Gruppe handelt. Falls es sich um die primäre Instanz handelt, passiert nichts, ansonsten werden die Jobs bereinigt.

Somit haben wir alle Fälle abgedeckt und können unseren Report Service hochverfügbar betreiben.

Ergänzen lässt sich dies alles sehr leicht mit E-Mail-Benachrichtigungen, damit der Administrator mitbekommt, dass ein Failover stattgefunden hat.

Frank Tröger
Cloud Architect

Unsere Blogs