0

This is my first attempt at setting up SSRS, so I apologize if any question appears basic. I've researched as much as I can and am confused about the below things. I have my environment described below as well.

We have a 2 node AG in a WSFC. lets call them sql1 and sql2, with listener of sql-listener. SQL 2016 enterprise. My intent is to have all application connections running reports sent to the secondary node to keep the load off the primary server. Nodes are setup in synchronous commit, read/write primary mode, and readable secondary. Questions:

  1. is Scale out deployment required in a setup like this? I can't seem to get Sql2 to show up on the scale-out screen on Sql1 for me to add. Event viewer is giving errors on Sql2 like "report server windows service cannot connect to the report server database" and The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead."
  2. Is the above error likely caused by using the built in virtual service account and not a domain account? both nodes have the report server db's and are synced in the AG. I have re-setup SSRS using the config manager on both nodes and they are identical in every way except the URL's reflect their local servernames http://sql1:80/ReportServer and http://sql2:80/reportserver. I had also added a new URL with the listenername on both servers, but the issue remains. 3.) the connection string must also be changed as I understand it. I added the applicationintent = readonly and changed the data source to the sql listener. Is that all that is required to change on the connection string to automatically point incoming connections to the secondary node given my AG setup above?
0

Configuring a Scale-Out SSRS instance on Always On is fairly straightforward. You follow the normal Scale-Out deployment steps but when you connect your first and second SSRS instances to the ReportServer database you simply point to the Always On listener.

There is a great blog article here that goes through the steps from start to finish.

You do not specify applicationintent = readonly in the connection string because you want both SSRS instances to connect to the primary ReportServer database. Use that connection string option in the connection strings for your report data sources (if they are in an AG) to redirect report workload to the secondary replica.

Lastly, using the Virtual Account is likely causing your issues as it won't have access to the RS databases. Use a domain service account instead.

  • Thanks for the response! That blog post is what i used to setup SSRS, and the setup itself was very straighforward, but it does seem like the issue I have is the lack of domain account. I do have both instances pointing to the sql listener as well. This is my first exposure to connection strings as well, so I was a bit in the dark about how they work. I'll adjust and use the readonly for the data sources, as the source DB is in the AG as well. – DBA Greg14 Mar 27 at 12:47
  • It is very likely caused by not using a domain service account and hitting network boundary issues. Are you able to have domain accounts provisioned for this service? – HandyD Mar 28 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.