This article touches on three important aspects of the task of administrating SQL Server Reporting Services (SSRS): Planning, documenting and troubleshooting.

SSRS (SQL Server Reporting Services) is a part of the SQL Server package and it has been available since 2005. It includes a rich set of features which are used to consume data from various sources and transform it into web-based graphical representations that can be geared to the requirements of different users by representing data in a variety of formats.

SSRS is used for

• The distribution of Business data
  • Many companies deliver data and aggregations via SSRS to their employees, so they can make decisions and analyze business situations. This type of reports can be viewed in a browser or the users can subscribe to reports and receive them by email.
• Integration with applications
  • As SSRS provides a web service which can be used at application runtime to deliver the ‘freshest’ data to the user, it can be integrated within an application.
• Ad-hoc reporting
  • Users can develop their own reports by using templates in Report Builder.

Since SSRS can unify the data from various data sources, it can be used as a ‘common place’ for different departments that may have their data in different formats or in different types of systems.

SSRS presents the developers with a variety of ways to develop and manage reports, flexible security and subscriptions. The Reporting Services module of SQL Server consists of two components: a report server and a report designer.

Planning and documenting your SSRS projects

If SSRS is used by any data-dependent organization, it must be documented. Proper documentation can not only contribute to the fast recovery in the case of a disaster, but it can also point out potential design, configuration and performance improvements. I highly recommend documenting not only the infrastructural design and configuration, but also the security, user objects and the performance of the SSRS environment.

There is a tremendous difference between the 2005 and 2008 versions of SSRS, which makes the documentation different for these versions.

• Dependencies
  • Starting with SSRS2008, the architecture is not completely dependent on IIS anymore.
• New memory management
  • The report processing uses a file system cache to adapt to memory pressure and the administrator of the SSRS is able to set min and max memory usage targets.
  • In SSRS2005 the reports were memory bound, i.e. a request for a large dataset could cause ‘out of memory exception’ or fail other reports; in SSRS2008 when a report is run, only the first page is rendered and expressions are not rendered until the page which contains them is requested
• GUI
  • SSRS2008 has its own standalone Report Builder

There are several parts of SSRS which should be documented:

• Installation
• Configuration – directories, security
• User objects: data sources, reports, schedules
• Performance and usage of the reports

In the following sections we will go into detail in each documentation aspect.

Installation

SSRS installs as part of SQL Server installation. At that point, it is important to consider and to document the topology and the installation settings. This documentation for the setup and installation of SSRS can well be split into three main topics:

• the topology the single deployment or scale-out deployment (There is also a Sharepoint integration mode, but this is beyond the scope of this article))
• the resources (design for performance from the start and not post-factum),
• the security (password encrypted keys protect the data between the Report Server and the Report databases).

In general terms, there are three key players in a SSRS deployment: the Client, the Report Server and the Report Server Database. We can have many variations in topology, depending on the distribution of these components,

The main considerations for each key player are, of course, performance, scalability and failure tolerance.

In a Single deployment we have a single reporting server, which hosts both the Reporting Service and the Reporting Database. This setup is perfect for development or for evaluation, and even for small environments where the requests sent to the server are not unmanageable.

In the case where the performance is a high priority, we would most likely use the Standard layout of the environment where the Reporting Server and the Report Databases are each on different servers. This is intended to avoid the competition for resources between the workloads carried out by the Reporting Service and the Report Database.

The Report Database is usually very IO intensive (which also affects CPU) and memory demanding (especially in SSRS2005), while the Reporting Service is memory and CPU intensive. This is why it is a good idea to use different servers for each module in larger environments. This setup is called Standard Deployment.

In the case where the performance, scalability and fail tolerance are all important, we must use Scale-out Server deployment. This means that we have a client which sends a request to a set of load-balanced Report Servers, which in turn connect to a database server which hosts the Report Server databases.

Furthermore, to guarantee a full fail tolerance in our environment, we could decide to host our Report Server databases on a clustered SQL Server environment.

Regardless of the topology, it is always a good idea to use your favorite tool to document the setup and planning of the environment during setup. It is a good idea to use a charting tool to produce a drawing of the topology, with machine names, IP addresses, network connection speeds etc. In case of failure we can easily understand what caused it and in case we need more resources we would know where to add them; also, in case of taking on new team members it will be easier for them to get introduced to the system.

