Setting up an integrated SSRS server using AutoSPInstaller

Jun 8, 2011 at 5:58 PM

Let me start by saying this looks like a great project that will save me a bunch of time and improve consistency while setting up my SP2010 farms!

One of the things that I need to do is set up a dedicated server within the farm that runs SQL Server Reporting Services (SSRS) in SharePoint integrated mode. The general process for this is to:

  1. install the SharePoint bits
  2. join the server to an existing farm
  3. configure the server as a WFE, but do not use it to host web sites
  4. install and configure SSRS

I haven't seen anything here that talks specifically about this type of install, if I missed it please point me in the right direction. I assume that what I should do is modify the AutoSPInstallerInput.xml file for this server to:

  • not provision / start / configure any of the services or service applications since they will not be running on this server
  • I think the managed accounts can be left since they already exist in the farm
  • remove all of the WebApplication nodes since I don't want these to run on the server

Eventually I will be creating dedicated servers for the service applications and for search/crawl. I presume that similar modifications would apply, but leaving the "Provision" property on appropriate service applications set to TRUE for servers they will run on or FALSE for WFEs where they will not.

Does that sound right? Did I miss anything?

Thanks!

Dec 7, 2011 at 4:19 PM

Hi Mover

Did you get any information on this. I too an look out for the same.

Dec 7, 2011 at 4:56 PM

