Friday, January 04, 2013 5:03 PM
I ran into an interesting question from a developer on how to handle subscriptions on 2 instances of SSRS on a cluster. Our setup is an Active - Active 2 node cluster with 2 instances. ON top of this we have 2 instances of SSRS. The second instance of SSRS is a copy of the first one....meaning the same reports are deployed on each instance and query the same db. The reason for this is redundancy and having the possibility of using a web service load balancer.
We have subscriptions setup on SSRS node1 but not node2. In the case I lose node1, I will not have the subscriptions email out daily. Setting up the subscriptions on both nodes would email the end user twice.
Any suggestions on how I would be able to configure subscriptions to be failure proof?
Tuesday, January 08, 2013 2:56 AMModerator
From the MSDN document About SQL Server Agent, we can see that “SQL Server Agent jobs that are running at the time of a failover event on a SQL Server failover cluster instance do not resume after failover to another failover cluster node.” Since Reporting Services subscriptions execute as SQL Server Agent jobs, it is limited by that as well.
A common method to resolve this issue is to script all of these Reporting Services jobs in the primary site and then use these scripts to re-create them on the disaster recovery site (with the jobs disabled); activate these jobs only if the failover occurs. After the failover event occurs, we need to query the subscriptions status on the report server database to find the failed subscriptions, and fire the failed jobs again manually or by custom application on the new failover cluster node.
Hope this helps.
If you have any feedback on our support, please click here
TechNet Community Support
- Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Tuesday, January 15, 2013 1:16 AM