Existing Farm Install

DRAFT: THIS IS A DRAFT OF A WHITEPAPER CURRENTLY BEING WRITTEN. IT MAY BE UPDATED AND WE INVITE YOU TO POST COMMENTS WITH SUGGESTIONS, CORRECTIONS AND QUESTIONS TO THIS PAGE. WE WILL CONSOLIDATE THEM AND INTEGRATE THEM BACK INTO THE BODY OF THE POSTING. AT SOME FUTURE TIME THIS INFORMATION WILL BE PART OF AN OFFICIAL MICROSOFT WHITEPAPER – BUT UNTIL THEN, WE CAN ALL BENEFIT FROM YOUR INPUT. THANKS.

Use this document to configure a PowerPivot Server on a SharePoint farm if:

1. You already have a SharePoint 2010 farm installed and configured and you have identified the servers that you want to install PowerPivot on. Go directly to step 10 if this is the case.

-OR-

2. You want to set up a multi-server SharePoint 2010 farm and then install PowerPivot on the servers of your choice. (Please note that SharePoint 2010 requires a 64-bit platform and Windows Server 2008 SP2 or later.) You will be guided through the steps to create a farm that looks like the following. It should be relatively straightforward to adapt these instructions if your farm is slightly different.

 

1. Resources and Links

You can download Office 2010 SharePoint Beta and SQL Server 2008 R2 November CTP from:

• Office 2010 SharePoint Beta: http://technet.microsoft.com/en-us/evalcenter/ee388573.aspx
  Remember to write down your product key for this download. You will need it during SharePoint installation.
• SQL Server 2008 R2 November CTP: http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx

2. Install SQL Server on the machine you designate as the DB Server

a. Navigate to the folder where you downloaded SQL Server 2008 R2 November CTP, launch setup.exe and install the SQL Server relational engine.

b. Select both Database Engine Services and Management Tools.

c. Ensure that Named Pipes and TCP/IP are enabled in the SQL Server Configuration Utility.

To do this, go to All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager. Click SQL Native Client 10.0 Configuration > Client Protocols and ensure that the Named Pipes and TCP\IP client protocols are enabled. If applicable, repeat this step for the 32-bit version of the SQL Native Client 10.0 Configuration (32bit). Also, click SQL Server Network Configuration > Protocols for MSSQLSERVER and make sure that Named Pipes and TCP/IP are enabled.

d. You must open the server’s firewall ports for SQL Server access. In this case, this is TCP port 1433. To do this, click Start > Run, type firewall.cpl. Click Allow a program through windows firewall. You will find an option to add a port there.

3. Install SharePoint 2010 on the WFE

a. Navigate to the folder where you downloaded Office 2010 SharePoint Server, right-click PrerequisiteInstaller.exe and select Run as administrator.

b. After step a. finishes successfully, from the SharePoint build location, run setup.exe.

c. If you see the following message:

Install KB971831 from http://go.microsoft.com/fwlink/?LinkID=160770 and then run setup.exe.

Important: If you are running Windows Server 2008 R2 (Windows 7 Server), setup will not prompt you to install a QFE. Install KB976462 from http://go.microsoft.com/fwlink/?LinkID=166231 and then run setup.exe.

e. Accept the terms and conditions and click Continue.

f. Click Server Farm and then click Next.

g. On the Server Type tab, choose Complete.

h. On the File Location tab, leave the default settings and click Install Now.

4. Run the SharePoint configuration wizard on the WFE

a. After the installation in Step 4 is complete, you will get a prompt asking if you want to run the Configuration Wizard. Verify that the check box selected and click Close.

b. On the Welcome page, click Next.

c. When prompted, click Yes.

d. On the Connect to a server farm page, select Create a new server farm, then click Next.

e. On the Specify Configuration Database Settings page, specify the machine name of the database server and the Windows account (Needs to be a domain account) used to access the database. This must be the account used to install SQL Server.

f. On the Specify Farm Security Settings page, type a pass phrase that meets the minimum requirements.

