Administering a Report Server Database
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 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 a Report Server Database to Another Computer 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 Services Configuration tool. To learn more about the report server connection to the report server database, see Configuring a Report Server Database Connection.
- To gather metrics about report server performance and activity, see Querying and Reporting on Report Execution Log Data in SQL Server Books Online.
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 Web service and Report Server Windows service.
You can use the Reporting Services Configuration tool to specify the connection. You can use the credentials of the service accounts, or the credentials of domain user account, or a SQL Server login. The account you choose for the connection must have a SQL Server login and must have the Public and RSExecRole roles for the report server databases.
The login and permissions are created for you automatically when you use the Reporting Services Configuration tool to configure the database connection, upgrade the report server database, or run equivalent scripts on the SQL Server instance that hosts the database.
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. Starting in SQL Server 2005 Service Pack 1, the RSExecRole is a member of the db_owner role for the report server databases. The additional permissions allow the report server to update the schema in subsequent releases, thus simplifying the upgrade process.
Removing Unused Accounts in RSExecRole
If you configure the Report Server Web service or Windows service to run under a different account, the new account will be added to the RSExecRole automatically. However, any accounts that were previously added and that are no longer used must be removed manually. To remove the accounts, use Management Studio to do the following:
- Connect to the Database Engine instance that hosts the report server database.
- Expand the Databases folder, expand the ReportServer folder, expand the Security folder, expand Roles, expand Database Roles, and then double-click RSExecRole.
- Select the account that is no longer used.
- Click Remove.
Naming Conventions for the Report Server Databases
When creating the primary database, the name of the database must follow the rules specified for 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.
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 https://localhost/reportserver).
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
See Also
Concepts
Creating a Report Server Database
Backup and Restore Operations for a Reporting Services Installation
Report Server Database
Administering Reporting Services
Report Server Database Requirements
Storing Encrypted Report Server Data
Managing Encryption Keys
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
15 September 2007 |
|
12 December 2006 |
|
14 April 2006 |
|