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=14.0.0.0, 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

Comments

• Anonymous
  October 26, 2011
  So my question is where do you have to license SQL / SSRS in this scenario?

• Anonymous
  October 28, 2011
  Pricing/Licensing and SKU information is still being determined.  We will be publishing more information on this topic on the official Microsoft SQL Server blog.

• Anonymous
  January 28, 2012
  Did ya'll make improvements to the performance of the Reporting Report Viewer in SharePoint with IE 9? It was notorious for being very slow verses other browsers and even integrated mode run much more slower than native mode. Hopefully so, but thought maybe you would know?

• Anonymous
  January 28, 2012
  Nevermind, found a great article that says this. Yay!.

7. Performance – For Reporting Services you’ll now see parity between SharePoint and SQL 2012 mode. Reports use to be 2-3x slower. As well it’s 30% to 60% faster out of the gates than SQL 2008 R2 as a whole. Column query processing on the same hardware can be 10x faster, with reduced IO, less tuning required (Search for more on this in project Apollo.) The SharePoint Conference Keynote demo showed how responsive a recordset with millions and millions of records could still be sub second response times across the list and search queries. Source www.sharepointjoel.com/.../Post.aspx

• Anonymous
  February 03, 2012
  Sorry for the late response, as you mention we made investments in the performance of the SharePoint mode, you can see the details in this blog entry from my friend Prash blogs.msdn.com/.../performance-of-ssrs-in-sharepoint-mode-reports-in-full-page-view-in-sharepoint-2010.aspx

• Anonymous
  March 13, 2012
  The comment has been removed

• Anonymous
  March 14, 2012
  Yes you can, your could have the SharePoint databases and the Reporting Services databases running in a SQL Server 2008R2 SQL engine, but to use Power View with PowerPivot you will need to install the new version of Power Pivot in your farm. Also you could use Power View consuming an external SQL AS Server installed in tabular mode

• Anonymous
  September 11, 2012
  Nice Article ,,

• Anonymous
  June 08, 2013
  Do you need an extra sql  2012 license for this setup?

• Anonymous
  March 26, 2015
  The comment has been removed

• Anonymous
  December 03, 2015
  The comment has been removed