Administering SQL Server Report Server Databases

SQLServerF1

Once DBAs install and configure SQL Server Reporting Servers, two new databases will get created in the respective SQL Server instance selected during Reporting Services configuration. By default, these two new databases names will be ReportServer and ReportServerTempdb. As part of Reporting Services configuration, we can choose different names too, but in general, if we choose not to change default names, then we will see these two new databases being created in the respective SQL Server instance.

ReportServer Database – The report server database is a SQL Server database that is used to store the content such as reports and linked reports, shared data sources, report models, folders, resources, Subscription and schedule definitions, Report snapshots and report history, System properties and system-level security settings, Report execution log data, Symmetric keys and encrypted connection and credentials for report data sources, etc.
ReportServerTempDB Database – Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database. This reportservertempdb has different behavior compared to regular tempdb database. Reportservertempdb does not get recreated upon SQL Server restart, and DBAs are allowed to delete this database, in which case we need to restore this database either using available backups or database files if available.

Backup Considerations for Report Server databases – It is important to backup report server databases to be able to recover reporting services instance in case of a server crash or a disaster. It is best to choose Full recovery model for ReportServer database and perform regular full and log backups. For ReportServerTempDB, we can choose to keep it in Simple recovery model and importantly perform regular Full backups to be able to get the sachems back. If we do not have backups of these databases, then report server configuration and settings will be lost and need to be built from scratch.

Also, it is very important to backup report server encryption keys and keep them safe on some backup share, as this is required to be able to restore reportserver databases and to be able to read data from the tables. Missing encryption keys will cause the loss of credentials and other settings which are encrypted using this key and will need to build missing things from scratch again. We can backup the key from the Report Server Configuration Manager. Not having backup of report server encryption keys may result in major issues, so just be aware and careful.

For troubleshooting report server issues, we can check eventlogs and report server logs which are mostly available at \Reporting Services\LogFiles folder. We may need to some times clean up the files if old logs or dumps are not cleaned up.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Leave a Reply

Your email address will not be published. Required fields are marked *