Designing a Strategy to Manage Reporting Services

We are really surprised by the uptake of SQL Server Reporting Services (SSRS) by the SQL Server community. Looking at all the books on SSRS in the bookstore, a new one seems to be coming out each month.

Yes, SSRS is a popular product. It provides a powerful platform for meeting the reporting requirements of your organization. It has a complex architecture that leverages a number of technologies such as databases, Internet Information Services (IIS), web services, and other Windows services. It is extensible, so custom assemblies and rendering extensions could also make up your reporting solution.

All in all, it’s a lot for the DBA to manage, from post-installation configuration changes to security management (an important and complex one) to even a scale-out your report servers. So, it is important to design a strategy to manage these core elements of your SSRS solution:

• SSRS configuration

• SSRS servers

• SSRS databases

As with all management aspects, it is important to use the appropriate tools, so make sure you understand both the SRSS architecture and the tool set available.

Note 

See the topics “Report Server Administration Tools” and “Reporting Services Configuration Files” in SQL Server 2005 Books Online.

Managing the SSRS Configuration

SSRS configurations can vary greatly depending on how they have been deployed from default configurations on stand-alone installations to scaled-out report server solutions where you typically need to monitor the server load and respond accordingly.

There has been a global trend recently (just look at the uptake of ITIL training) of recognizing the importance of documenting and managing your infrastructure, so, as always, you should document your policies, change management, and procedures, as well as maintain some kind of run book.

Automating SSRS Operations

We love automating operations in SQL Server 2005. It lets you get away earlier so you can go scuba diving, go drink beers, or go light up the BBQ! You can automate two major types of operations in your SSRS reporting solution:

• Processing and delivery of reports

• Administrative tasks

To automate the processing and delivery of reports, you use the Report Manager, a web-based tool that can be used to both access and administer your reporting solution. Figure 5.2 shows the Report Manager environment.

Figure 5.2: Report Manager

You can use Report Manager to perform the following tasks:

• Search and execute static and parameterized reports.

• Execute ad hoc reports based on a report model through the Report Builder application.

• Subscribe to reports.

• Create report models.

• Create subscriptions that send reports to multiple recipients.

• Create and maintain a folder hierarchy for the easier navigation of reports.

• Configure security.

• Configure report optimization.

• Configure site properties.

For automating other administrative tasks, you generally use the report server script host utility (rs.exe). This utility can run custom Visual Basic scripts that you might write to re-create and can move existing content from one report server to another.

1. Write your script in Visual Basic.

2. Save it as an .rss file.

3. Use rs.exe to execute the script on your target SSRS server.

SSRS Configuration Tools

You can configure your SSRS using a number of different tools. As always, you should evaluate the available tools and the appropriateness of their use in your environment in the context of any operational policies, such as being able to configure your production environment only through scripts.

Don’t forget, however, that the SSRS configuration files are stored in XML format and can be edited manually. In some cases, certain settings can be changed manually only.

Warning 

Of course, you should be careful when manually editing the configuration files. An incorrect change can cause SSRS to fail to start or, in ways worse, use a default value or setting.

There are fundamentally two different ways in which you can manage your SSRS configuration, through the Reporting Services Configuration Manager or through command-line tools.

Reporting Services Configuration Manager

The Reporting Services Configuration Manager allows you to configure a report server deployment. It allows you to perform the following actions:

• View server status.   The Reporting Services Configuration Manager allows you to see the status of your SSRS solution. You can stop and start the report rerver Windows service here.

• Configure virtual directories.   The Reporting Services Configuration Manager allows you to configure the website and the virtual directories used by both report server and Report Manager.

• Configure service identities.   The Reporting Services Configuration Manager allows you to modify the Windows service identity and the Web service identity.

  Note 

  Under Windows Server 2003, the report server web service uses the security identity of the application pool. You can use the Reporting Services Configuration Manager to create new application pools or choose an existing application pool.

• Create and configure the Report Server database.   The Reporting Services Configuration Manager allows you to create, configure, and upgrade the SSRS database.

• Manage encryption keys.   The Reporting Services Configuration Manager allows you to back up, restore, and change the symmetric encryptions used by SSRS to safeguard the sensitive information (such as credentials and database connections information) stored in the reportserver database.

  Tip 

  It is important to protect the encryption key against disclosure or theft.

