Is the headline longer than the body? Not now I’ve included the fix for this situation as well as the initial rant. Maybe next time.
Yes, you may have an availability group – well done – and you may have installed SSRS on both servers. But you’ve only set up the reporting application to point to one of those? And you’ve given the link https://<<Listener_Name>>/reports
out to the users? Head/desk. I told you at the time that SSRS doesn’t play nicely with AGs. [Nearly misposted as SSRS doesn’t play nicely with SSRS, which, while valid, isn’t the point here…]
Here’s what you need to do to fix this / make sure it doesn’t happen:
- Install the reports on the active server
- Failover
- Install the reports on the now-active server
- On each server, change the report data source to use the listener as the source, not
(localserver)
- On each server, set up the security group(s) and permissions your users require
[Disclaimer – this worked for this situation. It might not work for yours. Support here is worth what you paid for it. Don’t Drink and DBA.]
Only then will you be able to get away with distributing the AG Listener as the SSRS URL. I said this much at the time… Yes, I know things were a bit…difficult back then, but still. Oh well.
Where’s my LART? Or my coffee? Either will do
Pingback: Reporting Services And Availability Groups – Curated SQL
I am interested in trying this method. I always though ReportServer DB in AG was not supported. I have one question. How do you manage deployment of new reports, or report updates? Do you deploy to current primary, then failover and deploy again?
It’s not. Hence the AG containing only application databases, and the requirement to go through this palaver on both servers. (Although that’s also down to the web componentry.)
Deployment of new reports / updates would, I suspect,involve the same routine. Unless the provider of those updates actually has a better plan…