Load Balancing SSRS inside an AlwaysOn Availability Group

Question

We have 3 nodes in an Availability Group in SQL 2014:

Primary Site:

• Server1 (synchronous primary)
• Server2 (synchronous, readable secondary)

DR:

• Server3 (async, nonreadable secondary)

We use Reporting Services and have it installed and licensed on all 3 servers.

When processing reports we use the availability group name to connect to the server, so that when we failover reports will continue to work (e.g. we connect to http://sqlAAGListener/reports). This has been working great for us, but we are getting to the stage where we'd like to balance the reports between the 2 servers on the primary site.

We have tested connecting directly to http://server1/reports and http://server2/reports and all of our reports work just fine on both servers.

MSDN suggests that the right way to do this is to have the report server database as part of the Availability Group (as we currently do), and then running 2 or more Reporting Services instances on separate servers and then put a load balancer (e.g. Windows Load Balancing service) in front of them. I see the advantages of doing this, however we don't have a budget for 2 more servers of Enterprise Edition, so it's not an option.

Instead, can we keep our SQL Connections pointing to the availability group listener, and install Windows Load Balancing on Server1 and Server2 to balance SSRS or will this cause problems?

Answer 1

I wouldn't use Windows Load Balancing to do this, rather use something like an F5 to handle it (if you have one). But your idea is valid, you can LB the reporting services sites in this manner with them pointing to the AG listener on the back side.

Do I need to add an extra network card or anything like that to the server? Will it cause any problems with the Failover Cluster that AAG relies on?

I'm not sure about Windows LB, but using an external network load balancer there is no need to (unless you deliberately want to segregate the traffic, which isn't always a bad thing). - https://dba.stackexchange.com/users/11639