Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSRS 2008 - How to handle subscriptions on 2 instances of SSRS on a cluster?

Answered SSRS 2008 - How to handle subscriptions on 2 instances of SSRS on a cluster?

  • Friday, January 04, 2013 5:03 PM
     
     
    Sign In to Vote
    0

    Hi all,

       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?

     

All Replies

  • Tuesday, January 08, 2013 2:56 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Vinman2,

    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.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here

    Mike Yin
    TechNet Community Support