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:
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.
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:
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.