Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series

Authors: Denny Lee, Lukasz Pawlowski
Contributors: John Gallardo, Robert Bruckner, Dean Kalanquin
Technical Reviewers: Burzin Patel

This technical note is part of the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series  which provides general guidance on how to set up, implement, and optimize an enterprise scale-out architecture for your Microsoft® SQL Server® Reporting Services environment. This note provides guidance for both SQL Server 2005 and 2008 Reporting Services. The focus of this technical note is Scale-Out Deployment Best Practices – the Reporting Services Windows®/Web Services servers that provide your reports.

Architecture

Figure 1 shows a typical scale-out Reporting Services environment; as noted by the red box, the focus of this technical note is scale-out deployment.

Figure 1: Reporting Services scale-out architecture

Report Catalog Sizing

As noted in the previous technical note of this series, Report Server Catalog Best Practices, the report server catalogs are an integral part of your Reporting Services solution. When you begin the process of building your Reporting Services scale-out deployment, you will need to appropriately scale your report server catalogs to handle the additional workload. In addition to the concepts discussed in the aforementioned technical note, a key consideration is Report Catalog sizing; the two report server catalogs (RSDB and RSTempDB) have different sizing requirements.

Report Server (RSDB) Database Size

The size of this database typically varies by the number of reports published and the number of history snapshots associated with each report. A general rule of thumb is that a moderate-sized report definition will take about 100-200 KB of disk space, which is larger than the actual size of the RDL. This is because Reporting Services will persist both the RDL and the compiled binary representation to improve report execution performance. In the case of history snapshot sizes, this is a function of the amount of data that is in the report (i.e., the larger your report and datasets, the larger your snapshot). A general rule of thumb is that Reporting Services has an approximate 5:1 compression ratio; therefore if your report has 10 MB of data, then the snapshot will be about 2 MB in size. Note that reports that do not perform aggregations will be slightly larger than reports that do.

Report Server TempDB (RSTempDB) Database Size

The size of this database varies depending on the number of users who are concurrently using the report servers. Each live report execution generates a report snapshot that is persisted in the RSTempDB for the duration of the user’s session. Because of the high degree of variability of the number of users who are accessing your report servers at any one time, your sizing estimate should be based on the maximum number of concurrent users that access your Reporting Services environment. A general rule of thumb here is that you will typically see a maximum of 10-20% concurrency of your user base.

For example, if you have 1,000 users, then you can have up to 200 concurrent users. If most of these users are accessing your 10-MB report, then you will need to have at least 400 MB of storage when taking compression into account. Of course, as users are no longer querying for reports and/or report sessions are timing out, the space will be reclaimed and made available for new users. But at the same time, to size properly, you will want to make your calculation based on the maximum number of concurrent users.

Enable File System Snapshots for SQL Server 2005 Reporting Services

Please note that this is applicable for SQL Server 2005 Reporting Services, not SQL Server 2008 SQL Server 2008 Reporting Services. As noted in Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned, this feature did not provide the scale of performance benefit (more on this later) for SQL Server 2008 Reporting Services.

Recall that the report server uses snapshot data stored in RSTempDB to render reports. Therefore RSTempDB gets filled up fast, because there are many transactions performed to keep report consistency (e.g., if I get the first page of a report, we need a snapshot of the second page that is taken from the same data as the first). Because of this concern for consistency, it is then important for Reporting Services to cache all of this information. To reduce the number of transactions being placed on your RSTempDB, use the File System (FS) snapshots feature instead. With it enabled, snapshot data persists on the local Reporting Services server. Therefore, this reduces the network traffic from the Reporting Services server to the catalog, which also reduces the number and size of transactions within the catalog. For more information about this feature, see the Planning for Scalability and Performance with Reporting Services white paper.

For Reporting Services to efficiently access this snapshot data, it is spread over a number of chunks – logical divisions of smaller size. During regular operations, the report server may need to access the chunks multiple times to satisfy user requests. For example, a particular chunk may have been removed from server memory, so the report server needs to fetch it again from RSTempDB. By default, the server must query RSTempDB each time it needs to get a snapshot chunk. As user load increases due to report complexity or concurrency, the number of transactions to RSTempDB also increases. This can cause performance degradation on RSTempDB. To mitigate this, Reporting Services provides configuration options to create file system chunks (i.e., file system snapshots), which act as a cache for the snapshot chunks on the node that created the chunk. This reduction in load on RSTempDB allows your Report Catalog to scale better.

Now that the report data is stored on the file system, it is a requirement to use a load balancing solution to affinitize user sessions to their report server node (more on this later). The slight drawback to this feature is that if you were to lose a reporting server, you would also lose the file system data cache that corresponds to that reporting server. However, the metadata is still available in RSDB and the original data is in the reporting data. Your users would simply connect to a different report server and make a new request for this report, experiencing slower initial query performance before the file system snapshots kick in.

