Administer a Report Server Database (SSRS Native Mode)

Report Server Database (SSRS Native Mode)

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. For more information, see Manage a Reporting Services SharePoint Service Application

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.

You can port or reuse an existing report server database from previous installation or a different instance with another report server instance. The schema of the report server database must be compatible with the report server instance. If the database is in an older format, you will be prompted to upgrade it to the current format. Newer versions cannot be down graded to an older version. If you have a newer report server database, you cannot use it with an earlier version of a report server instances. For more information about how report server databases are upgraded to newer formats, see Upgrade a Report Server Database.

Report Server Database

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. For recommendations and instructions on how to back up the database, see Moving the Report Server Databases to Another Computer (SSRS Native Mode).

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

Create a Report Server Database (SSRS Configuration Manager)

Reporting Services Native mode uses two SQL Server relational databases to store report server metadata and objects. One database is used for primary storage, and the second one stores temporary data. The databases are created together and bound by name. With a default SQL Server instance, the databases are named reportserver and reportservertempdb. Collectively, the two databases are referred to as the "report server database" or "report server catalog".

Reporting Services SharePoint mode includes a third database that is used for data alerting metadata. The three databases are created for each Reporting Services service application and the database names by default include a guid that represents the service application. The following are example names of the three SharePoint mode databases:

• ReportingService_90a9f37075544f22953c4a62e4a9f370
• ReportingService_90a9f37075544f22953c4a62e4a9f370TempDB
• ReportingService_90a9f37075544f22953c4a62e4a9f370_Alertin

Ways to Create the Report Server Database

Native Mode: You can create the Native mode report server database in the following ways:

• Automatically: Use SQL Server Setup Wizard, if you choose the default configuration installation option. In the SQL Server Installation Wizard, this is the Install and configure in the Report Server Installation Options page. If you chose the Install only option, you must use the Reporting Services Configuration Manager to create the database.
• Manually: Use the Reporting Services Configuration Manager. You must create the report server database manually if you are using a remote SQL Server Database Engine to host the database. For more information, see Create a Native Mode Report Server Database (SSRS Configuration Manager).

SharePoint Mode: The Report Server Installation Options page only has one option for SharePoint mode of Install Only. This option installs all the Reporting Services files and the Reporting Services shared service. The next step is to create at least one Reporting Services service application in one of the following ways:

• Use SharePoint Central Administration to create a Reporting Services service application. For more information see the “Service Application” section of Step 3: Create a Reporting Services Service Application.
• Use Reporting Services PowerShell cmdlets to create a service application and the report server databases. For more information see the sample for creating service applications in the topic PowerShell cmdlets for Reporting Services SharePoint Mode.
• 
  

Create a Report Server Database (SSRS Configuration Manager)

SQL Server 2016

Other Versions

 

Applies To: SQL Server 2016 Preview

Reporting Services Native mode uses two SQL Server relational databases to store report server metadata and objects. One database is used for primary storage, and the second one stores temporary data. The databases are created together and bound by name. With a default SQL Server instance, the databases are named reportserver and reportservertempdb. Collectively, the two databases are referred to as the "report server database" or "report server catalog".

Reporting Services SharePoint mode includes a third database that is used for data alerting metadata. The three databases are created for each Reporting Services service application and the database names by default include a guid that represents the service application. The following are example names of the three SharePoint mode databases:

• ReportingService_90a9f37075544f22953c4a62e4a9f370
• ReportingService_90a9f37075544f22953c4a62e4a9f370TempDB
• ReportingService_90a9f37075544f22953c4a62e4a9f370_Alerting

Important

Do not write applications that run queries against the report server database. The report server database is not a public schema. The table structure might change from one release to the next. If you write an application that requires access to the report server database, always use the Reporting Services APIs to access the report server database. The exception to this are the execution log views. For more information, see Report Server Execution Log and the ExecutionLog3 View

Ways to Create the Report Server Database

Native Mode: You can create the Native mode report server database in the following ways:

• Automatically: Use SQL Server Setup Wizard, if you choose the default configuration installation option. In the SQL Server Installation Wizard, this is the Install and configure in the Report Server Installation Options page. If you chose the Install only option, you must use the Reporting Services Configuration Manager to create the database.
• Manually: Use the Reporting Services Configuration Manager. You must create the report server database manually if you are using a remote SQL Server Database Engine to host the database. For more information, see Create a Native Mode Report Server Database (SSRS Configuration Manager).

