Reporting Services SharePoint Integrated Mode Overview
What is integrated mode?
In SharePoint integrated mode, a report server must run within a SharePoint server farm. A SharePoint site provides the front-end access to report server content and operations. The report server provides all report processing and rendering. The advantage of integrated mode is a rich level of integration that allows you to access and manage report server content types using the application pages and data stores of a SharePoint Web application.
Users publish or upload reports, models, and data sources to a SharePoint library.
Reports, data sources, and data models in a SharePoint document library are stored as files in the SharePoint content database for that SharePoint document library. Files are stored in the .rdl, .rsds, and .smdl extensions. When a user requests to view or manage a report, data source, or data model, Reporting Services synchronizes its contents with the SharePoint content database.
Processing and Synchronization
The report server continues to provide all data processing, rendering, and delivery. It also supports all scheduled report processing for snapshots and report history.
When you open a report from a SharePoint site, the ReportServer2006 endpoint connects to a report server, creates a session, prepares the report for processing, retrieves data, merges the report into the report layout, and displays it in the Report Viewer Web part. While the report is open, you can export it to different application formats, or interact with data by drilling into underlying numbers or clicking through to a related report. Export and report interaction operations are performed on the report server.
The report server synchronizes operations and data with Windows SharePoint Services and tracks information about the files it processes. The synchronization is performed because 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. When you modify properties or settings for any report server item, the change is stored in a SharePoint database and then copied to a report server database that provides internal storage to a report server.
This is all provided by the Reporting Services Add-in for SharePoint Technologies which:
- On Windows SharePoint Services, provides the ReportServer2006 proxy endpoint, a Report Viewer Web part, and application pages so that you can view, store, and manage report server content on a SharePoint site or farm.
On Reporting Services, provides updated program files, a SOAP endpoint, and custom security and delivery extensions. The report server must be configured to run in SharePoint integrated mode, dedicated exclusively to supporting report access and delivery through your SharePoint site.
Integrated Mode Limitations:
Integrated Mode Installation Overview
Report Server Requirements:
SharePoint Product and Technology Requirements:
Database Requirements:
Deployment Architectures:
Standalone Server Deployment
This deployment is the simplest to configure. Because all the components are installed on the same computer, it also uses the least amount of licenses. Specifically, Reporting Services and the Database Engine can be installed as a single licensed copy of SQL Server. In addition, Reporting Services can connect directly to the local instance of the SharePoint product or technology.
Two Server Distributed Deployment
The first computer hosts an instance of a SharePoint product or technology. The Reporting Services Add-in is installed on this computer.
The second computer hosts SQL Server Reporting Services and the Database Engine. Installing Reporting Services and the Database Engine on the same computer is useful if the benefits of running both components as a single licensed installation outweigh performance considerations.
Notice that when you run Reporting Services and the SharePoint product or technology on different computers, the report server must have a minimal installation of the same SharePoint product or technology. This means that if you installed Microsoft Office SharePoint Server on the first computer, and you must install a Web front-end of Office SharePoint Server on the second computer. The minimal installation provides a Web front-end that allows you to join a report server instance to the instance of the SharePoint product or technology.
Three Server Distributed Deployment
The first computer hosts an instance of a SharePoint product or technology. The second computer hosts SQL Server Reporting Services. The third computer hosts an instance of the Database Engine.
In the illustration, notice that the Database Engine does not require a local installation of a report server or an instance of a SharePoint product or technology.
To use a remote database server with a deployment of a SharePoint product or technology, choose the Advanced installation option when installing an instance of a SharePoint product or technology, or use Central Administration to modify the database server setting. To select a remote database server for Reporting Services, use the Reporting Services Configuration tool.
Three Server Mixed SQL Version Distributed Deployment
The first computer hosts an instance of a SharePoint product or technology. The second computer hosts SQL Server Reporting Services using SQL Server 2008. The third computer hosts an instance of the SQL Server 2005 Database Engine used for the SharePoint Configuration and Content databases (and alternatively the Reporting Services 2008 Report Server database as this may be hosted in a SQL Server 2005 database).
In the illustration, notice that the Database Engine does not require a local installation of a report server or an instance of a SharePoint product or technology.
To use a remote database server with a deployment of a SharePoint product or technology, choose the Advanced installation option when installing an instance of a SharePoint product or technology, or use Central Administration to modify the database server setting. To select a remote database server for Reporting Services, use the Reporting Services Configuration tool.
Key points regarding mixed SQL Versions:
- The SSRS 2008 database can be deployed to the same or another database (not required to reside wherever the SharePoint databases are located)
- You can install the SSRS 2008 Add-Ins to SharePoint even though it's databases are on a SQL 2005 (confirmed by Lukasz Pawlowski – Program Manager SSRS)
- You can host the SSRS 2008 content catalog (ReportServer and ReportServerTempDB databases) under either a 2005 or 2008 SQL Server instance
- For Reporting Services, if you have SSRS deployed to one server and SharePoint on another, regardless of the SSRS add-in, only the Reporting Services machine will require a SQL Server license
- The SharePoint Configuration and Content databases will require a separate SQL Server license if stored on a different server than the Reporting Services Report Server databases
Database Usage:
SharePoint Configuration Database
Every SharePoint Web application has a configuration database that stores application settings. When you set up an instance of Windows SharePoint Services or Office SharePoint Server 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.
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 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.
Joe Salvatore | MicroLink Business Intelligence Architect