Important: Make note of your pass phrase, as you must have it to add machines to your farm).

g. On the Configure SharePoint Central Administration Web Application page, accept the default port, or specify a port number of your choice. Leave the default security settings (NTLM) and click Next.

h. On the Completing the SharePoint Products and Technologies Configuration Wizard page, click Next to start the configuration process.

5. Run the Initial Farm Configuration Wizard on the WFE

In this step, you will configure the default web application, site collection for your farm and provision Excel Services and Secure Store Service which are required by PowerPivot.

a. On the Configure your SharePoint farm page, verify that the Walk me through the steps using the wizard. option is selected. Click Next.

b. Choose the service account that SharePoint will use to provision services. You can use an existing managed account or create a new one.

c. On the same page, you can choose the SharePoint services that you want to run on your server. Excel Services and Secure Store Service are required to successfully configure PowerPivot on your server. So make sure these two are selected and click Next.

Note: If you have chosen many services, the processing may take a long time to complete. Also, it is a good practice to only install the services you need to reduce the load on your server.

d. It is recommended that you create site collection here. Enter a name for your default site collection and then click OK.
This completes the configuration of your SharePoint farm.

e. To complete this configuration, click Finish.

f. You must open the server’s firewall ports for SharePoint Web access – which are port 80 (this should already be opened under the program of World Wide Web Services (HTTP)) and the SharePoint Central Administration Web Application port specified in Step 5f. To do this, click Start > Run, type firewall.cpl. Click Allow a program through windows firewall. You will find an option to add a port there.

g. Finally, you will need to set a Key for the secure store service application that was created for you in 5.c . To do this, click Start > All Programs > Microsoft Office Server 14 and then click SharePoint 4.0 Central Administration. Navigate to Application Management > Manage Service Applications, select Secure Store Service and then click Manage at the top of the page. At the top of the page, click Edit and then click Generate New Key. You will be prompted for a passphrase. (This is used to encrypt the credentials stored in the Secure Store Database.) Please enter a passphrase of your choice here.

6. Install SharePoint 2010 on PowerPivotServer1

a. Repeat Step 3 on PowerPivotServer1; after this step is completed go to Step 7.

Important: Do not repeat Steps 4-5.

7. Run the SharePoint configuration wizard on PowerPivotServer1

a. After the installation in Step 6 is complete, you will get a prompt asking if you want to run the Configuration Wizard. Verify that the check box is selected and click Close.

b. On the Welcome page, click Next.

c. On the Connect to a server farm page, select Connect to an existing server farm.

d. On the Specify Configuration Database Settings page, specify the machine name of the database server. The configuration database is automatically retrieved for you when you click Retrieve Database Names.

e. On the Specify Farm Security Settings page, type in the passphrase you specified in Step 4 f. This allows your server to join the farm you have created earlier.

f. On the Completing the SharePoint Products and Technologies Configuration Wizard page, click Next to start the configuration process.

g. When the Configuration Wizard completes, you will be asked if you want to start all services running on the farm on ‘PowerPivotServer1’, click Yes.

8. Install and Configure SharePoint Server 2010 on PowerPivotServer2 and PowerPivotServer3

a. Next, repeat Step 6 and Step 7 for ‘PowerPivotServer2’ and ‘PowerPivotServer3’.

9. Confirm Farm Setup

a. To view your farm configuration from any one of the servers, navigate to SharePoint Central Administration, click System Settings, and then click Manage servers in this farm. This displays the farm information up to this point. An example screenshot is below:

Notice that it shows all of the servers within your farm and what services are running.

10. Run Existing farm setup on PowerPivotServer1

a. From the folder where you downloaded SQL Server 2008 R2, launch setup.exe.

b. Click Installation > New SQL Server Stand-alone installation. Select the default option on each page of the wizard until you reach the Feature Role page. (You can choose Enter a product key and leave this blank when prompted for a key. If not, make sure you select the same edition that you selected in step 7.)

c. On the Setup Role page, select the Analysis Services with SharePoint Integration option and then select Existing Farm from the drop down menu.

