Considerations for Installing SQL Server PowerPivot for SharePoint
SQL Server 2008 R2 Setup is used to both install and optionally configure SQL Server PowerPivot for SharePoint. Configuration is supported through the New Server option, available for systems that satisfy the prerequisites for this installation type.
This topic contains information and recommendations for:
Before you install, review the following requirements and component descriptions:
In this release, Analysis Services is implemented in the shared service architecture that SharePoint 2010 provides. The new SharePoint service architecture enables an administrator to easily associate services with Web applications, and switch roles or features as necessary. The farm takes on a modular composition where arbitrary servers and applications can be enabled, configured, and discovered by other member servers that run in the same farm. Analysis Services works in this environment because SQL Server Setup installs and configures it as a shared service.
Although the new architecture makes it easy to enable features or services for any given application, the services still have a permanent residence on a physical computer in the farm. When you run SQL Server Setup, a physical service instance of Analysis Services is deployed on the local server. The physical service instance is a licensed copy of SQL Server Analysis Services that provides both a query processor and a new systems management interface that supports interaction with other applications and components in the farm.
To add PowerPivot for SharePoint to a farm, you must run SQL Server Setup on each physical computer in the farm that you will use to provide large scale data processing. If your scale-out requirements call for three PowerPivot server instances, you must run SQL Server Setup on each of the three computers to install a physical instance on each one.
After the physical installation process is complete, you can manage the service application as a logical entity by configuring it for just those SharePoint web applications that will use the PowerPivot for SharePoint features. If you install two or more PowerPivot server instances, they will be utilized as a single system. The actual processing of any given request can occur on any of the physical server instances you install.
SQL Server 2008 R2 Setup introduces a new setup role installation option that deploys a SQL Server feature component in a specific configuration. In this release, Setup includes two setup roles for installing SQL Server PowerPivot for SharePoint:
Existing Farm option. Use this option to add PowerPivot for SharePoint to a farm or server that is already configured. With this option, you must manually configure PowerPivot for SharePoint after installation.
New Server option, recommended for single-server deployments that place SharePoint, Excel Services, Analysis Services, and other PowerPivot server components on the same computer. This option automatically configures all services and features.
PowerPivot for SharePoint is always installed as a fixed setup role, separately from other SQL Server feature components, including other instances of Analysis Services that support MOLAP, ROLAP, and HOLAP storage options. The setup role ensures that required components and features are pre-selected so that Setup can deploy PowerPivot for SharePoint in a working state and that only one instance of PowerPivot for SharePoint is installed on each physical server.
The following table lists the SQL Server features and shared components that are installed when you choose a PowerPivot for SharePoint setup role.
Analysis Services service
Analysis Services in SharePoint integrated mode provides in-memory data storage and calculations of compressed PowerPivot data in Excel 2010 workbooks.
PowerPivot service application (for New Server only)
A shared service application that provides an endpoint to the PowerPivot System Service.
PowerPivot solution packages (Deployed by SQL Server Setup for New Server only)
Used to deploy PowerPivot for SharePoint on a web front end and to copy application files to servers in the farm.
Powerpivotfarm.wsp is a solution package that is applied globally. It adds templates, content types, and web parts to the farm.
Powerpivotwebapp.wsp is a solution package that is applied after you create a PowerPivot service application. A pre-defined service application is required for deploying this solution.
Database Engine Service (for New Server only)
When you select the new farm configuration option, Setup installs a named 'PowerPivot' instance of the database engine to use as the database server in the farm. In addition to the database engine, Setup also adds the following services:
SQL Server Agent service
SQL Server Browser service
SQL Server Management Studio (for New Server only)
This tool is installed because it is useful for troubleshooting and diagnosing connection problems to PowerPivot data that is loaded by the Analysis Services service instance.
SQL Server Import and Export Wizard (for New Server only)
This wizard is installed as a core component of the database engine service. It is a general purpose tool for moving data between SQL Server relational databases.
Configuration tools (for New Server only)
Configuration tools are provided for administering SQL Server services and databases:
SQL Server Configuration Manager
SQL Server Error and usage Reporting
SQL Server Installation Center
Performance tools (for New Server only)
Performance tools are included in installations of Database Engine instances:
Database Engine Tuning Advisor
SQL Server Profiler
SQL Server Books Online
Depending on how you install SQL Server PowerPivot for SharePoint, Setup will configure several service accounts using a combination of default accounts and information you provide. For each account, Setup will configure the following:
Analysis Services service
This service account is specified during Setup using domain user account information that you provide. A domain user account is required. For more information about account requirements, see Plan a PowerPivot Deployment in a SharePoint Farm.
PowerPivot service application pool
The service account of the PowerPivot System Service is the identity of the PowerPivot service application pool. This account must have administrative permission on the Analysis Services service instance. SQL Server Setup grants these permissions automatically.
For New Server installations, the application pool identity is set to the Server Farm account. You can specify a different identity in Central Administration after Setup is complete.
Farm account (for New Server only)
For new server configuration only, you must specify a domain user account for running essential services and for accessing the configuration database.
Farm administrator (for New Server only)
For new server configuration only, a farm administrator, service administrator, and primary site collection administrator will be configured based on the account of the user who ran SharePoint Server Setup and SQL Server Setup. You can delegate administrative roles to other users in SharePoint Central Administration after Setup is complete.
Primary site collection administrator (for New Server only)
For new server configuration only, the primary site collection administrator for the default SharePoint Web application is identical to farm administrator. Use SharePoint Central Administration to delegate this role to other users.
SQL Server Database Engine system administrator, Analysis Services system administrator(for New Server only)
SQL Server Setup requires that you specify a system administrator for both the Database Engine and Analysis Services. As the person running setting, you should add yourself to the System Administrator roles for both servers. You can use SQL Server Management Studio to delegate these roles to other users after Setup is complete.
For more information about service account configuration, see Change Service Accounts and Passwords (PowerPivot for SharePoint).
Although you can only have one instance of a PowerPivot service on a computer, you can install other Analysis Services instances on the same physical computer to support native mode operations (MOLAP, HOLAP, and ROLAP). To perform a side-by-side installation, do the following:
Install Analysis Services using the Feature Selection page in the Installation wizard. Analysis Services is installed as the default SQL Server instance, or as a named instance that you define.
Install PowerPivot for SharePoint using a Setup Role in the installation wizard. Analysis Services is installed as the 'PowerPivot' named instance.
Choose one of the following links when you are ready to install PowerPivot for SharePoint:
Use the SQL Server Installation Wizard to deploy PowerPivot as quickly as possible, without significant advance planning or in-depth knowledge of SharePoint products. In this scenario, Setup both installs and pre-configures settings so that server features are ready to use.
Use the SQL Server Installation Wizard to deploy PowerPivot on a system that already has SharePoint 2010 installed. SQL Server performs a minimal installation by copying files and making them available in the SharePoint environment. SharePoint administrators complete the deployment by enabling the PowerPivot software.
SQL Server 2008 R2 includes multiple features that integrate with SharePoint. Use these installation instructions to learn how to install Reporting Services and PowerPivot for SharePoint in the same farm.
You can scale out a PowerPivot for SharePoint deployment by installing PowerPivot software on additional application servers.
You can run Setup from the command line to perform an unattended install.