SharePoint Mode: The Report Server Installation Options page only has one option for SharePoint mode of Install Only. This option installs all the Reporting Services files and the Reporting Services shared service. The next step is to create at least one Reporting Services service application in one of the following ways:

• Use SharePoint Central Administration to create a Reporting Services service application. For more information see the “Service Application” section of Step 3: Create a Reporting Services Service Application.
• Use Reporting Services PowerShell cmdlets to create a service application and the report server databases. For more information see the sample for creating service applications in the topic PowerShell cmdlets for Reporting Services SharePoint Mode.

Database Server Version Requirements

SQL Server is used to host the report server databases. The SQL Server Database Engine instance can be a local or remote instance. The following are the supported versions of SQL Server Database Engine that can be used to host the report server databases:

• SQL Server 2016 Community Technology Preview 2 (CTP2)
• SQL Server 2014
• SQL Server 2012
• SQL Server 2008 R2
• SQL Server 2008
• SQL Server 2005

Creating the report server database on a remote computer requires that you configure the connection to use a domain user account or a service account that has network access. If you decide to use a remote SQL Server instance, consider carefully which credentials the report server should use to connect to the SQL Server instance. For more information, see Configure a Report Server Database Connection (SSRS Configuration Manager).

Moving the Report Server Databases to Another Computer (SSRS Native Mode)

You can move the report server databases that are used in an installation SQL Server Database Engine to an instance that is on a different computer. Both the reportserver and reportservertempdb databases must be moved or copied together. A Reporting Services installation requires both databases; the reportservertempdb database must be related by name to the primary reportserver database you are moving.

Applies to: Reporting Services Native mode.

Moving a database does not effect scheduled operations that are currently defined for report server items.

• Schedules will be recreated the first time that you restart the Report Server service.
• SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.
• Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Applyagain to save your changes.
• Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

SQL Server provides several approaches for moving databases, including backup and restore, attach and detach, and copy. Not all approaches are appropriate for relocating an existing database to a new server instance. The approach that you should use to move the report server database will vary depending on your system availability requirements. The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

Important
The steps provided in this topic are recommended when relocating the report server database is the only change you are making to the existing installation. Migrating an entire Reporting Services installation (that is, moving the database and changing the identity of the Report Server Windows service that uses the database) requires connection reconfiguration and an encryption key reset.

Detaching and Attaching the Report Server Databases

If you can take the report server offline, you can detach the databases to move them to the SQL Server instance you want to use. This approach preserves permissions in the databases. If you are using a SQL Server 2016 database, you must move it to another SQL Server 2016 instance. After you move the databases, you must reconfigure the report server connection to the report server database. If you are running a scale-out deployment, you must reconfigure the report server database connection for each report server in the deployment.

Use the following steps to move the databases:

1. Backup the encryption keys for the report server database you want to move. You can use the Reporting Services Configuration tool backup the keys.
2. Stop the Report Server service. You can use the Reporting Services Configuration tool to stop the service.
3. Start SQL Server Management Studio and open a connection to the SQL Server instance that hosts the report server databases.
4. Right-click the report server database, point to Tasks, and click Detach. Repeat this step for the report server temporary database.
5. Copy or move the .mdf and .ldf files to the Data folder of the SQL Server instance you want to use. Because you are moving two databases, make sure that you move or copy all four files.
6. In Management Studio, open a connection to the new SQL Server instance that will host the report server databases.
7. Right-click the Databases node, and then click Attach.
8. Click Add to select the report server database .mdf and .ldf files that you want to attach. Repeat this step for the report server temporary database.
9. After the databases are attached, verify that the RSExecRole is a database role in the report server database and temporary database.RSExecRole must have select, insert, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. For more information, see Create the RSExecRole.
10. Start the Reporting Services Configuration tool and open a connection to the report server.
11. On the Database page, select the new SQL Server instance, and then click Connect.
12. Select the report server database that you just moved, and then click Apply.
13. On the Encryption Keys page, click Restore. Specify the file that contains the backup copy of the keys and the password to unlock the file.
14. Restart the Report Server service.

Backing Up and Restoring the Report Server Databases

