SQL Server Availability Groups and SQL Server Reporting Services – A Brief Rant Triggered By Someone Failing Something Over Without Understanding The Full Implications, Or, Pay Attention To Your DBA Because He Knows This Stuff And That’s What You Pay Him For

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:

  1. Install the reports on the active server
  2. Failover
  3. Install the reports on the now-active server
  4. On each server, change the report data source to use the listener as the source, not (localserver)
  5. 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

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

3 Responses to SQL Server Availability Groups and SQL Server Reporting Services – A Brief Rant Triggered By Someone Failing Something Over Without Understanding The Full Implications, Or, Pay Attention To Your DBA Because He Knows This Stuff And That’s What You Pay Him For

  1. Pingback: Reporting Services And Availability Groups – Curated SQL

  2. gbargsley says:

    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?

    • thomasrushton says:

      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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.