Storing and Synchronizing Report Server Content With SharePoint Databases

When you configure a report server to run in SharePoint integration mode, the report server uses the SharePoint configuration and content databases as well as its own internal databases to store content and metadata.

Both Reporting Services and SharePoint are distributed server applications that allow you to run services and internal databases on separate computers. Each server stores different kinds of data. Multiple SQL Server relational databases provide the internal storage for both servers. Knowing which type of data is stored in each one can help you understand how the servers work together. It also provides background information that can help you make decisions about how to allocate disk space and schedule database backups.

The following diagram shows the complete set of databases used in a report server deployment that runs in SharePoint integrated mode.

Database diagram

SharePoint Configuration Database

Every SharePoint Web application has a configuration database that stores application settings. When you set up an instance of SharePoint Foundation 2010 or SharePoint Server 2010 to work with a report server, you specify configuration settings in SharePoint Central Administration. The settings that you specify are stored in this database. Settings include the URL of the report server, account information for the Report Server service, information about the authentication provider used on the server, and site-level settings that limit or enable report history and logging. For more information, see Configuring Reporting Services for SharePoint 2010 Integration.

SharePoint Content Database

Each SharePoint Web application has one or more content databases that store the documents and properties that are accessed and managed through the server. For a report server that is configured for SharePoint integrated mode, the SharePoint content database provides the primary storage for published reports, report models, shared data sources, resources, properties, and permissions.

Report Server Database and the Report Server Temporary Database

Each report server instance or scale-out deployment uses a single report server database to store persistent data. Temporary data is isolated in a secondary database. This enables you to create different backup schedules and set different growth properties for each one. There is one temporary database for each report server database. It stores session data and temporary snapshots that are created only for subscription processing, interactive reporting, or report caching as a performance enhancement.

The data that is kept in a report server database includes schedules, subscriptions, and snapshots for report history or report execution. The report server database also maintains internal copies of the content files that are stored in the SharePoint content database. Storing internal copies improves performance by minimizing the number of times a file has to be sent to the report server for processing. Synchronization and verification checks ensure that the reports, models, and data sources are the same.

Important

A report server database can support native mode operations or SharePoint integration mode, but never both. If you create a report server database to support SharePoint integrated mode, you cannot convert or migrate the database to run with a native mode report server instance later. The report server database contains mode-specific metadata (specifically, item location and permission information) that is not compatible in the other mode.

Database Connections, Permissions, and Logins

A user who configures a report server or an instance of Windows SharePoint Services or Office SharePoint Server must be a local administrator or a member of the built-in Administrators group on the computer.

The Report Server service requires a database connection to the SharePoint content database, but not to the configuration database. Report server integration settings are stored in the configuration database using the login and permissions that are created for users of the Central Administration tool when Windows SharePoint Services is installed.

In contrast, the Report Server service does create, update, and delete items and properties in the SharePoint content database. The connection information, login, and database permissions are configured when you specify the trusted account in Central Administration.

The Windows SharePoint Services or Office SharePoint Server instance does not add or retrieve data from the report server databases. All requests are directed to the report server, which in turn accesses the report server databases using its own connection settings.

Permission to view an item or perform an operation on a report server is managed by the Windows SharePoint Services or Office SharePoint Server instance. Authentication and security checks occur before the request is sent to the report server. Each request is accompanied by a security token that includes permission information that determines whether the request is serviced or denied.

Database Maintenance

The report server performs routine maintenance to remove orphaned report snapshots, report history, schedules, and subscriptions from the report server database that are deleted on the SharePoint site. Orphaned items are those that no longer have an association with a parent item in a SharePoint content database.

At daily intervals, the report server verifies that items stored in the report server database are associated with a report that exists in a SharePoint content database. To modify the frequency of the clean up process, add the DailyCleanupMinuteofDay configuration setting in the RSReportServer.config file. The clean up process runs in the background and uses the schedule information that is defined in the configuration files. You cannot run the process on demand.

