Muokkaa

Jaa

Administer a report server database (SSRS native mode)

  • Artikkeli

A Reporting Services deployment uses two SQL Server relational databases for internal storage. By default, the databases are named ReportServer and 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. The tasks also include managing the encryption keys that are used to encrypt and decrypt sensitive data.

To administer the report server databases, SQL Server provides various tools.

  • You can use SQL Server Management Studio, the Transact-SQL commands, or the database command prompt utilities to:

    • Back up or restore the report server database
    • Move a report server database
    • Recover a report server database

    For more information, see Move the report server databases to another computer (SSRS native mode).

  • 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.

  • You can use the Database Setup page in the Reporting Services Configuration tool to manage connections between the report server and report server database. You can also use it to find out which database is used for a particular report server instance. To learn more about the report server connection to the report server database, see Configure a report server database connection (Report Server Configuration Manager).

SQL Server sign in 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 doesn't create accounts for you.

A SQL Server sign-in 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 assigns 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 the primary database 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 autoupgrade process.

Naming conventions for the report server databases

When you create 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 can't choose a different name for the temporary database.

Renaming a report server database isn't 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. For more information, see the error log. (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 prevents the stored procedures from working properly.

Enable snapshot isolation on the report server database

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

If you didn't purposely enable snapshot isolation, the attribute might be 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 isn't available. However, because database versions are always synchronized to product versions, you can use product version information to tell when the database version 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 https://localhost/reportserver).

Related content