Add PowerPivot Servers to a SharePoint Farm
If you anticipate a high volume of requests for PowerPivot query processing in a SharePoint farm, you can configure a scale-out deployment by adding SQL Server PowerPivot for SharePoint instances to new or existing application servers in your farm.
Use the instructions in this topic to add additional PowerPivot servers to a SharePoint farm. These instructions assume that you already have a PowerPivot server in the farm, and that you are adding a second server to handle additional processing load. Except for differences in installation requirements, post-install configuration, and verification, the steps for deploying a scale-out solution are identical to adding a single PowerPivot server to an existing farm.
This topic contains the following sections:
Prerequisites
Determine the Analysis Services Service Account
Install PowerPivot for SharePoint
Verify Installation for Additional PowerPivot Service Instances
Troubleshoot Installation
Post-Installation Steps
Prerequisites
You must install the PowerPivot server instance on a computer that has a deployment of SharePoint 2010. The SharePoint server must be joined to the same farm that includes the other PowerPivot server instance.
You must be a local administrator on the computer to run SQL Server Setup.
You must be a farm administrator to add Analysis Services to the farm. If the farm administrator account is not a member of the local Administrator group, you should add it now so that you can install PowerPivot for SharePoint and add the service to the farm. You can remove the account from the Administrator group after Setup is finished.
You must know the name of the database server for the farm.
You must know the service account under which the existing Analysis Services service runs.
In a multi-server farm, all PowerPivot instances must be at the same version. If you applied service packs or updates to other PowerPivot servers that are already in the farm, the new instance you are deploying must be updated to the same version after Setup is complete. After installation, the new instance will be unavailable until any necessary updates have been applied.
Note
Adding PowerPivot for SharePoint to a farm topology requires that you understand which server components are required for different configurations. For more information about installation order and to learn about client library requirements for dedicated servers in a farm, see Plan a PowerPivot Deployment in a SharePoint Farm.
Determine the Analysis Services Service Account
SQL Server Setup will prompt you to specify a service account for the Analysis Services service instance. You must specify the same service account that is used by other Analysis Services service instances in the farm. The account must be a domain user account. If the service runs under a built-in account such as Network Service or Local Administration, change it before you add a second server to the farm. For more information, see Plan a PowerPivot Deployment in a SharePoint Farm.
Use either approach to determine the service account:
In Central Administration, in the Security section, click Configure Service Accounts. Select Windows Service – SQL Server Analysis Services. After you select the service, the service account name will appear in the page.
On a server that already has a PowerPivot service installation, open the Services console application in Administrative Tools. Double-click SQL Server Analysis Services. Click the Log On tab to view the service account.
Important
Only use Central Administration to change service accounts. If you use another tool or approach, permissions will not be updated correctly in the farm
Install PowerPivot for SharePoint
Run SQL Server 2008 R2 Setup to install SQL Server PowerPivot for SharePoint using the existing farm option.
On a computer that already has an installation of SharePoint 2010, insert the installation media or open a folder that contains the setup files for SQL Server 2008 R2.
Run SQL Server Setup with administrator permissions. Right-click Setup.exe and select Run as Administrator.
Click Installation on the navigation pane to the left.
Click New SQL Server standalone installation or add features to an existing installation.
In Setup Support Rules, click OK if no problems were detected. Otherwise, follow the instructions on the page to correct the problems and then restart Setup. Click Install to continue.
In Installation type, select Perform a new installation of SQL Server 2008 R2. This page only appears if you already have an existing instance installed. Do not select Add features to an existing instance. PowerPivot for SharePoint must be installed as a new instance.
In Product Key, specify Evaluation edition or enter a product key for a licensed copy of the Enterprise edition. Click Next.
Accept the Microsoft Software License Terms of agreement, and then click Next.
In Setup Support Files, click Install.
In Setup Support Rules, click Next.
In Setup Role, select SQL Server PowerPivot for SharePoint.
Also on the Setup Role page, in Add PowerPivot service to:, select Existing farm. Click Next.
In Feature Selection, view a read-only list of the features that will be installed. This list is displayed for informational purposes. You cannot add or remove items that are preselected for this role. Click Next.
In Installation Rules, click Next.
In Instance Configuration, a read-only instance name of 'PowerPivot' is displayed for informational purposes. This instance name is required and cannot be modified. However, you can enter a unique Instance ID to specify a descriptive directory name and registry keys. Click Next.
In Disk Space Requirements, verify you have sufficient disk capacity to install the feature, and then click Next.
In Server Configuration, specify a domain user account for SQL Server Analysis Services. This is required for installing Analysis Services in a SharePoint farm. For more information about account recommendations, see Plan a PowerPivot Deployment in a SharePoint Farm. Click Next.
In Analysis Services Configuration, click Add… and enter the account used by the PowerPivot service application that is installed on an existing PowerPivot server in the farm.
To get the required account information, use SharePoint Central Administration and open the Configure Service Accounts page in the Security section.
Setup will grant this account system administrator permission on the Analysis Services service instance. You can change this account later after Setup is finished. For more information, see Change Service Accounts and Passwords (PowerPivot for SharePoint).
In the same page, add the Windows user account of any person who requires administrative permissions on the Analysis Services instance. Any user who wants to connect to an Analysis Services service in SQL Server Management Studio to troubleshoot database connection problems or get version information must have system administrator permissions on the server. Add the user account of any person who might need to troubleshoot or administer the server now.
Click Next.
Click Next on each of the remaining pages until you get to the Ready to Install page.
Click Install.
Verify Installation for Additional PowerPivot Service Instances
To verify PowerPivot query processing on the server you just installed, do the following:
In Central Administration, open the Manage services on server page to confirm that the server and its services appear.
In Server, click the down arrow, click Change Server, and then select the server that has the new PowerPivot for SharePoint installation.
Verify that SQL Server Analysis Services and SQL Server PowerPivot System Service are started.
In Central Administration, stop other PowerPivot for SharePoint servers so that the server you just installed is the only one available. For more information, see Start or stop services on a PowerPivot for SharePoint instance.
Click a PowerPivot workbook to open it from the library.
Click a slicer or pivot the data to initiate a query. The server will load PowerPivot data in the background. In the next step, you will connect to the server to verify the data is loaded and cached.
Start SQL Server Management Studio from the Microsoft SQL Server 2008 R2 program group in the Start menu. If this tool is not installed on your server, you can skip to the last step to confirm the presence of cached files.
In Server Type, select Analysis Services.
In Server Name, enter <server-name>\powerpivot, where <server-name> is the name of the computer that has the new PowerPivot for SharePoint installation.
Click Connect.
In Object Explorer, click Databases to view the list of PowerPivot data files that are loaded.
On the computer file system, check the following folder to determine whether files are cached to disk. The presence of cached files is further verification that your deployment is operational. To view the file cache, go to the \Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup folder.
Restart the services that you stopped earlier.
Troubleshoot Installation
If you get errors instead of the pages and features you expect, do the following:
Review release notes for both SharePoint 2010 and SQL Server 2008 R2 to get workarounds for known installation problems. Release notes are provided with the installation media or on the Microsoft site from which you downloaded the software.
Review the Troubleshooting PowerPivot for SharePoint Installation Problems page on the Microsoft TechNet WIKI.
Refer to SharePoint deployment articles on the Microsoft web site. The articles include steps for configuring settings in Internet Explorer. For more information, see Deploy a single server with SQL Server.
Post-Installation Steps
No further steps are required. You do not need to create service applications, activate features, deploy solutions, or change service accounts. Existing Web applications and service applications will discover and use the new server software automatically.