• Initialize.   The Reporting Services Configuration Manager allows you view the initialization status of your SSRS server. Creating and storing the symmetric key discussed earlier is performed during the initialization process that prepares a SSRS server for encryption operations. The Reporting Service Configuration Manager also allows you to configure a scale-out deployment of your reporting solution by connecting each report server to the shared report server database.

  Note 

  Running multiple report servers in a scale-out deployment requires that all the report server instances use a single, shared reportserver database because it provides internal storage and state information for all report servers that are joined to it.

  Note 

  For more information about initializing report servers, search for the “Initializing a Report Server” topic in SQL Server 2005 Books Online.

• Configure email delivery.   Reporting Services includes a report server email delivery extension that allows report subscribers to get reports delivered to an electronic mailbox. The email delivery extension uses Simple Mail Transport Protocol (SMTP) to deliver the report or notification. You can use the Reporting Services Configuration tool to specify which SMTP server or gateway on your network to use for email delivery.

• Control the execution account.   The Reporting Services Configuration Manager allows you to control the execution account used to perform unattended operations at a very low security level.

  Note 

  Some functionality is disabled or lost if an execution account is not specified.

Figure 5.3 shows the Reporting Services Configuration Manager tool.

Figure 5.3: Reporting Services Configuration Manager

Reporting Services Command-Line Tools

The SSRS command-line tools are useful for scripting, batch processing, and SQL Server Agent jobs. The main SSRS command-line executions tools are as follows:

• rs   As discussed earlier, the script host utility (rs.exe) runs custom Visual Basic scripts against multiple report server instances.

• rsconfig   The rsconfig utility (rsconfig.exe) encrypts and stores connection and account values in the RSReportServer.config file. The rsconfig utility is used for two purposes:

• To modify the database connection information that a report server uses to connect to a report server database

• To configure a special account that the report server uses to log on to a remote database server for unattended report processing

• rskeymgmt   The rskeymgmt utility (rskeymgmt.exe) extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access. The rskeymgmt utility is also used to join report server instances in a scale-out deployment.

  Tip 

  For more information about SSRS scale-out deployments, search for the “How to: Configure a Report Server Scale-Out Deployment (Reporting Services Configuration)” topic in SQL Server 2005 Books Online.

Managing SSRS Servers

Managing SSRS server is all about understanding the technology (easy) and understanding your users (not so easy) and their requirements (even worse). In your organization, different sets of user will have different reporting requirements with respect to report processing load, refresh frequency, delivery mechanism, and caching/snapshot/history strategies. Understanding how these reports are processed and delivered is the important first step in developing a SSRS management strategy. As we said, it’s all about the users!

Figure 5.4 shows the report history options available in Report Manager. Figure 5.5 shows the report snapshot options available in Report Manager.

Figure 5.4: Report History options in Report Manager

Figure 5.5: Report Snapshot options in Report Manager

Tip 

You should identify and pay more attention to reports that have large processing loads, are more frequently requested, or are subscribed to by larger users because they will have a more significant impact on your SSRS reporting solution.

You’ll now learn about your various responsibilities for managing your SSRS databases.

Note 

For more information about running reports from snapshots in SQL Server 2005, search for the “Setting Report Execution Properties” topic in SQL Server 2005 Books Online.

Managing Security

SSRS has its own security subsystem from SQL Server that is stored in the reportserver database, with its own sets of roles, users, and permissions. Make sure you become familiar with these roles to determine whether you will need to add new roles.

Tip 

If your SSRS environment has many users where there is a significant churn, you can benefit from using data-driven subscriptions instead of controlling permissions at the user and group level.

With SSRS, it is easy for developers to give access to data through reports that users would not usually have permissions to in the database. Consequently, as part of your SSRS security management strategy, you should periodically monitor all facets of security in the SSRS architecture, including the following:

• IIS   SSRS relies on IIS virtual directories, so you should engage with your web administrators to make sure they have been correctly configured and secured.

• Unattended report execution   Many SSRS features utilize the unattended report execution capability that relies on specific credentials to access data sources, process reports, and deliver them. On-demand interactive reports have a number of options for controlling the credentials used. You should analyze which users will use the unattended reports versus on-demand reports and establish a credential strategy that minimizes security risks at data sources.

