SSRS Management for the DBA

Many DBAs find themselves being somewhat responsible for SSRS. Many have to install it for a 3rd party application that uses SSRS, others find that it was installed by the vendors installation package or by the implementation team. Some DBAs install, configure, and even create reports, however most are not completely familiar with what it takes to recover SSRS in the event of a system failure.

If the DBAs have implemented a decent backup routine, then ReportServer and ReportServerTempDB should be backing up like any other database, however the ReportServer database is encrypted and by default, the encryption key is not backed up. A standard install of SSRS, you’ll configure the web service, web portal url, establish a database connection, however you are not prompted to back up the encryption key before you start using SSRS.

Sensitive data such as data sources, stored user names and passwords, and more are encrypted in the ReportServer database. Without the encryption key being available to recovery in the event of an emergency, that information would have to be recreated, thus causes a much longer outage. There are multiple ways to back up and restore the encryption key, one of the most straight forward is to use the Report Server Configuration Manager. Just select to “Backup” or “Restore” and follow the prompts.

As a data professional responsible for supporting the SSRS infrastructure, we should be familiar with much more about the ReportServer database that is full of useful information to help with troubleshooting. Some key tables you should be familiar with to reference include:

Catalog

The dbo.Catalog table contains lots of useful information listing out what a user can interact with using the Web Portal UI. This includes data sources, images, reports, and more.

Subscriptions

Often times, I come across customers who utilize subscriptions. Directly querying the dbo.Subscriptions table will reveal all subscriptions currently defined in SSRS. Expanding this to join a few other tables to link subscriptions to reports can be very helpful to know who all has access to various reports.

Users

This one is self explanatory, the dbo.Users list out specific users and groups who have been given access to SSRS. Keep in mind that SSRS security is separate from SQL Server users and logins.

Execution Log Storage

This is the table I review the most in order to see when reports have run, how long they took, row count, etc. There are actually three views built on the dbo.ExecutionLogStorage table. These are ExcecutionLog, ExecutionLog2, and ExcecutionLog3. This is my go-to spot to look for failed reports.