SQL Server 2012 Reporting Services SharePoint integrated mode


General Architecture

One of the interesting changes we did in Reporting Services was to change our SharePoint mode from a Windows Service to become a SharePoint Shared Service, there are many benefits on that one like integrated administration experience and integrated scale out capabilities to mention some of them.

First at all I had to learn about the SharePoint Shared Service architecture and the components so they can be identified in a configuration.

In general the Shared Service Applications in SharePoint uses this Architectural View


Databases: Reporting Services Shared Service creates and use three databases Catalog, TempDB and Alerting, you have the flexibility to configure it in any SQL Server Instance you prefer (including the same instance that is hosting the SharePoint Databases), this databases are created when you create the Service Application (the full details on how to install Reporting Services SharePoint Mode are here)

Service Machine Instance: In a SharePoint Farm with multiple mid tier servers you decide which servers process reports installing the “Reporting Services – SharePoint” feature from the SQL Server Setup and running the Install-SPRSService cmdlet

Service Application:   Is the logical application that you configure in the Farm, for Reporting Services each Service Application has it own set of databases

Service Proxy:   The proxy is a reference to the Service Application, is the actual endpoint which the consumer talks and know how to talk with the load balancer to access the Service Machine Instances available.

Consumer: The Reporting Services web pages including the Report Viewer or any application that uses the Reporting Services SOAP API.

One thing that was confusing for me at the beginning is that you can have multiple Service Applications each one with its own set of Databases, configuration and name. But that does not means you can decide which one is executed in a specific machine, as soon you install the binaries in a Server and run the  Install-SPRSService cmdlet it will host all the Reporting Service Applications you have created or will create.

This diagram is useful to clarify the relationships


To summarize

  • Can be multiple Reporting Services Service Applications in the Farm
  • Each individual machine can start or stop the Shared Service, that means it will stop/start all the Reporting Services Service Applications in the machine
  • The Setup of the Feature “Reporting Services – SharePoint” just lay down the bits , you need to let know SharePoint that you want to run that Service Application , that is accomplished with the Install-SPRSService cmdlet, in that moment the machine becomes a Service Machine Instance
  • All the Service Applications are going to be running in all the Service Machine Instances in the Farm.
  • Each SharePoint Web Application can connect to different Reporting Services Service Application.
  • SharePoint automatically provides load balancing and Scale out capabilities across all the Service Machine Instances in the Farm

Where is rsreportserver.config?

One of the consequences of this new Architecture is that you don’t need to configure manually each one of the machines that belong to the Farm independently, the configuration for the Reporting Services Shared Application is in the SharePoint configuration Database, one time configuration will take effect in all the machines in the farm.

Most part of the configuration that was inside the rsreportserver.config under C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config in Reporting Services 2008R2 is not there anymore, you can use the UI to manage the Service Application or  use PowerShell and a new set of cmdlets that are installed with Reporting Services (the full list is here)

Usually there is not need to use the cmdlets except for this scenarios I have identified from questions in the forums:

1. Install the RS Shared Service the first time (a good post about it by the PM Prash Shirolkar here )

2. Configure NTLM authentication for the E-mail (the documentation details are here)

3. Configure data extensions like when you need to Use Access Services with SQL Server Reporting Services

For the data extension for Access Services you can use the following script, which need to be run in the SharePoint 2010 Management Shell

$apps = Get-SPRSServiceApplication
foreach ($app in $apps)
    New-SPRSExtension -identity $app -ExtensionType “Data” -name "ADS" -TypeName "Microsoft.Office.Access.Reports.DataProcessing.AdsConnection, Microsoft.Office.Access.Server.DataServer, Version=, Culture=Neutral, PublicKeyToken=71e9bce111e9429c"

The first cmdlet used is Get-SPRSServiceApplication which will return all the Reporting Services Service Applications available in the farm, you can use the modifier fl (format list) to get the full details of the Reporting Services Service Applications in your Farm

The second is New-SPRSExtension which register the new extension in the Service Application, is the equivalent to add the xml tag to the old rsreportserver.config