If you cannot take the report server offline, you can use backup and restore to relocate the report server databases. You must use Transact-SQL statements to do the backup and restore. After you restore the databases, you must configure the report server to use the database on the new server instance. For more information, see the instructions at the end of this topic.

Using BACKUP and COPY_ONLY to Backup the Report Server Databases

When backing up the databases, set the COPY_ONLY argument. Be sure to back up both of the databases and log files.

-- To permit log backups, before the full database backup, alter the database 
-- to use the full recovery model.
USE master;
GO
ALTER DATABASE ReportServer
   SET RECOVERY FULL

-- If the ReportServerData device does not exist yet, create it. 
USE master
GO
EXEC sp_addumpdevice 'disk', 'ReportServerData', 
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\ReportServerData.bak'

-- Create a logical backup device, ReportServerLog.
USE master
GO
EXEC sp_addumpdevice 'disk', 'ReportServerLog', 
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\ReportServerLog.bak'

-- Back up the full ReportServer database.
BACKUP DATABASE ReportServer
   TO ReportServerData
   WITH COPY_ONLY

-- Back up the ReportServer log.
BACKUP LOG ReportServer
   TO ReportServerLog
   WITH COPY_ONLY

-- To permit log backups, before the full database backup, alter the database 
-- to use the full recovery model.
USE master;
GO
ALTER DATABASE ReportServerTempdb
   SET RECOVERY FULL

-- If the ReportServerTempDBData device does not exist yet, create it. 
USE master
GO
EXEC sp_addumpdevice 'disk', 'ReportServerTempDBData', 
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\ReportServerTempDBData.bak'

-- Create a logical backup device, ReportServerTempDBLog.
USE master
GO
EXEC sp_addumpdevice 'disk', 'ReportServerTempDBLog', 
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\ReportServerTempDBLog.bak'

-- Back up the full ReportServerTempDB database.
BACKUP DATABASE ReportServerTempDB
   TO ReportServerTempDBData
   WITH COPY_ONLY

-- Back up the ReportServerTempDB log.
BACKUP LOG ReportServerTempDB
   TO ReportServerTempDBLog
   WITH COPY_ONLY

Using RESTORE and MOVE to Relocate the Report Server Databases

When restoring the databases, be sure to include the MOVE argument so that you can specify a path. Use the NORECOVERY argument to perform the initial restore; this keeps the database in a RESTORING state, giving you time to review log backups to determine which one to restore. The final step repeats the RESTORE operation with the RECOVERY argument.

The MOVE argument uses the logical name of the data file. To find the logical name, execute the following statement: RESTORE FILELISTONLY FROM DISK='C:\ReportServerData.bak';

The following examples include the FILE argument so that you can specify the file position of the log file to restore. To find the file position, execute the following statement: RESTORE HEADERONLY FROM DISK='C:\ReportServerData.bak';

When restoring the database and log files, you should run each RESTORE operation separately.

-- Restore the report server database and move to new instance folder 
RESTORE DATABASE ReportServer
   FROM DISK='C:\ReportServerData.bak'
   WITH NORECOVERY, 
      MOVE 'ReportServer' TO 
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer.mdf', 
      MOVE 'ReportServer_log' TO
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer_Log.ldf';
GO

-- Restore the report server log file to new instance folder 
RESTORE LOG ReportServer
   FROM DISK='C:\ReportServerData.bak'
   WITH NORECOVERY, FILE=2
      MOVE 'ReportServer' TO 
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer.mdf', 
      MOVE 'ReportServer_log' TO
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer_Log.ldf';
GO

-- Restore and move the report server temporary database
RESTORE DATABASE ReportServerTempdb
   FROM DISK='C:\ReportServerTempDBData.bak'
   WITH NORECOVERY, 
      MOVE 'ReportServerTempDB' TO 
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServerTempDB.mdf', 
      MOVE 'ReportServerTempDB_log' TO
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\REportServerTempDB_Log.ldf';
GO

-- Restore the temporary database log file to new instance folder 
RESTORE LOG ReportServerTempdb
   FROM DISK='C:\ReportServerTempDBData.bak'
   WITH NORECOVERY, FILE=2
      MOVE 'ReportServerTempDB' TO 
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServerTempDB.mdf', 
      MOVE 'ReportServerTempDB_log' TO
         'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\REportServerTempDB_Log.ldf';
