SSRS Interview Question VIII

16. What do you know about Report server ?

Ans. A report server is a stateless server that uses the SQL Server Database Engine to store metadata and object definitions. A native mode Reporting Services installation uses two databases to separate persistent data storage from temporary storage requirements. The databases are created together and bound by name. By default, the database names are reportserver and reportservertempdb, respectively.

A SharePoint mode Reporting Services installation will also create a database for the data alerting feature. The three databases in SharePoint mode are associated with Reporting Services service applications.

The databases can run on a local or remote Database Engine instance. Choosing a local instance is useful if you have sufficient system resources or want to conserve software licenses, but running the databases on a remote computer can improve performance.

The report server database is a SQL Server database that stores the following content:

  • Items managed by a report server (reports and linked reports, shared data sources, report models, folders, resources) and all of the properties and security settings that are associated with those items.
  • Subscription and schedule definitions.
  • Report snapshots (which include query results) 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.

Because the report server database stores application state and persistent data, you should create a backup schedule for this database to prevent data loss.

Report Server Temporary 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.

Reporting Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables. Although the temporary database does not contain persistent data, you should back up a copy of the database anyway so that you can avoid having to re-create it as part of a failure recovery operation.

If you back up the temporary database and subsequently restore it, you should delete the contents. Generally, it is safe to delete the contents of the temporary database at any time. However, you must restart the Report Server Windows service after you delete the contents.

Note :  These databases are responsible for containing the metadata that is needed by the Reporting Services engine so that it knows how to request data from the data source (such as a SQL Server database or an Analysis Services database). When a report is executed by a report server, SSRS loads report metadata from RSDB. This includes the data source information for the report, the default parameter values, and the report itself. Note that for ad-hoc reports, this data is not loaded from the RSDB catalog database because the report comes from the RDL that is executed, published, and bound to the user’s session. As well, all user session information and live or cached execution snapshots are stored in the RSTempDB database. History and execution snapshots are stored in the RSDB database. Subsequent report requests can use the stored state to view the report rather than re-executing the report.