Therefore, for large-scale SQL Server 2005 Reporting Services deployments, we recommend you turn on the file system storage of snapshot chunks. To do that, you must set WebServiceUseFileShareStorage and WindowsServiceUseFileShareStorage to True. You can control the location of the files using the FileShareStorageLocation configuration property. These settings are available in the file rsreportserver.config located under the report server installation folder.

Note that if you enable this feature, it will require more disk space than your standard Internet Information Services (IIS) configuration, because of the large amount of data that will be written onto disk. (For more information, see the Report Catalog sizing information in the previous section.)

Why Not File System Snapshots for SQL Server 2008 Reporting Services?

There are a number of changes and optimizations to SQL Server 2008 Reporting Services, including better utilization of memory and of the file system. Enabling file system snapshots in SQL Server 2005 Reporting Services was an advantage because unless this feature was enabled, a majority of the transactions required hitting the RSTempDB. But SQL Server 2008 Reporting Services caches a lot of this data into memory. Meanwhile the data is continually persisted to the Report Catalogs so that the local file system acts as a write-through cache, regardless of whether file system snapshots are enabled. This was done to ensure stability in case of incorrect load balancing affinity or gaps (e.g., secondary output streams such as CSS style sheets in HTML, report images retrieved in HTML) in report snapshots.

The design in SQL Server 2005 Reporting Services was that it generally would not materialize the original datasets in the report snapshot, except for particular cases. This was possible since the entire report was calculated in SQL Server 2005 Reporting Services when the first page was requested, and the report contents were persisted in a output-format independent way. On the other hand, SQL Server 2008 Reporting Services does not precalculate everything on the initial request – therefore, it has to always serialize all datasets so that it can calculate report contents as needed later. There are still certain parts in SQL Server 2008 Reporting Services that once calculated (e.g., group/sort/filter/aggregates) are persisted and not recalculated. However, many parts of a report (e.g., text box values, style values) are calculated on demand instead of precalculated and stored.

Another reason file system snapshots do not have as profound impact for SQL Server 2008 Reporting Services is that when reports are executed, the Reporting Services on-demand engine will initially retrieve all of the data for your report and write this data into the RSTempDB, to achieve a consistent data snapshot. It will then do the calculations and groups required for the first page. But subsequent calls are more efficient and have much less impact than SQL Server 2005 Reporting Services (e.g., page 2 of the report) because the on-demand engine will only access a subset of the data instead of requesting for the entire dataset. Note that the data is materialized in the initial request (e.g., render first page) when all dataset requirements are executed. Later session requests (e.g., navigate to second page, export to Microsoft Excel®) will use the data already materialized in the session snapshot.

This observation was also noted in the Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned technical note. Saying this, you may still want to test the efficacy of this feature within your environment, because there could be variables that allow this feature to help improve performance – but it certainly doesn’t always have benefits as it did for SQL Server 2005 Reporting Services.

Turn on Cache Execution

As you may have noticed, so far we have a recurring theme concerning the effective use of memory and desire to have minimal impact on the file system. After all, a key component for scalability is the ability to reduce I/O. Of course, CPU and memory are extremely important for scalability, but often it is the disk I/O that is slowing down your performance. A powerful feature to reduce I/O is to enable the cache execution feature of your reports. By default, when you deploy your Reporting Services reports, it will be a live execution report – i.e., upon report execution, Reporting Services will query the snapshot or your data source. At a minimum, this means that for every report execution some I/O occurs to provide your users with data. By using cache execution, Reporting Services caches a temporary copy of this report into memory that expires after a set number of minutes. Report executions during this time period request data from memory instead of disk, thus reducing I/O. Even if you set your cache timeout for your report to every five minutes, you will still see an 80% reduction in I/O for your report.

To configure cache execution, after you deploy your reports to your report server, you will need to configure each report (i.e., there is no global setting) to cache a temporary copy of the report. Note that you will want to set your reports to use stored credentials instead of Windows integrated security, in order for the cache execution to work effectively. Do not forget to set up your Reporting Services keys to ensure encryption of the stored credentials.

Load Balance Your Network

It is very important to load balance your many client connections to your Reporting Services servers. We recommend the use of a hardware network load balancer (NLB) that allows your client connections to balance across your Reporting Services servers, as noted in Figure 2.

Figure 2: Load balancing your network

We also recommend that you use cookie persistence (refer to your NLB guide to set this up) to preserve the client-to-Reporting Services server connection – i.e., to affinitize the client to the Reporting Services server. You can consider IP affinity, but this can overload under browser-based or proxy connections. Note that you typically will round-robin the initial connections to minimize the load on your NLB. Subsequent queries will have affinity to allow the clients to make use of the Reporting Services local file cache.

Because your Reporting Services servers are making heavy use of the network, you may want to consider also placing additional NIC cards for your Reporting Services servers. In enterprise Reporting Services environments, an important task will be to profile your network resources so you can determine where your network resource bottleneck is. If you are running into network bottlenecks, a setup to consider if you want to scale up your network load is to have two NIC cards, one for the browser traffic and one for the database traffic.