GO

-- Perform final restore
RESTORE DATABASE ReportServer
   WITH RECOVERY
GO

-- Perform final restore
RESTORE DATABASE ReportServerTempDB
   WITH RECOVERY
GO

How to Configure the Report Server Database Connection

1. Start the Reporting Services Configuration Manager and open a connection to the report server.
2. On the Database page, click Change Database. Click Next.
3. Click Choose an existing report server database. Click Next.
4. Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.
5. In Database Name, select the report server database that you want to use. Click Next.
6. In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.
7. Click Next and then Finish.

Administer a Report Server Database (SSRS Native Mode)

A Reporting Services deployment uses two SQL Server relational databases for internal storage. By default, the databases are named ReportServerand ReportServerTempdb. ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports.

In Reporting Services, database administration tasks include backing up and restoring the report server databases and managing the encryption keys that are used to encrypt and decrypt sensitive data.

To administer the report server databases, SQL Server provides a variety of tools.

• To back up or restore the report server database, move a report server database, or recover a report server database, you can use SQL Server Management Studio, the Transact-SQL commands, or the database command prompt utilities. For instructions, see Moving the Report Server Databases to Another Computer (SSRS Native Mode) in SQL Server Books Online.
• To copy existing database content to another report server database, you can attach a copy of a report server database and use it with a different report server instance. Or, you can create and run a script that uses SOAP calls to recreate report server content in a new database. You can use the rs utility to run the script.
• To manage connections between the report server and report server database, and to find out which database is used for a particular report server instance, you can use Database Setup page in the Reporting ServicesConfiguration tool. To learn more about the report server connection to the report server database, see Configure a Report Server Database Connection (SSRS Configuration Manager).

SQL Server Login and Database Permissions

The report server databases are used internally by the report server. Connections to either database are made by the Report Server service. You use the Reporting Services Configuration tool to configure the report server connection to the report server database.

Credentials for the report server connection to the database can be the service account, a Windows local or domain user account, or a SQL Server database user. You must choose an existing account for the connection; Reporting Services does not create accounts for you.

A SQL Server login to the report server database is created for you automatically for the account you specify.

Permissions to the database are also configured automatically. The Reporting Services Configuration tool will assign the account or database user to the Public and RSExecRole roles for the report server databases. The RSExecRole provides permissions for accessing the database tables and for executing stored procedures. The RSExecRole is created in master and msdb when you create the report server database. The RSExecRole is a member of the db_owner role for the report server databases, allowing the report server to update its own schema in support of an auto-upgrade process.

Naming Conventions for the Report Server Databases

When creating the primary database, the name of the database must follow the rules specified for Database Identifiers. The temporary database name always uses the same name as the primary report server database but with a Tempdb suffix. You cannot choose a different name for the temporary database.

Renaming a report server database is not supported because the report server databases are considered internal components. Renaming the report server databases causes errors to occur. Specifically, if you rename the primary database, an error message explains that the database names are out of sync. If you rename the ReportServerTempdb database, the following internal error occurs later when you run reports:

"An internal error occurred on the report server. See the error log for more details. (rsInternalError)

Invalid object name 'ReportServerTempDB.dbo.PersistedStream'."

This error occurs because the ReportServerTempdb name is stored internally and used by stored procedures to perform internal operations. Renaming the temporary database will prevent the stored procedures from working properly.

Enabling Snapshot Isolation on the Report Server Database

You cannot enable snapshot isolation on the report server database. If snapshot isolation is turned on, you will encounter the following error: "The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available."

If you did not purposely enable snapshot isolation, the attribute might have been set by another application or the model database might have snapshot isolation enabled, causing all new databases to inherit the setting.

To turn off snapshot isolation on the report server database, start Management Studio, open a new query window, paste and then run the following script:

ALTER DATABASE ReportServer
SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE ReportServerTempdb
SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE ReportServer
SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE ReportServerTempDb
SET READ_COMMITTED_SNAPSHOT OFF

About Database Versions

In Reporting Services, explicit information about the database version is not available. However, because database versions are always synchronized to product versions, you can use product version information to tell when the database version has changed. Product version information for Reporting Services is indicated through file version information that appears in the log files, in the headers of all SOAP calls, and when you connect to the report server URL (for example, when you open a browser to http://localhost/reportserver).