If the instance of Windows SharePoint Services or Office SharePoint Server is not available (for example, if the server is offline), no cleanup operation occurs. If you restore a report server database that has old data that does not correspond to the items currently stored in the SharePoint database, the items will be removed the next time that the clean up process runs.

The clean up process only deletes content; it does not synchronize or update the report server items that are stored in the SharePoint content database. By default, the clean up process runs at 2:00 a.m. each day.

To specify a different time, you must add DailyCleanupMinuteofDay to the RSReportServer.config file and set a different value. For example:

<Add Key=" DailyCleanupMinuteOfDay " Value="120" />

The setting is not in RSReportServer.config file by default. To add it, copy the entry and place it under the Configuration element alongside the other Add elements in that section. The value is minutes starting at 12:00 A.M., with a minimum value of 30 and a maximum of 1380 (23 hours).

Synchronization and Verification Operations

To ensure that the copies kept for internal processing on the report server are the same as the original items in the SharePoint library, the report server performs synchronization and item verification steps before processing a request.

Synchronization is a process that creates, updates, or deletes an internal copy of an item that is stored in the report server database. The report server retrieves items and properties from a SharePoint library and stores them in the report server database. Timestamp information is the basis of comparison. The timestamp is stored in UTC format. If the report server and the SharePoint site or farm are in different time zones, the timestamp is converted to local server time as it is stored.

Verification is a step that determines whether synchronization is necessary. If the timestamp for the original item and the internal copy is the same, no synchronization occurs.

How items are synchronized

Synchronization is performed by the report server. It is a background process that is triggered automatically whenever an item is created, updated, or retrieved. You cannot manage, configure, or explicitly control synchronization operations. Synchronization is scoped to specific requests to create or update an item. There is no global synchronization operation that aligns all the items and properties at the same time. To manually synchronize a report, report model, or shared data source, you must run it.

Internal copies of an item are created or updated when the item does not exist in the report server database or the item in the report server database is older than the corresponding item in the SharePoint library. An internal copy is created in response to the following events:

  • Request for a report or report model. This creates an internal copy of a report, report model, and any shared data sources that are referenced by either item.

  • Publish a report or report model from Report Designer or Model Designer to a SharePoint library. After the item is added to the SharePoint content database, a copy or the report or model is created and added to the report server database. If you also publish a shared data source along with the report model, an internal copy of the shared data source will not be created until it is used.

Using the Upload action on a SharePoint site will delay the creation of an internal copy in the report server database. For any report server item that you upload, an internal copy is created when the item is requested.

No internal copies are ever made of external image files used in a report, or of schedules, snapshots, or subscriptions that are stored exclusively in the report server database.

About Configuration Settings and Permissions

Although configuration settings must be defined correctly on both servers, they are excluded from synchronization processes. If you change the Report Server service account, the URL, authentication type, or the server execution mode of the report server so that it runs in native mode instead of SharePoint integrated mode, you must explicitly update server configuration settings in SharePoint Central Administration or Reporting Services Configuration tool.

Permissions that are set on a SharePoint site or farm are used by the report server. There is no synchronization of permissions between a SharePoint site or farm and a report server.

Server Availability and Synchronization Failures

Synchronization of items, properties, and operations requires that all computers and connections that are part of a deployment are available and operational. Synchronization will not occur when the following conditions are true:

  • Servers or backend databases are taken offline or are otherwise unavailable.

  • Connection information for any of the servers or backend databases becomes invalid. For example, if you change a URL incorrectly or you change a service account or password.

  • Connection information is modified without taking servers offline first, disrupting the timing and flow of synchronized operations.

For the cases where synchronization cannot occur because the servers cannot connect to each other or their backend databases, the report server will reconcile out-of-sync content or metadata when the connection is restored and when a specific report, report model, shared data source, subscription is requested.

The rsItemNotFound error will occur if synchronization fails. This means that the item was not found in a SharePoint library.