Isolate Your Workloads

Within your enterprise Reporting Services environment, you will typically have two types of users – one set that assesses scheduled reports and another set that reviews the reports in an interactive manner. A common profile differentiation between these two types of reports is that users accessing interactive reports will execute their reports often in order to drill up, drill down, filter, etc. On the other hand, scheduled reports typically will process against much larger sources of data (which involves a considerable hit on the system to produce the reports; hence scheduling will reduce the load) or have a much larger audience (where the load created by the concurrency of a large number of users to query the same report can be reduced by creating the report beforehand). Whether you are dealing with interactive users or scheduled report users, you have a situation where your users produce two different types of profiles as noted in Figure 3.

Figure 3: Isolating your workloads

Therefore, it is suggested that within your scale-out deployment, you also isolate your workloads so that you have one set of servers devoted to working with your interactive users (Interaction) while another set of servers work on scheduled reports (Scheduling). As implied in Figure 3, doing so will provide you with a more predictable workload because of the different resource profile that is used by these different report users. Workload isolation will also allow you to isolate any performance issues associated with these two types of reporting users; that way, interactive users and scheduled report users are not interfering with each other.

Note that ad-hoc report users (i.e., users who use Report Builder) are a different class of interactive users. Because these users can build whatever reports desired, there is a high degree of unpredictability in the amount of resources that this class of users will require. In extreme cases, you may want to consider creating a completely separate deployment for these ad-hoc users so that they do not bottleneck your interactive or scheduled report users.

To isolate your workloads, set your scheduling Reporting Services servers to Schedule Events and Report Delivery Enabled, and set your interactive Reporting Services servers to Web Service and HTTP Access Enabled. By default both options are enabled, so make sure only one is enabled on each server when you decide workload isolation is the path you will take.

Additional Notes on Workload Isolation

You can also make these workload isolation changes programmatically through WMI using the Reporting Services WMI Provider Library. You will change the SetServiceState method; for more information, see the SQL Server Books Online > SetServiceState Method.

To help isolate your reporting bottlenecks for scheduled reports (the Scheduled Events and Report Delivery), schedule your reports so they are not being processed in parallel. This way you will be able to isolate performance issues.

Report Data Performance Considerations

With all of these configuration and infrastructure considerations, you will be able to build an optimal scale-out environment for your Reporting Services solution. But while your Reporting Services environment is scaled out, do not forget the impact on your underlying reporting data – i.e., the data source. Here are some of the things you can do to reduce the impact on your report data:

• Limit the dataset size by using query filters.
• If you need to provide large dataset sizes, use alternate mechanisms such as Integration Services to provide an extract that is accessible from the file system, instead of providing a million-page report.
• Limit the number of reports that users can access. This is not just a security concern. Limiting access reduces your maintenance cost by preventing users from creating multiple similar subscriptions and reports.

But even after making the above changes, you may still find yourself placing far too much load on your reporting data. To help resolve these issues, you may want to consider also scaling out your underlying data sources. Figure 4 is an example of a scale-out solution where a set of two Reporting Services servers are assigned to a single Analysis Services server. The underlying reporting data (in this case Analysis Services cubes) is replicated to multiple Analysis Services query servers so that every two Reporting Services servers query only a single Analysis Services instance. Note that to do this you will need to manually create local data source aliases on the reporting servers that will perform this type of server assignment.

Figure 4: Scaling out Analysis Services

These options are outside the scope of a Reporting Services specific technical note, but here are some resources that you may find helpful:

• Deploying a Scalable Shared Database: This feature allows you to deploy multiple read-only versions of your SQL Server database for the purposes of reporting. Scalable shared databases offer a scale-out solution in which multiple servers hold read-only copies of your SQL Server database that users can query thus distributing the query load across multiple databases or servers.

• SQL Server Replication: Providing High Availability using Database Mirroring: This white paper covers the use or database mirroring to increase availability of the replication stream.
• Database Mirroring and Log Shipping: You can use database mirroring and log shipping together to provide both high availability and disaster recovery.
• SQL Server Replication Features: Choose among transactional, merge, or snapshot replication to provide yourself with query replica databases.
• Scale-Out Querying with Analysis Services: This white paper describes how to replicate a read-only Analysis Services database to multiple servers.
• Scale-Out Querying with Analysis Services Using SAN Snapshots: If you have a SAN, you can make use of SAN snapshots to perform the task of metadata replication instead of physically copying the underlying Analysis Services database.
• Scaling out an Analysis Services Solution: In addition to attaching or detaching an Analysis Services database, you can also attach a read-only copy of your Analysis Services database that places the Analysis Services database metadata locally and refers to a remote copy of the database. That is, one copy of a database has metadata distributed to multiple servers so that the formula calculation load can be scaled out to many servers.

Next Steps…

Now, that you’ve scaled out your Reporting Services deployment – there are additional features and configurations that may optimize your Reporting Services environment; for more information, see SSRS Performance Optimization Configurations [link provided when published].