Scale-out SSRS (a brain dump)

So, I just got finished with a SQL Server Reporting Services (SSRS) scale-out deployment, based largely on this step-by-step guide. Things mostly went well, but there were a couple of hiccups I’d like to record before they fly out of my head again.

Note, too, that I am NOT an SSRS guru. Feel free to ping me in the comments with additions and corrections as you see fit, but be kind, or I will beat you with my T-SQL Bat of Vengeance.

First, the scenario

So with SSRS scale-out, you can do a number of different scenarios. Here’s what mine looked like:

  • Data source databases (you know, regular databases) on an Availability Group on Server1 and Server2. This, mind you, doesn’t actually matter, except to note that all the remaining servers are dedicated to SSRS matters only…not data.
  • SSRS services on four servers – SSRS01 through SSRS04 – sitting behind a network load balancer. Note that these servers JUST had SSRS installed, not the actual SQL engine.
  • SSRS databases, in an Availability Group (AG), on two additional instances. Let’s call these servers SSRS_DB01 nd SSRS_DB02*. Note that these servers had the SQL engine installed as well as SSRS.

The point of this is to make sure there’s lots of firepower dedicated to actual report processing, and even more dedicated to the writing and retrieval of report-specific information.  AND, that the report databases themselves have (manual) failover ability, for high availability purposes.

I’m not going to go over the blow-by-blow of putting all of this together. The step-by-step guide does a pretty good job of that.

*Footnote 1:  The actual naming scheme was way worse than this, but what’re you gonna do when you’re not in charge of naming?

Limitations

Note that, while you CAN stick ReportServer and ReportServerTempDB into an AG, and while you could configure that for automatic failover, I DO BELIEVE that you’ll still have to do some manual work – pointing the SSRS services at the right replica – after the failover. So it might as well be manual.

“Reporting Services offers limited support for using Always On availability groups with report server databases. The report server databases can be configured in AG to be part of a replica; however Reporting Services will not automatically use a different replica for the report server databases when a failover occurs.” – Docs.Microsoft.com

Having said that, I have no idea why you can’t point the SSRS services at the AG listener, and have done with it. I’ll look into it, or some helpful soul will contribute in the comments.

The Hang-ups

Here are the bits that I either missed in the guide, or wasn’t made clear at all:

  • All of the SSRS services must run under the same service accounts.  That’s the SSRS services on the SSRS servers (SSRS01 through SSRS04), and the SSRS services on the SSRS database servers (SSRSS_DB01 and SSRSS_DB02).
  • You have to back up the encryption key on the SSRS database server primary – in this case, SSRSS_DB01 – and restore that encryption key to all of the SSRS services.
  • Did you get that? All of them need the same service account, and all of them need the same encryption key. It makes sense after the fact, but I’m not at all used to splitting out SSRS services from their underlying databases.

6 thoughts on “Scale-out SSRS (a brain dump)

  1. Robert L Davis

    I have pointed SSRS at the Listener. You should only put the ReportServer database in the AG. The ReportServer temp database merely needs to exist on all nodes of the AG and does not need to be kept in sync.

  2. Greg

    Also under normal license agreements you have to licence SSRS separately if you install it on its own elsewhere away from the SQL instance.

  3. Nic

    You’ll need to restart the RS services on at least one of the scale-out servers post failover in order for the subscription jobs to be created on the SQL Agent on the new replica (and you should have a process to clean out the subscription jobs on the old machine as they are all going to fail and that’s going to create a lot of monitoring noise).

  4. Pingback: Where on Earth is the SQL Server Reporting Services Config Manager? > The MidnightDBA Star-Times

Comments are closed.