What I ended up doing was creating a couple different flavors of the AutoSPInstallerInput.xml file that provision (or don't) services on various servers. It pretty much follows the suggestions in the original message. I have 3 flavors:

  1. WFE - doesn't provision services, does contain the <WebApplications> section.
  2. SSRS - doesn't provision services, does not contain the <WebApplications> section.
  3. Services - does provision services, does not contain the <WebApplications> section.

This has worked pretty well for me. I've been doing the Service install first and setting up Central Admin to run there, then not provisioning it on other servers. You can always go back later (through CA) and enable / disable services on different servers as necessary.

Installation and configuration of SSRS is still being done manually on the appropriate servers in the farm.

Hope that helps!

Mike:o

Apr 13, 2012 at 10:27 PM

Hi Mover,

Did you install SSRS in SharePoint Integrated mode or native mode? In my research, I found that you can install SSRS in integrated mode on the SharePoint App Server (which hosts other SP services such as Secure Store Service, PerformancePoint Service etc.). I am looking to extend my current SP environment to include SSRS and SSAS as well. I have created a separate discussion post for that. From your post, I wasn't clear whether you added SSRS from the get go or you had already established your SP environment and then added SSRS later. Can you please throw some more light on your scenario and the rationale around choosing native vs. SP integrated mode for SSRS depending upon which route you chose to implement?

Thanks in advance!

Apr 16, 2012 at 1:52 PM

SSRS (2008 R2) was set up in SharePoint Integrated mode. I had it on a separate machine from the SharePoint services. I'm now in the process of tearing out the 2008 R2 SSRS and replacing it with the 2012 version. If you haven't installed SSRS yet, you should definitely consider using 2012 since it runs as a SharePoint service and therefore has much better integration of the management.

When you say that you're looking to extend your current SP environment to include SSAS, are you talking about PowerPivot for SharePoint (PP4SP)? If it's PP4SP, again I'd suggest that you consider the 2012 version. If you're talking about "regular" Analysis Services (which doesn't integrate with SharePoint, but can be accessed from SP or SSRS, you'll definitely want to install it on its own dedicated server with no SP installed on it.

Hope that helps - let me know if you have more questions!

Mike:o

Apr 16, 2012 at 3:55 PM

Hi Mover,

Thanks for your reply. Here are some follow-up questions:

1. I am assuming you had to install the SharePoint on the separate machine where you installed SSRS in SP integrated mode and then join it to the SP farm. Did you have the ReportDB and ReportTempDB on this machine or on the SharePoint DB server? If you installed the ReportDB and ReportTempDB on the SharePoint DB server, then was it on the same database instance or a separate database instance?

2. Are you aware of any functionality that is missed out when you install SSRS in SP integrated mode? I am pretty sure I want to go with SP integrated mode due to the obvious benefits but I would like to know if SSRS native mode has some features that will not be available in the integrated mode.

3. Did you have to do any sort of prep work before running the auto installer script for the SSRS install? For example, was there a need to set up certain AD accounts etc. ahead of time? If so, will you be able to share any information regarding that? Any documentation/script will be great but even general pointers will be very helpful since I haven't done the automated install myself before.

4. Which server (database server or the separate machine for SP integrated SSRS assuming they are on separate machines) you had to kick off the install process? This question is based on the assumption that the installer is run once and it installs all the components in various server tiers.

5. Also, my initial impression was that SP integrated SSRS install has app server and database server pieces to it. However, after further research I get a sense that it actually has web server and database server pieces but no app server piece, at least for 2008 R2. By web server pieces I mean SSRS Proxy and Report viewer web part etc. Is this correct? From what you mentioned about SSRS 2012, it looks like that has app server piece since you said that it runs as a SharePoint service in 2012. This is what I originally thought with respect to 2008 R2 SSRS as well.

6. Have you installed the SP integrated SSRS on a single machine or in a cluster? 

7. As far as Analysis Services is concerned, since we are just starting to set that up, I am looking to install both "regular" and "SP integrated" Analysis Services. It sounds like I should set that up on a separate machine as far as "regular" Analysis Services is concerned. However, do you have any thoughts/ideas on which machine should the SP integrated Analysis Services be installed? Do you have Analysis Services installed in your environment? If so, which one (regular or integrated)? 

Your suggestion about using 2012 version is very timely. I'll look into it and try to figure out the licensing and other aspects before firming up that path, not to mention understanding the new features and capabilities and how it maps to the existing system architecture. However, that'll take some time so I'll continue to explore the system architecture using 2008 R2 components meanwhile.

I appreciate your time and feedback and look forward to your response.

Thanks in advance!

 

Apr 16, 2012 at 5:41 PM

Answers to your questions:

  1. Correct - the SharePoint bits do need to be installed on the SSRS server and it needs to be joined to the farm, but it does not need to run any services or serve as a WFE. We created the SSRS databases in the same server / instance as the rest of the SharePoint databases.
  2. Sorry, I'm not that familiar with native mode SSRS, since I'm a SharePoint guy.  :)  I know that when you set SSRS up in SP-Integrated mode, most (2008R2) or all (2012) interaction and management is through SharePoint. You don't even set up the "Report Manager" URL because all publishing / scheduling of reports occurs through SP document libraries.
  3. I never did script the SSRS install. I used AutoSPInstaller to install / configure SP on the server and then ran through the SSRS install manually. SQL Server 2008R2 doesn't really support PowerShell from what I can see. 2012 appears to be much better, but I did those manually as well because wasn't really familiar enough with the process to feel comfortable scripting it.
    If you'll be using Kerberos to delegate identity to backend data sources, give some thought to the service accounts that you're using for SSRS relative to SP. We tried to follow best practices for setting up least privileges accounts throughout our environment. Todd Klindt has a pretty good post.
  4. SSRS installer runs on the server where you want SSRS to run. Since it's already joined to the SP farm it has access to everything SharePoint. During the install process you provide the database server (and name) for the SSRS-specific databases.
  5. Correct, SSRS 2008R2 really uses SP Web server components on the server. Database components could be installed here I suppose, but we use a dedicated SQL server for all databases. SSRS 2012 runs as a SP service instead and is managed entirely through the Central Admin Services management pages.
  6. I've done both. The scale-out cluster requires Enterprise licensing for SQL, you can get by with Standard in a non-clustered implementation. In 2012 you'll need to use either the BI or Enterprise version to get some of the advanced capabilities (like Power View, Alerting & Scale-out). Here's a good reference: http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx#Reporting
  7. When you say "SP Integrated Analysis Services" are you referring to PowerPivot for SharePoint? I am not aware of any other integration between SharePoint and SSAS...   Our DBAs have been designing & building SSAS databases on dedicated servers for a number of years and we leverage them through SP with both SSRS (integrated) and PerformancePoint. We've used PowerPivot integration with Excel for a while as well, but are just starting to dip our toes into the PP4SP pond.
Apr 17, 2012 at 3:45 AM

Hi Mover,

Thanks for answering all my questions. Your responses have been very helpful to me and I am very grateful to you for that.

You are right about the last item (#7). I am really referring to PP4SP when I say "SP Integrated Analysis Services". Does the PP4SP installer also run on WFE or on app server? (I am assuming it runs on WFE) From what I have gone through so far, it looks like PP4SP should install components on WFE (e.g.PowerPivot Web Service,PowerPivot Management Dashboard etc.), app server (e.g.PowerPivot System Service, Analysis Services Service etc.) and database server (e.g. PowerPivot Application database). Is this correct? Also, my understanding is that the PowerPivot Application database stores temporary objects and settings used by PowerPivot to do the PowerPivot reporting. The actual data for the PowerPivot reporting comes from the PowerPivot System Service and Analysis Services Service which connects to the OLAP cube as the data source. Please let me know if this understanding is correct or not.

In your environment, do you have all the components of PP4SP installed on the same machine dedicated for SSAS or do you have separate server for PP4SP? If PP4SP server is separate, are all of the PP4SP components installed in a single server or on different servers (WFE, app and db)? Please let me know.

And again, Thanks in advance!

Apr 17, 2012 at 6:37 PM

I'm not familiar with the 2008R2 PP4SP installer. in 2012, you run it on the application server where the PP2SP service is going to run and all of the WFE components (in the form of solutions) get deployed to other servers in the farm as part of the configuration.

What you say about the various PowerPivot components sounds correct from my recollection, but I'm pretty new to it and would have to go back to the doc's to verify.  :)

We have an app server in the farm dedicated to PP4SP. It might be able to run on the same app server as SSRS, but since I'm not certain what load either will create I've chosen to separate them.

Apr 17, 2012 at 11:10 PM

Thanks a bunch! I truly appreciate your response.