d. Click Next.

e. Click Next again.
The Instance Configuration page should look similar to the following image. The instance ID must be GEMINIBI.

f. Continue through setup and select defaults until you reach the Server Configuration page. Specify the service account for your SQL Server Analysis Services instance in the Account Name and Password fields. Leave the default value for the SQL Server Browser Service account and click Next.

g. Continue selecting defaults until you get to the Analysis Services Configuration dialog.

h. On the Analysis Services Configuration page, make sure to select the Add Current User check box to ensure that your service account has access to the Analysis Services instance.

i. Continue through the rest of the Setup wizard and select the default for each option, then click Install.

11. Run Existing farm setup on PowerPivotServer2 and PowerPivotServer3

a. Next, repeat 10 for PowerPivotServer2 and PowerPivotServer3.

b. After you complete this step for each server, navigate to SharePoint Central Administration > Servers in Farm. Your screen should look similar to the screenshot below:

12. Deploy PowerPivotWebApp solution to web application(s)

a. On the WFE, open the Central Administration web site and go to System Settings -> Manage Farm Solutions -> PowerPivotWebApp.wsp -> Deploy solution.

b. Choose the web application that you want to deploy PowerPivotWebApp.wsp to and click on OK.

c. Repeat this step, on any other web applications that you want the PowerPivotWebApp.wsp to be deployed to.

13. Create PowerPivot service application.

a. On the WFE go to Central Admin -> Home -> Application Management -> Manage service applications-> New-> SQL Server Analysis Services Mid-Tier Service.

b. Choose existing application pool from the list or create a new application pool.

c. Select the Add the Service Application to the farm’s default group check box at the bottom of the form, if it is not selected, and click OK.

14. Activate the “Analysis Services Integration Site Collection Feature”