Monitoring SSRS Performance

SSRS reporting solutions tend to represent core business functionality and typically are heavily utilized by both users and management. Consequently, it is important to develop a monitoring strategy to determine usage patterns and capacity plan correctly.

Microsoft has included two performance objects for monitoring SSRS performance:

• MSRS 2005 Web Service   The MSRS 2005 Web Service performance object includes a collection of counters used to track report server processing performance.

  Note 

  The MSRS 2005 Web Service performance object counters are reset whenever ASP.NET stops the Reporting Services web service.

• MSRS Windows Service   The MSRS 2005 Windows Service performance object includes a collection of counters used to track scheduled operations (including subscription and delivery, report execution snapshots, and report history).

Managing Subscriptions

Another powerful feature of SSRS is that users can be given the ability to subscribe to updated reports and receive the reports via a number of different delivery means. The subscription owner manages this kind of subscription. Figure 5.6 shows an example of an email subscription being set up for the “Dili – Unemployment” report.

Figure 5.6: An example of an email subscription in Report Manager

However, obsolete subscriptions might still be running after they are no longer required, which translates to unnecessary processing and can degrade the performance of your SSRS solution. Consequently, you should establish a strategy for keeping track of subscriptions and periodically checking to see whether they are current.

Determining an Appropriate Caching Strategy

SSRS can cache copies of processed reports, which improves performance because the reports don’t need to be regenerated repeatedly. This is particularly useful for large, complex, or frequently accessed reports. When caching reports, you also configure how long the report will be cached for.

Figure 5.7 shows the scheduling interface and options available in SSRS.

Figure 5.7: Configuring report caching in Report Manager

Numerous cached reports with long retentions can adversely affect your SSRS solution if you have not capacity planned your hardware correctly. Consequently, it is important to understand both SSRS’s features and your user’s requirements so as to be able to determine an appropriate caching strategy for your organization. You should periodically review your strategy to ensure it is still optimal.

Note 

For more information about caching reports in SQL Server 2005, search for the “Report Caching in Reporting Services” topic in SQL Server 2005 Books Online.

Determining an Appropriate Scheduling Strategy

The ability to schedule automatic processing of reports was a very welcome feature of SSRS. Scheduling is available from either shared schedules or report-specific schedules. Only authorized users and roles have the ability to create shared or individual report schedules. Consequently, you should plan carefully which users will be granted permission to create these two different types of schedules.

Figure 5.8 shows the scheduling interface and options available in SSRS.

Figure 5.8: Configuring schedules in Report Manager

The users themselves manage user-created subscriptions, and there is no tool available to list all of your existing user-managed subscriptions. Consequently, you should consider establishing a process to keep track of user’s scheduling requirements and the impact on the SSRS solution.

Reviewing SSRS Logs

It’s boring, but someone has got to do it. You should periodically review the SSRS logs to ensure everything is performing optimally and that you are not experiencing (or about to experience) any capacity problems. SSRS maintains quite a number of logs by default for both the execution of reports (report execution logs) and the SSRS components (SSRS trace logs).

You can both adjust the level of logging detail and turn off logging altogether. The SSRS trace logs are configured through configuration files, whereas the report execution logs are configured through the Report Manager.

The SSRS trace logs contain very detailed information that is useful for debugging applications and/or investigating events. SSRS has four trace log files located in the \Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles directory. Table 5.1 describes the purpose of these four log files.

Table 5.1: SSRS Trace Log Files
Open table as spreadsheet

Log File	Description
ReportServerService_<timestamp>.log	Trace log for the Report Server Windows service worker threads
ReportServerService_main_<timestamp>.log	Trace log for the Report Server Windows service management threads
ReportServerWebApp_<timestamp>.log	Trace log for Report Manager
ReportServer_<timestamp>.log	Trace log for the Report Server web service

The report execution log, on the other hand, is stored in the reportserver database. Bummer! It’s especially unfortunate because there is quite a lot of potentially rich information there, such as when a report was run, who ran it, where it was delivered, and so on, as you can see from this list:

• SSRS server instance name that handled the request

• Report identifier

• User identifier

• Request type

• Rendering format

