Tuesday, December 10, 2013

SQL Server Reporting Services (SSRS) 2012 Integration with SharePoint 2013 - Step by Step Guide

This implementation guide walks through step by step on configuring SQL Server Reporting Services 2012 Integration with SharePoint 2013.

What’s New in SSRS 2012 with SharePoint 2013?

Unlike its previous versions, SSRS 2012 with SharePoint 2013 installation & configuration has been greatly simplified.  There are lots of changes in installing & Configuring Reporting services 2012 compared with its previous version SQL Server 2008 R2.  Reporting Services is now a service application in SharePoint 2013!

No more SQL Server instance of type SharePoint integrated Mode! Just Install features “Reporting Services - SharePoint” and “Reporting Services Add-in for SharePoint Products” during SQL Server 2012 installation.

We don't have to touch "Reporting Services Configuration Manager" to create Reporting Services databases, setup service accounts, etc. Also we don't have to go to Central Admin >> General Application Settings >> and Configure Reporting Services Integration, as they are part of service applications now!  These steps should be performed for SQL Server 2008 R2 Reporting services integration with SharePoint 2013/2010 still! But not for SQL Server 2012!!
Its strongly recommended that having SQL Server 2012 SP1 (http://www.microsoft.com/en-us/download/details.aspx?id=35575) and SQL Server 2012 CU-7 (http://support.microsoft.com/kb/2894115) for flawless SSRS integration with SharePoint 2013.

SQL Server Reporting Services with SharePoint Integration - Topology:
SQL Server Reporting Services with SharePoint Integration Topology

Steps Overview

  1. Install the Reporting Services Features in SharePoint Application Server.
  2. Register SSRS Service in SharePoint App Server
  3. Create Reporting Services Service Application in SharePoint Central Administration

Step 1: Install the Reporting Services Features in SharePoint Application Server.

I've used my SharePoint Central Administration Server as a Report Server. You can also choose a dedicated App server where SharePoint 2013 is installed for Report Server role. Lets start.
  1. Login to your Identified Report Server as a Administrator, Mount SQL Server installer media, Start the SQL Server 2012 installer and click on “New SQL Server Stand-alone Installation or Add features to an existing installation” link Install SSRS 2012 Features for SharePoint 2013
  2. Choose either New installation or add feature to existing installation based on your scenario. In the Reporting Services Configuration step, Install features “Reporting Services - SharePoint” and “Reporting Services Add-in for SharePoint Products".SSRS Components for SP 2013
  3. Run through the installer to complete the installation. You have to Install SSRS Add-in for SharePoint products on all other WFE servers either by running above wizard and choosing only “Reporting Services Add-in for SharePoint Products” or from a separate Add-in download from internet.
Important:
Once you install/Update, SSRS Add-on, Don't forget to run SharePoint Products Configuration Wizard again.

Step 2: Register SSRS Service in SharePoint App Server

Open SharePoint 2013 Management shell as an administrator, Run these three PowerShell cmdlets to Install SSRS Service & Proxy on SharePoint Application Server which serves a Reporting Server.
Install-SPRSService

Install-SPRSServiceProxy

Get-SPServiceInstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

This installs the core SSRS services into the SharePoint Farm, provisions and starts the service instance.

Now, if you go to Central Administration >> Services on server, you’ll see “SQL Server Reporting Services Service” is started.

SSRS 2013 Service in SharePoint Services on Server
Step 3: Create Reporting Services Service Application in SharePoint Central Administration

Now, the SSRS service is installed and available to SharePoint. Go to:
  1. Central Admin >> Manage Service Applications >> New >> SQL Server Reporting Services Service Application. Create New SSRS 2013 Service Application
  2. Create the new SQL Server Reporting services service application by filling details such as Service application Name, Service account, Database Name (remember to remove the GUID from the database Name!), Web Applications to associate. SQL Server 2012 Reporting Services Service App Creation
  3. Wait for the successfully created service application message. Click on the “Provision Subscriptions and Alerts” link (This step is optional, however).SSRS 2012 Integration with SharePoint 2013
  4. Click on the "Download Script" button.  Copy the SQL Query and execute it in your SQL server. This script adds the RsExecRole to the Reporting Services dbs. Fill in the User name, Password to Run SQL Server agent and check the box for windows credentials.
  5. Now we’ve the Service application ready. You can go back to Manage Service Applications and configure SSRS specific settings.
    Reporting Services 2013 settings on SharePoint 2013 Central Admin
Verify the Reporting services Integration features
  • Navigate to Site Settings >> Site Collection Features.
  • Click on Site Collection Features under site collection administration
  • Check if the Report Server Integration Feature is Active, if not just click activate
Report Server Integration Feature for SharePoint 2013
Unit Testing:
Create a new document report library, Enable content type and add "SQL Server Reporting Services Report" content in it. Build a SSRS report using either Report Builder tool or with Visual Studio. Verify whether the SSRS runs smooth and reports are getting rendered on the site.  I'll publish an another article on building SSRS reports on SharePoint 2013.

References:



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

1 comment :

  1. Thanks for the clear write up on this service!
    I have an existing 2008R2 SSRS running. It will be upgraded to 2012R2. Can I setup the SP integrated SSRS to point to this existing report server and use that to pull reports from?

    Cheers!

    ReplyDelete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...