a. Open your default web site (http://wfename) and click Site Actions -> Site Settings -> Site collection Administration -> Site collection features.

b. Click Activate button beside the Analysis Services Integration Site Collection Feature.

c. Repeat this step on all other site collections (if any) that you want to activate the Analysis Services Integration feature on.

15. Configure the PowerPivot Unattended account (Optional)

If you will be allowing users to use the PowerPivot service account to schedule a data refresh, you will need to set an ‘unattended’ account. To do this:

To create a target secure store application ID:

a. Click Start > All Programs > Microsoft Office Server 14 and then click SharePoint 4.0 Central Administration. Navigate to Application Management > Manage Service Applications, select Secure Store Service and then click Manage at the top of the page.

b. Click New on the ribbon above

c. On the Target Application Settings page, enter a name of your choice for the Target Application ID and the Display name fields, specify an email address and click Next.

d. On the Credentials Field page, accept the defaults and click Next.

e. On the Target Application Administrators page, enter a user who has access to this target application. You can enter the account you are using to create the target application ID.

f. To return to the Manage page for the Secure Store Service Application, click OK. You should be able to see your target application id on this page.

g. To set the credentials for this application id, select the ID check box for your ID and click Set on the ribbon above.

h. On the Set Credentials page, in the Credential Owner field, type in the PowerPivot Service account. (The account you specified as an AS Administrator when you ran Analysis Services setup.)

i. In the Windows User Name and password fields, enter the credentials of a target application administrator. This should be one of the users you specified in step e. or the farm administrator.

Also, note that the two accounts that you need to specify can be the same if the PowerPivot service account is also a target application administrator for your application id.

To set the PowerPivot Unattended account:

a. Navigate to Application Management > Manage Service Applications, select your PowerPivot Service application that you created in step 13 and then click Manage at the top of the page.

b. After the page loads, click Configure Service Application Settings from the list of actions on the right and on the page that pops up scroll down to the Data Refresh section.

c. Enter the target application ID you created in step I in the Unattended Account field and click OK.

16. Configure the Microsoft ‘Geneva’ Claims to Windows Token Service

This needs to be configured on all the servers that are running Excel Calculation Services. In our current configuration this would be the WFE, PowerPivotServer1, PowerPivotServer2 and PowerPivotServer3.

a. Run services.msc. If Microsoft “Geneva” Claims to Windows Token Service is started, stop it.

b. In a text editor, open the configuration file \ProgramFiles\Microsoft Geneva Framework\ReferenceAssemblies\c2wtshost.exe.config XML file.

c. Replace the entire <allowedCallers> XML structure with the following:

<allowedCallers>

<add value="WSS_WPG" />

<!– <clear /> –>

<!– <add value="NT AUTHORITY\Network Service" /> –>

<!– <add value="NT AUTHORITY\Local Service" /> –>

<!– <add value="NT AUTHORITY\System" /> –>

<!– <add value="NT AUTHORITY\Authenticated Users" /> –>

</allowedCallers>

d. Save and close the file

e. Start the Microsoft “Geneva” Claims to Windows Token Service. (If necessary change the startup type for this service to Automatic.)

f. Reset IIS by typing iisreset at an administrator command prompt.

17. Enable usage data collection and choose events that trigger data collection

a. On the Home page in Central Administration, click Monitoring

b. In the Monitoring section, click Configure usage and health data collection.

c. Select the Enable usage data collection check box and select the events that you want to monitor. To get full functionality from the PowerPivot Management Dashboard, you must enable usage collection for all four PowerPivot events.

d. Click OK.

18. Configure file size limits (Optional)

Out of the box, SharePoint allows you to upload files up to 50MB in size and Excel Services allows you to view files as large as 10 MB within a browser. If users will be publishing and interacting with larger files, you can change these settings.

To increase Web Application limits:

a. Go to SharePoint Central Administration > Application Management > Manage Web Applications.

b. Select SharePoint-80 and then select General Settings -> General Settings from the ribbon on the top.

c. Change the Maximum Upload Size setting to the MB limit you want to set. (The maximum allowed by SharePoint is 2047MB.)

To increase Excel Services limits:

a. Navigate to SharePoint Central Administration > Application Management > Manage Service Applications.

b. Select your Excel Service Application.

c. On the Ribbon, click Manage.

d. Click Trusted File Locations and on the following page, select your trusted location. (Typically, this is listed as http:// in the Address column.)

e. In the Workbook Properties area, set the Maximum Workbook Size property to 2000 and the Maximum Chart or Image Size to 100 MB.

19. Confirm PowerPivot Setup

a. After you install the Excel 2010 and the PowerPivot Add-in for Excel 2010, use the PowerPivot Add-in to create workbook.

b. Publish the workbook to the Shared Documents folder on the default Web site (http://wfename).

c. On the WFE open the default site in a browser, navigate to Shared Documents, right-click the workbook you just published and select Copy shortcut.

d. Open SQL Server Management Studio, click Connect > Analysis Services and paste the link you just copied into the Server Name field.

e. Click Connect.
A connection should appear in the in the Object Explorer and you should be able to browse the cube in your Excel workbook. This confirms that you have PowerPivot set up correctly.

Now that you have successfully installed and configured PowerPivot, you might want to configure Reporting Services on your SharePoint farm to enable additional functionality. Use the Reporting Services – Installation and Configuration document to to install and configure Reporting Services in SharePoint Integrated mode. You can then use Report Builder to create reports off PowerPivot workbooks that you have been published to a PowerPivot Gallery.

II. Troubleshooting

Normally, when performing the above steps, the firewall ports for Excel Services and PowerPivot Services will be opened so that the farm will work without a problem. But if there is a problem, you may need to manually open the following firewall ports:

1) Open Excel Services port 32843 on the PowerPivot Application servers (PowerPivotServer1, PowerPivotServer2, PowerPivotServer3).

2) If you are still running into problems, you may need to open up the ports for the SQL Server Analysis Services Engine service. To do this:

a. On the PowerPivot Application servers, open up firewall ports 2382 and 2383.

b. Again, on the PowerPivot Application servers, modify the Windows Firewall settings to “Add program…” – add the $drive$\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\bin\msmdsrv.exe so that it can communicate through the firewall.