Reporting Services performance problems can often be narrowed down to report design or resource constraints and sometimes a combination of the two. In this post I’ll review the process of scaling-out Reporting Services so server resource constraints can be shared across multiple servers instead of relying on a single server for all report processing.

If you are new to understanding Reporting Services architecture it is important to understand the general steps that are occurring behind the scenes with Reporting Services. From the beginning of a user request the steps that happen are:

• Client makes a request to the report server
• Report server then makes a request to the report catalog
• Report catalog sends back to report server report definition and tells the report server which data sources to hit.
• Report server request data from source database
• Source database sends data and the report server renders report metadata and data for the client

Understanding Scaling-Out Architecture

If you have ever installed installed SQL Server and the BI stack locally on your machine for testing purposes than you’ve likely done a single server install. This means you have SQL Server, Reporting Services, Analysis Services and Integration Services engines all running on the same machine.

While this might be fine running in a local testing environment on your machine this obviously doesn’t scale well on a server that requires thousands of users to connect. In an environment that requires this many users a scaled-out deployment is ideal and can support a heavier workload.

In a scaled-out deployment multiple Report Server instances are installed that all use the same repository or ReportServer database. So when a thousand client machines make a request to the ReportServer the report rendering can be split the requests across multiple servers to do the work. They still must use the same repository database because they still want to view the same report metadata but the report processing should be helped. The screenshot below depicts a scaled-out deployment with three instances of Reporting Services installed and a single SQL Server database repository to return back report metadata. This is an example of a common setup but not the only way to do this of course.

With this approach we could have our west coast customers using a URL that points to Server A, mid west customers using a URL that points to Server B, and all east coast customer use Server C.

Another approach to this is to use a Network Load Balancer (NLB). The idea behind a Network Load Balancer is that you provide a single virtual URL to the report server and the NLB sends the user to the Report Server that is least utilized. So if you have two servers and Server A is only 20 percent utilized and Server B is 80 percent utilized then the NLB would choose Server A for the next incoming user instead of you manually providing a URL for each user. The Network Load Balancer can either be a physical device on your network for making these decisions or you can enable the feature in Windows Server.

Setup and Configuration

The setup and configuration for scaling Reporting Services into a Web Farm is actually fairly easy. In fact to oversimplify it here is an overview of the steps required:

• Install SQL Server (Database Engine) on one server.
• Install Reporting Services on two servers without configuring either.
• Pick one of the Report Server installations and configure it using the Reporting Services Configuration Manager. Go through each page of the configuration manager except the Execution Account and E-mail Setting, which are optional setting depending on you required setup.
• Configure the second Report Server but use same report catalog database as the first and set URLs. So on the database page ensure it points to the same database that was setup during the setup of the first Report Server

• Go back to first Report Server you configured and click Add Server under the Scale-out Deployment page to join the instances as shown below.

Without overcomplicating this that is really all it takes. This would be done for as many Report Servers as you would like to add to the farm. Hope this helps.