Encryption keys

All data is encrypted between the Reporting Services database and the Reporting Services Service. By ‘all data’ I mean connection strings, user names and passwords, connection strings for the SSRS databases, the service account information. Therefore, it is very important to back up the keys for the SSRS installation.

Should a disaster occur, the keys are required in order to connect to the SSRS databases after re-installation.

There are two ways to administer the encryption keys in an SSRS installation: either through the UI of the Reporting Services Configuration Manager or by using the command line utility RSKeyMgmt.exe which ships with SQL Server Reporting Services.

Again, the most important part to remember is to back up the keys in a safe place with a strong password and to document the procedure before a disaster happens.

Configuration

The configuration files for the SSRS

There are several configuration files which come with the 2008 version of SSRS:

IRSReportServer.config	It contains ReportManager and ReportServer web settings. This is the main configuration file, and some of its contents are exposed through the UI of the Reporting Services Configuration Manager and through the UI of the Properties Tab in the SSMS of the Report Server.
RSSrvPolicy.config	It contains policy settings for the Report Server Web Service.
RSMgrPolicy.config	It contains policies for the Report Manager web application.
Web.config	It contains ASP.NET settings for the Report Manager and the Report Server.
ReportingServicesServices.exe.config	It contains trace and logging settings for the Report Server Service.
RSReportDesigner.config	It contains settings for the Report Designer application.
RSPreviewPolicy.config	It contains security policies for the server extensions.

What about the config files for 2005?

The policy settings of SSRS2005 are located in RSWebApplication.config. SSRS2008 does not use this file, but saves the configurations instead in RSSrvPolicy.config and RSMgrPolicy.config.

As usual, it is important to document all the changes to the config files, and to backup all the config files to a secure location on a separate server or in a file repository.

Here are the locations of the config files in SSRS 2008:

ReportingServicesService.exe.config	%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer\bin
rsmgrpolicy.config	%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportManager
rsreportserver.config	%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer
rssrvpolicy.config	%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer
RSWebApplication.config	%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportManager
Web.config	%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportManager
web.config	%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer

Also, here is an example of a T-SQL script which will give you the opportunity to read and parse the config files.

Keep in mind that this is just a sample script and you will have to change the path to the config file of interest and also you will need to write your parsing command as shown below (by using the OPENXML command).

Writing a set of scripts for parsing all config files and options is beyond the scope of this article, but I hope this script will give you some ideas and a good starting point.

There is also an object in the ReportServer database which gives information about the configuration.

You may execute it by running exec dbo.GetAllConfigurationInfo in the context of the ReportServer database.

Configuration – directories, security

Here is one of the main differences between SSRS2005 and SSRS2008 and up. SSRS2005 relies on an IIS server to manage the security, whereas later versions have their own directory and security handling.

Backing up the ReportServer database and the master key, as well as the above mentioned config files should be sufficient in order to recover from a disaster; however, do not forget to document the topology and the purpose of each server and each SSRS instance.

Here is a script which you can run to document the directories and the permissions for them in your ReportServer instance:

In SSRS 2008 we can run the following simple query to find out what user-defined objects reside in our Reporting Server instance:

The Type column contains the following items:

1. Folder
2. Report (.rdl)
3. .XML
4. Link Report
5. Data Source (.rds)
6. model

In SSRS2008R2 there were 2 more types added:

1. Shared Dataset
2. Report Part

User objects: data sources, reports, schedules

The Reporting Services are based on two databases hosted on a SQL Server instance: Report Server database and Report Server Temp Database.

Generally, each instance of SQL Server can have Reporting Services installed (hence, there can be multiple instances of Reporting Services on the same machine), and it is up to the designer of the environment to decide what the names of the underlying databases will be.

The Report Server database contains all user generated objects (report definitions, data sources with encrypted credentials etc.), some metadata about the execution and performance of the reports, snapshots and some query results, schedules.

The Report Server Temp Database contains session and execution data, as well as cached reports.

Having said this, there are plenty of considerations for performance of the Report Server databases – the considerations are not too far behind from the general performance considerations of any other database (keep enough files, but not too many; use disk spindles and IO performance in a smart way; backup often, even the Temp database).

