We wanted to test out SQL Server Reporting Services within SharePoint 2013 but did not want to have everything running all on one server. We found some steps here but that essentially described a single server installation. Therefore we needed to figure out what we needed to do which wasn't too hard.
Our staging servers included the following:
Web Front End
Database Server
Reporting Server
The Reporting Server is essentially an Application Server that is joined to the SharePoint farm. It will run the SSRS Service Application. Running SSRS integrated mode now means that it is fully under SharePoint (thus reducing any double-hop issues).
Here is the high-level installation effort we implemented to make it work:
You now need to register and start the SQL Server Reporting Services on the server using PowerShell commands within the SharePoint 2013 Management Shell (on the Report Server):
Install-SPRSService
Install-SPRSServiceProxy
get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
I originally did not have this documented and didn't realize I missed this part until I tried to add another Reporting Services server to the staging farm and the service did not appear in the Services for Server. Ahhh.
The Service Appears under Services on Server (only when looking at the Reporting Server):
To add another Report Server to the farm, follow only the steps under the Report Server section.
Our staging servers included the following:
Web Front End
Database Server
Reporting Server
The Reporting Server is essentially an Application Server that is joined to the SharePoint farm. It will run the SSRS Service Application. Running SSRS integrated mode now means that it is fully under SharePoint (thus reducing any double-hop issues).
Here is the high-level installation effort we implemented to make it work:
Web Front End
1. Install SP2013 Prereqs (aka SharePoint Preparation Tool)
2. Install SharePoint Server
3. SharePoint Products Configuration Wizard - (Create new farm)
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 them yet)
Report Server
1. Install SP2013 Prereqs (aka SharePoint Preparation Tool)
2. Install SharePoint Server
3. SharePoint Products Configuration Wizard (to join to
Farm)
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
get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
Back on the Web Front End:
1. Install SQL Server 2012 selecting ONLY the Reporting
Services Add-In for SharePoint
2. Install SQL Server 2012 SP1 for SharePoint 2013
Support
Now in Central Admin, SQL Server Reporting Services appears
as a new Service Application to create:
Create the new SSRS Service Application.
To add another Report Server to the farm, follow only the steps under the Report Server section.
Hi Steve, this article has answered a lot of questions, thank you for posting it!
ReplyDeleteI have a few questions which I hope you can help with.
1) What hardware specs have the three servers got?
2) You mentioned you install SharePoint and SQL server on both the web and report server. Did you require separate licences?
That was in staging which needs to be rebuilt at the moment. In production the servers have between 12-16GB and 4-8 CPUs. For production you need a SharePoint license for the WFEs and Report Servers. I believe you only need the SQL Server license for the SQL Server box. We beefed up the Reporting Services servers. I can get you more specifics later this week..
Deletehi steve,
ReplyDeletei got following error when clik on “System Settings” on SSRS Service App.
Sorry, something went wrong
Access denied.
can you help me with this error?
thanks
pontjo
Make sure your Execution Account is properly configured as well as qualified with the domain name.
DeleteDid you install the SSRS on the SharePoint Application Server running Central Admin or a non-Central Admin SP App Server? The reason we ask is tried installing on the non-Central Admin App server and found that the Service Application wasn't showing up in Central Admin. The only fix we found was to install it on the Central Admin App server, scale out to the other SP App Server we wanted it running on, and then turn off the Central Admin Server's copy. The concern is over licensing of the SP App server piece. We know that the Add-in piece doesn't require licensing, but by SQL Server license guidelines, we figure the SSRS piece that goes on the SP App server does require SQL Server License.
ReplyDeleteI always install on an app server. The PowerShell commands should make it available. RS integrated should be part of the SharePoint Enterprise license so you wouldn't need an additional SQL Server license.
DeleteI hope you can give some guidance on this... I have a 4 server farm( 2 load balanced WFE, 1 app server with CA, one app server with Reporting services installed).
ReplyDeleteAfter all servers are members of the farm with SP2013 installed. After I installed RS and the Add-in on our RS server, it took several attempt to get teh Add-in installed on the CA server, but finally did. However, I cannot install the add-in on either of my WFE machines. To help troubleshoot this, I ran the rsCustomAction.exe and this is part of the message I receive...
=========================================================
Performing configuration task 2 of 3
Installing the application content files...
The SharePoint Central Administration Web Application content files will not be
installed because a Central Administration Web Application is either off-line or
does not exist on this server.
Installing the SharePoint Web Application content files...
Failed to install the application content files.
An exception of type System.NullReferenceException was thrown. Additional excep
tion information: Object reference not set to an instance of an object.
Total number of configuration settings run: 2
Total number of successful configuration settings: 1
Total number of unsuccessful configuration settings: 1
Successfully stopped the configuration of SharePoint Products.
Configuration of SharePoint Products failed. Configuration must be performed be
fore you use SharePoint Products. For further details, see the diagnostic log l
ocated at d:\SharePointLogs\PSCDiagnostics_11_11_2013_19_50_16_958_2001659079.lo
g and the application event log.
=============================================================
CA should not be on the WFE machines, but why would it think it is "off-line"? I have run the config wizard on all the servers multiple times and rebooted the servers multiple times, but I can not get the add-ins installed on the WFE's.
Any help is appreciated.
Sounds like something "ain't right" with your CA App Server. I would remove CA and then put it back on using a different port. If that doesn't work then I would remove from farm and then add back to farm. Then run the config wizard on the other servers. Continue with the RS add-in.
DeleteCentral Admin - check you IIS setting and try to restart the CA - web application in the IIS and try to browse to your local host. (Hoping this isn't your prod if so, then plan appropriately)
DeleteI've just configured SSRS for SP2013 (2WFEs and 2APPs with 1 SQL Instance) - SQL Server 2012 SP1 on production, this are my steps;
Make sure to install using your SharePoint install account.
1) Install SQL Server 2012 Reporting Services Add-In for SharePoint ONLY on all WFEs - download link to add-in http://www.microsoft.com/en-us/download/details.aspx?id=29068
2) For Reporting Services for SharePoint - Run the SQL Server 2012 SP1 ISO - and follow the wizard only to install feature 'Reporting Service - SharePoint' on all Application servers. You must Reboot all your application servers after installation is complete.
Check central Admin
it should automatically register SQL Server Reporting Service Application. you should be able to view this in central admin, (Server Farm Services Running, drop down in Service Application and SQLSRSS should stated Started)
If this isn't the case,
do the following,
• You now need to register and start the SQL Server Reporting Services on the server using PowerShell commands within the SharePoint 2013 Management Shell (on the Report Server):
o Install-SPRSService
o Install-SPRSServiceProxy
o get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
Good luck
Regards
V
We have a 2-server (web and app server plus a database server) SharePoint 2013 farm. We would like to add SharePoint-integrated mode SSRS 2012 to it. We were thinking of installing SSRS SharePoint mode on a different SQL box (existing database server). However, It seems like in SQL Server 2012, Reporting Services SharePoint mode needs to be installed on the SharePoint app server and cannot be installed on any database server. Is that right? If that is the case, does that mean we need to license the SharePoint server for SQL Server as well?
ReplyDeleteYou are correct on the installation. I believe you just need SharePoint Enterprise and the respective Enterprise CALs. I would check with your Microsoft rep as it never seems to be black and white.
DeleteHi Steve,
DeleteWe have the same license issue for few customers (3-tiers topology). MS is not clear on that topology. Do you have any feedback about this ? Thank you for you help.
Clément.
Should be 1 SQL Server License and 2 SharePoint Licenses and then Enterprise CALs for users accessing Reporting Services.
DeleteHi Steve
ReplyDeleteWe have a strange error with SQl Server Reporting Service on Sharepoint 2013 and SQL Server 2012. Our environment is as follows.
SharePoint Server with CA and SSRS in integrated mode for Sharepoint Server. Enterprise License is enabled
SQL Server 2012 on a different BOX
We also have Dynamics AX in our environment
We have created SQL Server Reporting Service in Sharepoint pointing to our SQL Server but when we connect to the Power View funcitonality as a current logged IN user it works fine for a 1st time but then it starts showing error. If we use stored credentials it works fine. If we restart the SQL Reporting it works fine again the first time. Note we have not installed any components of Sharepoint on the SQL Server 2012 BOX.
When you say "stored" do you mean the Secure Store Service or the Execution Account?
DeleteFor anyone out there that is having issue installing the SSRS SharePoint add-in, I spent about 6-8 hours today trying different installation scenarios only to find out that if you have any commented code in your web.configs it will cause the add-in to rollback the installation.
ReplyDeleteI tried this with both the SQL 2012 SP1 media and the standalone SP add-in.msi.
I had and once I removed it, the installer completed successfully!
Little victories...
Do you know of any articles discussing the features of the integration? what does it do? All the articles are talking about how to integrate, none are focusing on what we can do once integrated...
ReplyDeleteI've installed this and someone is asking me what the URL is for "SQL Reporting Services 2008 and 2008 R2" under General Application Settings. In pretty much none of the documentation I found online does it specify a need to configure this area + I installed the Reporting Add In and Reporting Add in for SharePoint on my one App/WFE isn't it just integrated in there?
ReplyDeleteYes it will be part of SharePoint:
Deletehttp://<>/_layouts/15/ReportServer/
<> = SharePoint Host Name
DeleteTechnically, I believe you should use _vti_bin/ReportServer but it depends on what you are trying to accomplish.
DeleteHere is a post for some insight: http://stevemannspath.blogspot.com/2013/11/sharepoint-2013-producing-pdf-report.html
Dear Steve,
ReplyDeleteThanks for your article, excellent!
I have a very basic query - I have 3 tier architecture of SP 2013 that means SQL, Front End, Application are 3 different servers.
I need to install SSRS in integrated mode
It was installed in SQL but then I removed as I need it on Front End to make it available for SharePoint.
Could you kindly assist, would appreciate your feedback.
Thanks
athars21@hotmail.com
Follow the steps I have in this post. Your Application would be the Report Server. It will be the server running the service for the Reporting Services service application.
DeleteNice article. It explained everything about SharPoint 2013 and ssrs integration.
ReplyDeleteI have a weird question, If the SSRS reports deployed to SharePoint 2013, when they retrieved with in Sharepoint depends on filters, where they will be executed. Will they be executed at SharePoint level or SSRS level.
Reason is, ssrs reports in SharePoint are getting timeout issues and etc.
Thanks.
Technically both. The SSRS Application Service should be doing most of the work, however, it is running on your SharePoint farm. There are many tips out there to speed up report rendering. My guess is that you have other service applications running in the same box and there are not enough resources to process the report in a timely fashion.
DeleteAlso, since you are using filters, which I assume are report parameters, you may be running into "parameter sniffing" issues. See here: http://stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs