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?
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.
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.
All SSRS instances have to use the same service account because the encryption key is specific to the service account. Back up the key on original server and restore on all others.
— Robert L Davis (@SQLSoldier) February 6, 2018