Here is a query which can help you look through the user created objects in our ReportServer. Just replace the Type in the search predicate with the type id of the object you are interested in ( look in the list of types above).

Performance and usage of the reports

I’ve already mentioned that the Report Server database contains metadata about execution and performance of the reports.

Here is a query which gives you some performance statistics about your ReportServer instance:

And here is a useful query which allows you to look for some text in the report definitions – the ones that use ad-hoc queries. Just make sure you replace the text in the last line of the query with the text you are looking for.

Note that the definition of the ad-hoc query is stored in the Catalog table as a image datatype (in the [Content] column), so you have to convert it into something more appropriate – VARBINARY(MAX) – and then cast it as an XML, which you can parse and then query.

So far we have covered SSRS installation, design and configuration and I also mentioned some possible ways to work with metadata about user objects.

Now I would like to go into the troubleshooting of performance issues with user objects, and specifically the methodology of approaching problems with Reporting Services.

Troubleshooting:

There are several places where we can get information about SSRS errors when we need to troubleshoot problems.

One place to start is the Report Server Service Trace logs.

First, I have to mention that there were a few different log files in SSRS2005, but in SSRS2008 onwards all log entries were consolidated into one text file.

In SSRS2005 the following files were used:

• ReportServerService_<timestamp>.log,
• ReportServerWebApp_<timestamp>.log,
• ReportServer_<timestamp>.log,
• ReportServerService_main_<timestamp>.log.

In SSRS2008 and later all trace log data was merged into one file:

• ReportServerService_<timestamp>.log.

The file / files can be found in the \Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles folder.

As mentioned above, the config file for the Report Server Service Trace files is located in: \Program Files\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer\bin foler, in the ReportingServicesService.exe.config.

There is no UI available for changing the configuration, but if some changes are needed, then a simple text editor will do the job.

The Trace logs roll over a period of 14 days, but this setting can be easily changed in the config file, as well as file size limits, tracing levels etc. For more information, look in BOL.

In the Trace files we find information about the Server, OS version, memory and number of processors; also SSRS version, events logged in the App log, exceptions thrown, low resource warnings, HTTP header, stack trace, debug trace and information about SOAP envelopes sent and received.

There is plenty of information available, which makes the Trace Logs file / files a good place to start debugging.

I would suggest that you enable the Remote Errors, so you can capture information about errors happening on remote servers. Here is a great article on how to do this. (http://msdn.microsoft.com/en-us/library/aa337165.aspx) Also, it is a good idea to look into the Report Server Excecution logs. This is a broad topic, but here is a link which will get you started (http://msdn.microsoft.com/en-us/library/ms159110%28v=SQL.90%29.aspx).

Keep in mind also that SSRS ships with a set of Performance counters in Perfmon in the MSRS Web Service and MSRS Windows Service category.

The detailed explanation of the counter sets is not in the scope of this article, but it is a good idea for the Report Server administrator to be familiar with them.

Some more considerations:

• Always be on the watch for the CPU and Memory usage of the reports and the Report Server itself.
• Always look at the IO as the biggest bottleneck (unless you run on SSDs, but even then you should be on the watch for adding good indexes).
• Keep an eye on the concurrency of scheduling. In the RSReportServer.config file there is a MaxQueueThreads parameter which defines the concurrency for scheduling and delivery. 0 means that the server will determine the best value.
• If you still need to get information about the performance of the queries going through the Report Server databases, don’t forget the Profiler.
• I highly recommend enabling the Report Server HTTP log in SSRS2008 and up. This is a great feature which logs all HTTP request and responses which are handled by the Report Server. The feature is available only in SSRS2008 and up, and it is not enabled by default. To enable this feature, you have to look into the ReportServerService.config file.

Summary

In this article we started by exploring the purpose and structure of SQL Server Reporting Services, then we moved on to the configuration possibilities, then we looked at the inner workings of the user objects and their performance. And towards the end of this article we went over some important performance troubleshooting considerations.

I hope that your work as a SSRS administrator has just become a bit easier after reading this article. The scripts provided in the article should help you document and troubleshoot your system.

Recently I developed a set of reports which are to be rendered in SSMS as Custom reports towards the Report Server database. The reports give configuration, security and performance information. Of course, they can be used for overviewing, troubleshooting and documenting the system since they can be executed on demand or even saved and reviewed at a later time.