SQL2012 SSRS on a SharePoint 2010 Farm using SQL2008R2

 

SQL 2012 SSRS contains two important enhancements.

1. Data can be exported using Excel 2007/2010 file formats – users can now forget the Excel 2003 row limitations when exporting reports.
2. Data Driven Alerts based user specified rules and schedules. Rules can be set on specific datasets and when triggered email can be sent out to individuals or groups

In addition SQL 2012 SSRS

1. Is easier to deploy
2. Is part of the SharePoint Service Application Architecture
3. Uses Claims to Windows Token Service to simplify delegation scenarios
4. Supports PowerView in the browser for a rich reporting experience
5. Managed in SharePoint instead of multiple locations
6. Support for claims-based authentication
7. SSRS events are logged to SharePoint ULS

Most of the SharePoint implementations used are SharePoint 2010 and in most cases after just getting everything stable, businesses do not want to move either to SharePoint 2013 or SQL 2013 just yet. So this article deals with how to make use of SQL2012 SSRS on an existing SharePoint 2010 farm that is using SQL2008R2. This will work on multi-server farms as well as on a single server farm.

There are two methods by which SSRS can be deployed. In both cases and .rdl files that exist are not destroyed. They do need to have their data mapping revised.

 

Method 1: Using the Existing SQL2008R2 database server(s). [Preferred Method]

1. Pre-requisites:

• Windows Server 2008R2 operating system with SP1 as a minimum with all the security patches on all SharePoint servers (WFE and application)
• .Net 4.0 on all SharePoint servers (WFE and application). This is available on the SQL2012 installation media or it can be downloaded from Microsoft.
• SQL 2008R2 SP2 has to be installed at a minimum.
• Check through Central Admin that all the correct servers in the farm are showing up and there are no extra ghost servers.

2. Installation:

• Using the SQL Install Media install install the  Reporting Services – SharePoint and Reporting Services Add-In for SharePoint Products on all  SharePoint servers
• Run the SharePoint Administrative Shell as administrator and register the service by running the two commands “Install-SPSRSService” and “Install-SPSRSServiceProxy”. On successful deployment in each case just the shell prompt will be returned (no message will be displayed).   
• Start the service by using the command “get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance”
• Configure SSRS (through Central Admin) and create the ReportingDB on the SQL2008R2, SP2 database server
• Use SSRS

 

Method 2: Using a SQL2012R2 database for Report Services on one of the Front End servers.

This is a method that should be used for testing purposes only of if a client wants to use it in production they should be made aware that unless there is a proper backup procedure and redundancy this has a single point of failure for the Reporting services.

1. Pre-requisites:

• Windows Server 2008R2 operating system with SP1 as a minimum with all the security patches on all SharePoint servers (WFE and application)
• .Net 4.0 on all SharePoint servers (WFE and application)
• Check through Central Admin that all servers in the farm are showing up and there are no extra ghost servers

2. Installation

• Using SQL 2012 Install Media install the Database Engine,  Reporting Services – SharePoint, Reporting Services Add-In for SharePoint Products and SQL Server Data Tools on one of the SharePoint Servers (preferably an Application Server)
• Using SQL 2012 Install Media install the  Reporting Services – SharePoint and Reporting Services Add-In for SharePoint Products on all the remaining SharePoint servers
• Run the SharePoint Administrative Shell as administrator and register the service by running the two commands “Install-SPSRSService” and “Install-SPSRSServiceProxy”. On successful deployment in each case just the shell prompt will be returned (no message will be displayed).
• Start the service by using the command “get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance”
• When configuring SSRS (through Central Admin), point the database to the SharePoint server that contains the SQL2012 Database instance
• Use SSRS

Notes:

1. Though there is a download called rsSharePoint.msi that is available from Microsoft this represents only the Reporting Services Ad-In for SharePoint Products and by deploying this download on all the SharePoint servers I have not been able to get SSRS working.
2. Since the license key (or evaluation) is required with the SQL component install check with your Microsoft Rep what the licensing requirements of your particular organization are to deploy this in production.