June 26, 2019 at 7:30 pm
I am having an issue with my SSRS setup when I failover. Details of my setup are below:
Things work fine, but when I fail over to the secondary node, I can no longer access reports. I suspect that I should not have SSRS installed on more than 1 node, is that what could be causing this problem? there are various errors in the report server logs about "cannot update the database since it is read only", but not sure why it would be trying to update the DB when just launching a report that just reads.
June 26, 2019 at 8:40 pm
The error sounds like the Report Server is pointing to the read only replica of the reporting services databases. The Reporting services database can't be read only, writes do happen to those databases. Read only intent connections can apply to databases used as data sources for reports but not the reporting services databases.
Did you stop and start reporting services after the failover? You may also want to check the steps listed in the Microsoft documentation:
Reporting Services with Always On Availability Groups (SQL Server)
Sue
June 27, 2019 at 12:30 pm
Thank you for the reply. I did read that article when this was setup, and assumed I had understood it correctly, but maybe not. the AG is setup in synchronous commit, with readable secondaries set to yes (not just read intent connections). Read only routing is configured to divert any read intent connections to whatever node is the secondary, so I am not sure how the DB cannot be in read only mode in this setup. Could this be due to the fact that i have a scale out deployment setup unnessesarily? or that I have SSRS service running on both the primary and secondary node? Would it be worth uninstalling SSRS from the secondary node and just keeping 1 report server running using the shared DB?
June 27, 2019 at 2:59 pm
As the article explains, how it's configured depends upon which of the three availability scenarios mentioned in the article were you configuring for - report data sources, report server databases, and report design. Its hard to tell what the availability purpose was but I suspect you don't need the reporting services itself as a scale out deployment. And I have no idea if you stop and start Reporting Services after the failover. The ReportServer database used by the service needs to be the primary so not sure about the how it would always be a read only. That would typically apply with data sources, not the ReportServer database. You have to be able to write to the reporting services databases which is why the service needs to use the reporting services databases on the primary
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply