Configuring the Reporting Services database

Reporting Services stores and maintains different types of information such as settings, metadata, and objects related to its applications. Some of this information is stored in configuration files and some of it, specifically metadata and objects, is stored in a SQL Server database. Reporting Services native mode uses two databases named ReportServer and ReportServerTempDB by default. The ReportServer database is the primary storage for holding all metadata and objects. ReportServerTempDB is for maintaining temporary information.

Note

Reporting Services uses three databases if the mode is SharePoint integrated mode. In addition to the ReportServer and ReportServerTempDB databases, it uses a third one to hold altering metadata.

If the Reporting Services instance is the default instance, the names of these databases are set as Report Server and ReportServerTempDB. If the instance is a named instance, then the default names become ReportServer$<instancename> and ReportServerTempDB$<instancename>.

The Reporting Services database is used to maintain report definitions, report models, shared data sources, schedules, security information, and snapshots. You should treat this database as a normal database; hence, it should be regularly backed up and should be a part of High Availability and Disaster Recovery strategies.

ReportServerTempdDB stores temporary data, such as session and cache data. Since it maintains temporary data, you do not need to have a backup strategy for this database. However, if this database gets corrupted or accidently deleted, you can create it using the CatalogTempDB.sql query located in the <installationpath>\Microsoft SQL Server\<instancename>\Reporting Services\ReportServer folder.

Getting ready

Similar to Reporting Services URLs discussed in the previous recipe, if you selected Install and Configure during the installation, these two databases are created automatically as a part of the installation. If you selected Install only, then you need to use Reporting Services Configuration Manager to create databases. You can host these databases either in a local database engine instance or in a remote database engine instance. Reporting Services supports the following database engine versions for hosting its databases:

• SQL Server 2016
• SQL Server 2014
• SQL Server 2012
• SQL Server 2008 R2
• SQL Server 2008

However, you need to make sure that the Reporting Services database is hosted in the correct edition:

• Enterprise edition: The database can be hosted in Enterprise or Standard edition
• Standard edition: It can be hosted in Enterprise or Standard edition
• Web edition: The database can be hosted in Web edition (local only)
• Express edition: The database can be hosted in Express edition (local only)
• Developer edition: It can be hosted in Enterprise, Standard, or developer edition

How to do it...

You can use Reporting Services Configuration Manager to create and configure the report server database. Or you can use it to view or modify settings related to the database that is already configured. Use the following steps to create or modify the report server database:

1. Open Reporting Services Configuration Manager and go to the Database page.
2. You should see two sections: Current Report Server Database and Current Report Server Database Credential. Click on the Change Database button:
   

   Figure 1.20

3. Change Database starts a wizard. You need to select a task from first page; select Create a new report server database to create a new one or select Choose an existing report server database to add an existing one. Select the first option for standalone report server.
4. The next page allows you to select the hosting server and credentials to connect with it. Note that the credential required in this page is for creating the database only. It will not be used by Reporting Services later to connect with the database:
   

   Figure 1.21

5. Select the hosting server and click on Next after setting the credentials. The next page allows you to set Database Name and Language.
6. There is no restriction on the database name. You can enter the name of the database as you want. However, it is always better to have a proper naming convention and name it accordingly. As you can see, the name of the temp database is automatically set using the database name. Click on Next to continue:
   

   Figure 1.22

7. The next page is for setting credentials to connect to the database by Reporting Services. You have three options: Windows credentials, SQL Server credentials, or Service credentials. If the database hosting server is a remote server, you should select either Windows or SQL Server credentials; otherwise, you can select one of these as per your requirements:
   

   Figure 1.23

8. Everything required is set. Click on Next to continue. You should see a summary page with all your input. Continue with the wizard to complete the database configuration.
9. If you go back to the second section of the Database page in Reporting Services Configuration Manager, you will see that there is a button named Change Credentials. This is for changing the credentials configured for Reporting Services to connect with the report server database. Although this gets configured either during the installation or while configuring the report database, you may use this option for changing credentials in the following situation:
   • The password set to the local or domain account is changed
   • The authentication type needs to be changed

How it works...

The wizard creates both ReportServer and ReportServerTempDB in the selected SQL Server instance. It uses codes stored in the <installationpath>\Microsoft SQL Server\<instance name>\Reporting Services\ReportServer folder to create these databases. Once the report server databases are created, report server uses the credentials stored for accessing report databases. During the configuration, it creates the encryption keys required and adds the necessary permissions for accessing the database.

Note

Users who access reports do not require permissions for report database or login for report database server. Report server uses credentials stored for accessing the database when required.

Reporting Services stores the connection for report database in the rsreportserver.config file in an encrypted format. Depending on the authentication type selected, some of the nodes related to the connection may be empty. As an example, if the authentication type selected is Service Credentials, then LogonDomain, and LogonCred are empty:

Figure 1.24

In order to verify the report database configuration, log in to the SQL Server instance that hosts the databases. You should see two databases configured with the names you used:

Figure 1.25

There's more...

Remember, Microsoft does not recommend using these databases directly to query or modify data. It is an internal database for report server and should not be accessed and modified.

See also

As mentioned before, Reporting Services uses three databases when the mode is SharePoint integrated mode. Read more about SharePoint integrated mode in Chapter 9, SharePoint Integration.