Setting up integrated SSRS and SSAS servers using AutoSPInstaller

Apr 13, 2012 at 10:21 PM

Hi Folks,

I have a sharepoint 2010 environment with 2 WEB FRONT END SERVERS, 2 APP SERVERS and 2 DATABASE SERVERS (IN A CLUSTER).

The roles and service applications running on the individual servers in the SharePoint 2010 Farm are as follows:

Web Server 1 (SharePoint Server 2010 and Office Web Applications)

  • Web Server Role
  •  Application Discovery and Load Balance Service
  • SharePoint 2010 Web Services

Web Server 2 (SharePoint Server 2010 and Office Web Applications)

  • Web Server Role
  • Application Discovery and Load Balance Service
  • SharePoint 2010 Web Services

App Server 1 (SharePoint Server 2010 and Office Web Applications)

  • Application Server Role
  • Service Proxy Role
  • Access Database Service
  • Application Registry
  • Business Data Connectivity Service
  • Central Admin Service
  • Excel Calculation Service
  •  InfoPath Forms Service
  • Managed Metadata Service
  • Performance Point Service
  • PowerPoint Service
  • Secure Store Service
  • SharePoint Timer Service
  • State Service
  • Visio Graphics Service
  • Usage and Health Service
  • Web Analytics Service
  • Word Automation Service

App Server 2 (SharePoint Server 2010 and Office Web Applications)

  • Application Server Role
  • Crawler and Indexing Role
  • Query Role
  • Service Proxy Role
  • Search Service
  • Search Admin Service
  • SharePoint Timer Service
  • User Profile Service
  • User Profile Sync Service

DB Cluster (SQL Server 2008 R2)

  • Database Server Role
  • Failover Services

I am looking to extend this environment to include SSRS and SSAS as well. I am thinking of installing SSRS in SharePoint integrated mode on App Server 1 since based on my research I felt that SP integrated mode would be the best way to leverage both SSRS and SharePoint. For the SSAS piece, I found that installing SSAS in SharePoint integrated mode only has some limitations. So, I am thinking of installing that as both native and SP integrated mode. However, I am not sure whether I should install both the components (Native Analysis Services Engine Service and SP Integrated/PowerPivot System Service) on the existing App Server 1 or a completely new Server (maybe App Server 3), assuming that App Server 2 is dedicated for search only. Another option I am wondering about is whether the PowerPivot System Service can be installed on the existing App Server 1 while the native Analysis Services Engine Service on a new server (App Server 3). 

As far as the storage/database layer for the SSRS is concerned, I am thinking of using the existing database cluster for the Reporting Server database on the same database instance that already exists. And for the SSAS cube storage, I am thinking of having a separate server for the Analysis Services Engine, although still not clear whether it needs to be clustered or not.

It would be great if you guys can share your experience on SharePoint environment that includes SSRS and SSAS as data providers, preferably in SP integrated mode. Also, what are the changes you'd have to make in the AutoSPInstallerInput.xml. If you need any additional information, I'd be more than happy to provide that.

Thanks in advance!