Reporting Services on Always On Availability servers.

Question

Hi All,

To avoid extra licensing charges, we want to have the Reporting services to be on the 2 node Always On cluster nodes (Install Reporting services on each node). Has anyone this kind of setup ?.

• Install SQL Server 2019 Reporting Services on each node.
• Have the SQL Server Listener setup.
• Have the 2 Reporting Services databases added to Always On availability group.
• Configure Read-Only routing on the Always On group.
• Change connection strings on the Reports to add "ApplicationIntent=ReadOnly" phrase.

My question is, do we need to install reporting services on both nodes ?. We would want to run the Reporting Services on the replica node whenever the AG fails over. I know there are links out there but any feed back appreciated.

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

https://www.sqlshack.com/configure-sql-server-reporting-services-databases-in-sql-server-always-on-availability-groups/

Answer 1

Hi @Carlos D,

A Reporting Services report server is a stateless server that stores application data, content, properties, and session information in two SQL Server relational databases. As such, the best way to ensure the availability of Reporting Services functionality is to do the following actions:

Use the high availability features of the SQL Server Database Engine to maximize the uptime of the report server databases. If you configure a Database Engine instance to run in a failover cluster, you can select that instance when you create a report server database.

• Use SQL Server Always On availability groups with the Reporting Services databases and for data sources, as possible. For more information, see Reporting Services with Always On Availability Groups.

Configure multiple report servers to run in a scale-out deployment, where all the servers share a single report server database. You can deploy multiple report server instances, preferably on different servers, in a scale-out deployment to help provide uninterrupted service in the event one of the report server instances goes down.

A scale-out deployment provides a way to share a database. If one report server goes down, other servers in the same deployment continue to work.

Reporting Services isn't cluster-aware. By itself, a scale-out deployment doesn't provide load balancing; it doesn't detect the processing loads on a report server and route new processing requests to the least busy server.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Thanks for stating the obvious. I already posted content of what you copied and pasted above from Microsoft link in my original post. My question was, is there a benefit to installing reporting services on both nodes but I know now the answer is no. What I wanted to do was that run Reporting server services on the replica node and not on the primary node regardless of failovers.