Share via

SQL Server Reporting Services Scale-out in Native mode

  • Article

Here are a list of references for those interested in setting up and configuring a SQL Server Reporting Services (SSRS) native mode instance in scale-out mode.

Concepts Involved

Let's start by confirming an oft repeated warning - Reporting Services is not cluster-aware. Further, adding the Reporting Services component to a clustered instance of SQL Server is going to be counter productive since we're installing software on the cluster that would contend with SQL Server for resources (CPU, memory, network, etc.). Please refer Moving Reporting Services off of a Cluster http://blogs.msdn.com/b/psssql/archive/2015/03/13/moving-reporting-services-off-of-a-cluster.aspx if Reporting Services is installed as part of a clustered instance of SQL Server.

Ideally, Reporting Services should be installed off the SQL cluster nodes with the clustered instance of SQL being a candidate for the Reporting Services catalog database. When there are No other available servers, installing Reporting Services as a non-clustered instance of SQL Server on the nodes involved works but is not recommended due to the performance impact mentioned above.

High Availability (Reporting Services) at http://technet.microsoft.com/en-us/library/bb522745.aspx confirms that and shares how scale-out deployment can help provide uninterrupted service in the event one of the report server instances goes down

Planning

Planning for Scale-Out Deployment at http://technet.microsoft.com/en-us/library/bb630407.aspx covers concepts, and requirements. The architecture is covered in the document Reporting Services Scale-Out Architecture at http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx

Configure a Native Mode Report Server Scale-Out Deployment http://msdn.microsoft.com/en-us/library/ms159114.aspx has references to authoritative documentation for completing the setup.

Setup

These following documents can be followed to setup and configure the scale-out deployment -

How to: Configure a Report Server Scale-Out Deployment (Reporting Services Configuration) at http://technet.microsoft.com/en-us/library/ms159114.aspx

How to: Configure a Report Server on a Network Load Balancing Cluster http://msdn.microsoft.com/en-us/library/cc281307.aspx

Tips

In addition to the steps provided above, a HOSTS file entry for the NLB name mapped to the local IP address of participating servers can reduce network traffic and ensure session affinity as well. This search shares the details Search for NLB and HOSTS entry Also, http://support.microsoft.com/kb/958998 shares this info on that -

===

With load balancing, an HTTP request that is sent to the same computer may be routed to the virtual server and then to a different node. This problem occurs intermittently. If the request is routed to the same node, the request succeeds.

To work around this issue, use one of the following methods:•Change the Hosts file on each node so that requests that go to the virtual node go to the local host instead. For example, you can redirect requests that are destined for the virtual node to an IP address of 127.0.0.1. This operation prevents double hop by restricting the requests from Report Manager to Report Server to the same computer.

•If you have a native Reporting Services installation, configure the <ReportServerUrl> tag to use "localhost" instead of the virtual server.

===

Best Practices

Reporting Services Scale-Out Deployment Best Practices at http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/10/21/reporting-services-scale-out-deployment-best-practices.aspx can be followed to ensure all bases are covered.

Some of the main aspects that need our attention are -

  1. Encryption Keys

  2. View state validation

  3. Ensuring session affinity on NLB solution

  4. HOSTS file entries that aid with session affinity

  5. Choice of SPNs against the service accounts and URLs used

Benefits

When Reporting Services is scaled out, some servers in the deployment can be dedicated for certain activities. "For example, you can enable only the background processing if all report processing is configured as scheduled operations. Similarly, you can run just the Report Server Web service if you only want interactive, on-demand reporting." - this and more is documented in "Turn Reporting Services Features On or Off" at http://msdn.microsoft.com/en-us/library/bb677363(v=sql.110).aspx

Note that this will not help run important reports on one server, and other reports elsewhere - as discussed at http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/ea9aaa29-07ea-4e49-b41f-63be242d853d

Thanks

Hope this is useful in getting the scale out environment up and running to reap the benefits. If there's any area needing more attention or references, please add those topics to the comments below. Thanks for choosing Microsoft.