Running SSRS with the ReportServer and ReportServerTempDB databases has been supported for a while now. If however you want to use scheduled delivery of reports you need to do some extra stuff to make this work.
The first thing that you need to do, if when setting up the SSRS instance you need to move the AG from Replica to Replica to setup the correct permissions within the MSDB database (or manually setup the permissions for SSRS within the MSDB database. The easy solution is to make the SSRS database a member of the db_owner role in msdb. This is because apparently the permission that the SSRS configuration manager gives the SSRS account doesn’t include the ability to add a category to the msdb database, which it needs to do. db_owner was the easy fix in our case, and the risk of SSRS doing something it shouldn’t in MSDB is minimal.
That’ll allow SSRS to create all the needed jobs on each node of the Availability Group.
The next thing you need to do is configure the Availability Group to restart SSRS after the AG fails over. This needs to happen because SSRS doesn’t recreate the jobs when it reconnects to the AG, it only does this on SSRS startup. If you are running SSRS in a farm (which you should be doing for high availability) you only need to restart SSRS on one node of the farm.
This can be easily done with a three line batch file, which you configure as a generic resource in failover cluster manager within the resource group which hosts the availability group. Personally I put this script in c:\scripts with some other stuff.
sc \\ssrs-server stop ReportServer > c:\scripts\restart_ssrs.txt
sc \\ssrs-server start ReportServer >> c:\scripts\restart_ssrs.txt
pause
The first line stops SSRS, the second starts it. The third line pauses the batch file so that it doesn’t complete and quit. If it did that then failover clustering would see that as a failure and a failure trigger a failover (or if you disable that a failed service). And since I like my clusters all green and happy, I pause the batch file so it sits there forever. The > c:\scripts\restar_ssrs.txt is logging the output to a text file.
Yes, I could have done this with PowerShell but it wouldn’t have gotten me anything except for more complex code while I check to see if the service is down, then attempt to start it. And SC works just fine for this.
The last thing you need to do is make the computer accounts for all the members of the availability group members of the local administrators group of the SSRS server. This needs to be done so that the service can be restarted (or in some other way give these accounts access to restart services). It needs to be the computer account of the member nodes, not the computer account of the cluster as the batch files run under the context of the computer.
After that, when an AG failover occurs your SSRS will restart within a minute or two and create any missing jobs.
Now the downside here is that you’ll have a bunch of jobs failing on the secondary replicas, but I’ll take that over a non-working configuration.
Denny
The post Configuring SSRS when ReportServer databases are in an AG appeared first on SQL Server with Mr. Denny.
2 Responses
Many thanks for this Denny! I will be playing with this and hopefully running it.
Regarding the failing jobs, I’ve tweaked SSRS subscription job steps to do some additional logic (basically skip running certain times of day for an otherwise hourly schedule) without any impact. You could probably set up an additional job to enumerate all the SSRS jobs and add the proper replica node check and just have it return instead of executing the report.
Hi Denny I deleted a subscription from SSRS reports. A SQL agent job referenced to the subscription is deleted in Primary. That’s great. But not in Secondary node. So, I failed over to Secondary and restarted SSRS service to see if the referenced SQL job is deleted in Primary(previous secondary node). The referenced subscription job in Agent is not deleted in upcoming Primary node. Do I need to another job to clean up orphaned SSRS subscription job in Agent? Thank you