• Report parameter values used during execution

• Start time of the report process

• Stop time of the report process

• Number of milliseconds spent retrieving the data

• Number of milliseconds spent processing the report

• Number of milliseconds spent rendering the report

• Report execution source

• Status

• Size of rendered reports

• Number of rows returned by queries

To access this information, you will have to use an SSIS package that has been provided by Microsoft to retrieve it in a format accessible to you.

Note 

For more information about how to view report execution data, search for the “Querying and Reporting on Report Execution Log Data” topic in SQL Server 2005 Books Online.

Managing the SSRS Databases

The SSRS databases (reportserver and reportservertempdb by default) are also important in any SSRS management strategy. The SSRS databases are accessed by internally configured users, so there is no need to directly manage access control to these databases. However, the reportserver database in particular stores the metadata about your SSRS solution.

We’ll now cover your various responsibilities for managing your SSRS databases.

Backing Up the SSRS Databases

It is important to incorporate the reportserver in your usual backup strategy. You don’t need to back up the reportservertempdb database because, similarly to the tempdb system database, it is just used for temporary workspace, albeit an important function.

Tip 

Don’t forget to perform your normal database maintenance tasks on the report-servertempdb database, such as database consistency checks and reindexing.

Because of the complex architecture of SSRS, it is not sufficient to back up only the database. SSRS stores sensitive data such as usernames and passwords in its databases. As mentioned earlier, it encrypts sensitive data using a symmetric key. Key-related information must be backed up and secured using the provided configuration tools or the command-line utilities. Figure 5.9 show you how to back up, restore, and change the encryption key in the Reporting Services Configuration Manager.

Figure 5.9: Managing encryption keys in the Reporting Services Configuration Manager

In addition to the database and encryption key backups, you should back up any configuration files and custom extensions (if they exist).

Managing Temporary Snapshots

You have the ability to configure SSRS to store your temporary snapshots in the file system instead of using reportservertempdb, which can be particularly useful for SSRS solutions that need to retain a lot of snapshots as you can take advantage of NTFS compression.

You do this by modifying the WebServiceuseFileShareStorage setting in RSReportServer .config, which is located in the \Reporting Services\ReportServer folder.

You should change the FileShareStorageLocation setting, which is the default storage location because the default otherwise is in a subdirectory of the installation directory, usually C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ RSTempFiles.

Moving the reportserver Database

If you need to move the reportserver database, you can use your standard techniques in SQL Server to manage that process (backup/restore, attach/detach, Copy Database Wizard, and so forth). However, you then need to update your SSRS reporting solution’s configuration files with the new location of your database.

Although you can do this via the rsconfig command-line utility discussed earlier, the easiest way to do it is through the Reporting Services Configuration Manager, as shown in Figure 5.10.

Figure 5.10: Reconfiguring the reportserver database in the Reporting Services Configuration Manager

Monitoring the Space Usage of the SSRS Databases

The space requirements used by your SSRS solution will depend on the configuration and other operational factors, such as the size of reports, how many users are running the various reports, and obviously the retention period (particularly of snapshots). We have certainly seen some reasonably sized SSRS databases at various client sites. It’s all about correctly sizing the SSRS databases by taking into account your peak workload hours, not when there is no one around at 9 A.M.!

Consequently, you should monitor the space usage of reportserver and in particular the reportservertempdb database as you would any other database, ensuring that you don’t run out of space in either the database or the transaction log files.

Capacity Planning the reportserver and reportservertempdb System Databases

When managing an SSRS solution, it is critical to capacity plan your SSRS environment and in particular the reportserver and reportservertempdb system databases. The reportserver system database stores configuration/security data, subscription/schedule definitions, and (most important) report snapshots, and the reportservertempdb system database stores session/execution data, cached reports, and worktables.

Unfortunately, the default SQL Server 2005 installation’s default configurations for these two system databases are not sufficient for most enterprise environments. In fact, several clients of mine who were heavily utilizing SSRS had performance problems related to these two system databases.

So for optimal performance, as with tempdb, you should put these databases on a separate drive. Don’t forget to capacity plan and size them correctly, include them in your disaster recovery plan (DRP), and perform all the typical admin tasks you would for your user databases, especially if you are compiling a history of report snapshots.