2

I am reading msdn article on using SSRS with AG and have few doubts on setting up the same:

  • Server A - Will host Report Server services with scale out deployment
  • Server N1 - Node 1 with ReportServer and ReportServerTempdb database serving as primary replica
  • Server N2 - Node 2 is secondary replica of the ReportServer and ReportServerTempdb

Above nodes will be part of multi-subnet cluster with N1 in DC1 and N2 in DC2.

I am thinking to create the listener first with 2 IPs for those multi-subnet so that we can use same while installing reporting services and point the listener name in instance and it's report server database to it so that it is aware of primary replica even after fail-over.

Is my above understanding correct? Is that how it's suppose to work?

Secondly, document says automatic fail-over is not supported even when I use sync mode between N1 and N2? Is that true? And why would that be the case only with SSRS? Also if it is, can we automate the fail-over or does it have to be manual every-time since I am thinking data source application string can use the option MultiSubnetFailover=true ?

Appreciate your inputs.

Improve this question

2 Answers 2

Reset to default
3

The reason the documentation states that automatic failover is not supported is that there are post-failover tasks that must be carried out to ensure the Report Server is completely online. See this section for information on exactly what tasks need to be completed. Essentially it boils down to needing to stop the SQL Agent on the old primary and restart the Report Server service.

I am thinking to create the listener first with 2ips for those multi subnet so that we can use same while installing reporting services and point the listener name in instance and it's report server database to it so that it is aware of primary replica even after failover. Is my above understanding correct? Is that how it's suppose to work?

Yes, build your AG and configure the listener first, then when you'r configuring the report server you can point it at the listener.

Secondly, document says automatic failover is not supported even when I use sync mode between N1 and N2 ? Is that true? And why would that be the case only with SSRs? Also if it is , can we automate the faiover or does it have to be manual everytime since I am thinking data source application string can use the option multi subnet failover = true ?

There seems to be some crossover here where you're talking about report data sources and Report Server databases. RS databases are the databases used by report server to manage and maintain the report server. They're typically named ReportServer and ReportServerTempDB. These databases do not support automatic failover because there are post-failover tasks you must complete to bring the service back to a 100% normal state.

These tasks can be automated through scripts easily enough, but that is is what the documentation means by not supported. For your report data sources, these only need AG specific parameters in the connection string (such as MultiSubnetFailover or ApplicationIntent) if the source databases are in an AG as well. If they are then adding those keys to your connection strings will ensure the reports continue working post-failover. They should handle failover automatically.

Something to note is this line from the documentation:

The use of MultiSubnetFailover, with the report server databases, is not supported.

What this means for you, given you have a multi-subnet cluster, is that you should set the RegisterAllProvidersIP setting to 0. This setting is 1 by default and means that each IP assigned to the listener is registered in DNS. If MultiSubnetFailover is enabled, the client will try to connect to each IP in parallel and return the connection from the first IP that responds. As this setting is not supported by the RS service for connections to the RS databases, after a failover you will likely encounter timeouts trying to connect.

The solution is to set RegisterAllProvidersIP to 0 and set a low TTL for that host record. This ensures that only the active IP is registered with DNS for the listener, and after failover the new IP is registered with DNS. This means new connections to the listener use the correct IP and you avoid timeouts.

Improve this answer
5
  • Thank you for a really great answer. You made that picture much clear from what i got confused with msdn. Also might not be related but if i configure AG in prior, when choosing listener those 2 ips's are different than the IP of multi subnet cluster. Also in above scenario will i be able to configure scale out deployment?
    – BeginnerDBA
    Commented Feb 4, 2020 at 6:35
  • The listener requires additional IPs on top of the cluster. In a two-node, two-subnet cluster with a single AG listener, you would need 6 IPs (1 per node, 1 in each subnet for the cluster and one in each subnet for the listener). It doesn't matter when you create the AG, those IP requirements don't change.
    – HandyD
    Commented Feb 4, 2020 at 22:34
  • For scale-out, yes you can use an AG in a scale-out deployment. I've actually configured an environment previously that ran a scale-out SSRS deployment using each node in the cluster as an SSRS server as well.
    – HandyD
    Commented Feb 4, 2020 at 22:35
  • @HandyD- I did a setup for Node1 and Node2 under AG for report server and reportservertempdb. To initiate automatic failover I stopped SQL service on node1 and it seems both the databases automatically failed over to NOde 2. Now I am confused which automatic failover is msdn ref: here?
    – BeginnerDBA
    Commented Feb 9, 2020 at 22:52
  • @BeginnerDBA The doco doesn't state that automatic failover won't work, simply that its not supported and the reason is there are extra steps needed for the new node to work 100% with the RS service (setting up scheduled jobs etc). If your AG is configured for automatic failover, the DBs will failover automatically, but you need to implement the post-failover tasks before it can be considered 100% online on the new node.
    – HandyD
    Commented Feb 10, 2020 at 21:59
1

why would that be the case only with SSRS?

As addition note to the Handy's answer, one of the main reason, SSRS dependencies are not only in ReportServer and ReportServerTempDB databases, but also on SQL Server Agent which host all the subscription configuration of the SSRS and which would not be a scope of AG replication.

So, you got covered ReportServer and ReportServerTempDB databases only by AG synchronization not the SQL Agent Jobs that are created by SSRS for each subscription (following is the sample screenshot of Agent Job that created by SSRS).

However, SSRS will re-create all the jobs on new primary replica (after fail-over event) but you must restart SSRS Service for this to happen.

enter image description here

Improve this answer
3
  • Thanks and do you think scale out deployment is possible for multi-subnet scenario as above?
    – BeginnerDBA
    Commented Feb 4, 2020 at 7:09
  • @BeginnerDBA, I haven't tested that so, but logically it should do the same (re-creating SQL Jobs on SQL Instance wherever SSRS service connected to). Probably, the host (in scale-out deployment) which responsible for the subscriptions would do this trick which you need try it out in your DEV environment
    – Shekar Kola
    Commented Feb 4, 2020 at 10:23
  • @Shekhar - I had one of the subscription job on Node 1 which after failover got created automatically on Node 2 without any SSRS service restart. So I am not sure where is this MSDN doc going to
    – BeginnerDBA
    Commented Feb 10, 2020 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.