Configure SQL Server Reporting Services With SharePoint 2013

In this article we learn how to configure reporting services integration with SharePoint 2013. SQL Server 2012 Service Pack 1 (SP1) is a version of Microsoft SQL Server 2012 that supports Microsoft SharePoint 2013 Excel Services usage of Excel workbooks containing data models and Reporting Services Power View reports.

What's new in SQL Server 2012 SP1 Installation for Business Intelligence:

• PowerPivot for SharePoint: Analysis Services provides a backend service for Excel Services to load, query, and refresh PowerPivot data models so that users can interact with Excel workbooks that contain data models in the browser. Analysis Services in SharePoint mode is fully independent of, and external to, SharePoint, although the server running Analysis Services must be on the same network and the Active Directory Domains Services (AD DS) forest as the SharePoint farm. You install and manage Analysis Services using SQL Server installation media and tools. Choose the setup option PowerPivot for SharePoint. After you install Analysis Services in SharePoint mode, the only additional configuration tasks are to grant the SharePoint services account's server administrator permissions in Analysis Services and to configure Excel Services to point to the Analysis Services instance.
   
• spPowerpivot.msi: A Windows Installer package available with the SQL Server 2012 SP1 Feature Pack. The installer enhances the PowerPivot for SharePoint experience with more features such as PowerPivot Gallery and Schedule Data Refresh. The .msi deploys Analysis Services client libraries, the PowerPivot for SharePoint 2013 Configuration tool, and copies PowerPivot for SharePoint 2013 installation files to SharePoint servers.
   
• Reporting Services: The overall Reporting Services installation remains the same with SQL Server 2012 SP1 as it was with SQL Server 2012. There is an updated Reporting Services for SharePoint add-in that supports SharePoint 2013.

In this article we are only discussing the reporting services configuration. We will see the remaining sessions, described previously, in future articles.

The following is the server architecture (I am using a three-server farm for the demo):

• Web Front End
• Database Server
• Application Server (I am using it for reporting services.)

The Reporting Server is essentially an Application Server that is joined to the SharePoint farm. It will run the SSRS Service Application. Using the SSRS integrated mode now means that it is fully under SharePoint.

Installation to be done on each server

Web Front End

Use the following procedure to install the Web Front End:

1. Install SP2013 Prerequisites
2. Install SharePoint Server 2013
3. SharePoint Products Configuration Wizard
4. Create Root Web Application (Port 80)
5. Create Root Site Collection
6. Create all Service Applications manually (this was for other purposes; Reporting Services was not one of them yet)
7. Install SQL Server 2012 selecting only the Reporting Services Add-In for SharePoint
8. Install SQL Server 2012 SP1 for SharePoint 2013 Support

Application Server

Use the following procedure to install the Application Server:

1. Install SP2013 Prerequisites
2. Install SharePoint Server
3. SharePoint Products Configuration Wizard
4. Install SQL Server 2012, selecting both Reporting Services and the Reporting Services Add-In for SharePoint
5. Install SQL Server 2012 SP1 for SharePoint 2013 Support

We have the option to use SQL Server 2012 with Service Pack 1.

Database Server

Use the following procedure to install the Database Server:

1. Install SQL Server 2012 Selecting all the options including SSRS and integration and analysis services
2. Install Service pack1

Power Shell Changes

Make the followng changes for/using Power Shell:

1. Click the Start button.
2. Click the Microsoft SharePoint 2010 Products group.
3. Right-click SharePoint 2010 Management Shell then click "Run as administrator".
4. Run the following PowerShell command to install the SharePoint service. A successful completion of the command displays a new line in the management shell. No message is returned to the management shell when the command completes successfully:
   Install-SPRSService
    
5. Run the following PowerShell command to install the service proxy:
   Install-SPRSServiceProxy
    
6. Run the following PowerShell command to start the service or see the following notes for instructions to start the service from SharePoint Central Administration:
   get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
    
7. You can also start the service from SharePoint Central Administration rather than running the third PowerShell command. The following steps are also useful to verify that the service is running.
8. In SharePoint Central Administration, click "Manage Services on Server" in the "System Settings" group.
9. Find SQL Server Reporting Services Service and click "Start" in the Action column.
10. The status of the Reporting Services service will change from Stopped to Started. If the Reporting Services service is not in the list, use PowerShell to install the service.

Central Administration Changes

Make the fllowing Central Administration Changes (If you are done with Power Shell then there is no need to do this configuration):

1. In SharePoint Central Administration, click "Manage Services on Server" in the System Settings group.
2. Find SQL Server Reporting Services Service and click "Start" in the Action column.
3. The status of the Reporting Services service will change from Stopped to Started. If the Reporting Services service is not in the list, use PowerShell to install the service
4. From the Manage Services on the Server click on the SQL Server reporting Services, as shown below:
   
   
    
5. In the screen, you can provide the Service name, database name and managed account details and click "Ok" to start the service; see:
   
   
    
6. It will take some time to configure this; it will lok like:
   
   
    
7. Once all configurations are done successfully, you will get the following screen:
   
   
    
8. You can verify the same in the Manage services session. You will see that the services are started and working fine, as in:
   
   
   

One of the interesting changes that Microsoft did in Reporting Services was to change the SharePoint mode from a Windows Service to a SharePoint Shared Service. There are many benefits of that, two of which are (1) an integrated administration experience and (2) integrated scale out capabilities.

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