by Mark Kromer
As part of our series on SQL Server BI for SQL Server DBAs, I am going to now focus on architectures that you can implement and what you need to know about scale-out for SQL Server Reporting Services (SSRS) for SQL Server 2008 R2. Previously, we talking about SSAS & data warehouse DBA management. Now let’s talk a little bit about how you can scale SSRS. Note that we are talking about scaling SSRS, not high availability. I am going to show an architecture that can also include database mirroring and/or Windows Clustering to build-in high availability and redundancy into the architecture. But keep in mind that in SQL Server-land, scaling out and high availability are 2 different and distinct things. Additionally, the SSRS service itself is not “cluster-aware”, meaning that when we cluster SSRS for HA, we are going to actually cluster the Report Server Catalog database, not SSRS as a service itself.
The first diagram that I am showing here is a sample of a SQL Server 2008 Reporting Services architecture from the Microsoft DPE team. In this architecture, the primary idea to take away is that you will create scalability with SSRS by utilizing multiple (n) number of SSRS servers running the Reporting Services Windows service. The SQL databases storing the database for the report server catalog is then housed on a separate SQL Server instance, which can then also have high-availability configuration as shown in the diagram. Note that in both a scale-out mode as well as a native SSRS configuration, you should to configure the ReportServerTempDB using configurations recommended in the SQL CAT whitepaper below and this Technet article. Setting up the TempDB properly such as the recommended methods in that Technet whitepaper are helpful in improving performance on any SSRS architecture. In their tests, Microsoft used “10 separate 1 GB files and a 1-GB transaction log in an effort to improve the parallelism for disk I/O operation” for ReportingServerTempDB.
In the next SSRS scale-out diagram below from the SQL CAT team, we add more scalability and load-balancing capabilities by introducing a network load balancer (NLB) from the user connections which then ties into a single RDSB (report server catalog DB). By using a native SQL Server HA capability such as database mirroring or SQL clustering with Windows clustering, you could add HA at the report catalog DB as well. These are both very common and I have seen these implemented successfully at a number of customers.
You get the same separation from your primary data sources and you also get the advantage of being able to scale to your customer reporting demands with multiple servers running SSRS.
It is important to note that these scale-out architectures are not absolutely necessary to scale SSRS. The SQL 2008 version of SSRS does not rely on IIS and has its own stripped-down version of the HTTP server that is uses to service requests. So on its own, SSRS is more scalable natively than the SQL 2005 version of SSRS, provided that you at least consider isolating SSRS running on a box separate from your primary database server, at a minimum.
