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:
- 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."
- 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?
Best Answer
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.