SSRS on HA Windows Clustering

Question

SSRS on HA Windows Clustering

romanista_db

SSC Enthusiast

Points: 121
More actions
November 15, 2018 at 2:43 pm

#368949

Was asked to assist with a SSRS failover issue that's on windows failover/always on availability groups.
Automatic seeding was used to replicate the databases during setup. Synchronous commit is the availability mode and the Secondary is not readable.
Now when it is on the primary, everything works on the reporting server web url. When they practice the failover, when we try to sign into the report server url - I get this message.
The service is not available.The report server isn’t configured properly. Contact your system administrator to resolve the issue. System administrators: The report server can’t access or use the encryption key. You might need to add the server to the scale-out group, reimport encrypted content, or delete all encrypted content and generate a new encryption key
It's pointing at the encryption key, now when you setup a SSRS on this type of environment are you supposed to restore the encryption key from the primary onto the secondary?
So my proposed solution was backup the encrypted key on the primary server and restore on secondary server. Not sure if that's the right approach, would appreciate if anyone could shed some light onto this.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gamleur84 Default port Points: 1475 More actions · Answer 1

There is two aspects when it comes to HA for SSRS. The first one the data tier, which is the ReportServer database. You say the database is a member of an Availability Group with Synchronous commit. Then the second part is the Web tier.

First question, do you have a shared ReportServer database or two distinct databases ?
Did you configure a SSRS scaled-out deployment for HA ?
Do you have a load balancer in front of your SSRS web app ?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Here are a couple of articles that may help:

">https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-with-always-on-availability-groups-sql-server?view=sql-server-2017

https://blog.dbi-services.com/dealing-with-ssrs-subscription-schedules-in-alwayson-environment/
http://pietervanhove.azurewebsites.net/?p=513

As one of the above articles points out - you need to consider how you are going to work with subscriptions. The ideal solution appears to be cycling reporting services and allowing it to rebuild the agent jobs - but that requires notification of the failover event to trigger that process - which probably can be done with an agent job that checks for the failover and if it occurred execute a powershell script to restart the service(s).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

romanista_db SSC Enthusiast Points: 121 More actions · Answer 3

Thanks guys for your insight.

I did solve the issue prior to receiving your assistance.
My steps were:
- backup the databases and encryption key on primary reporting server
- failed over primary to secondary
- restored encryption key on reporting services configuration

After that I did run into another issue that pointed at a permission issue. The account had Sys Admin role and still wasn't working. Figured out that it didn't have read/write permissions on the database files. Once I gave it local admin on the vm, everything worked.