PowerPivot for SharePoint SQL Server Instance

Question

I am a bit confused on PowerPivot SQL Server Instance in SharePoint 2010.  Can someone answer the questions below?

My current setup: 

1. SQL Server 2008 R2 SQL Instance

2. SQL Server 2008 R2 Analysis Instance

If I want to install PowerPivot, does it automatically create a new SQL Server Instance and another Analysis Instance call PowerPivot?  So I would end up with TWO SQL Server Instances and TWO Analysis Service Instances?  If this is not correct, what is the correct way?

Answer 1

 

Hi Steve,

 

 

Given that your setup is on a single box, and you also have SharePoint 2010 Enterprise on that box, you may install PowerPivot for SharePoint. And yes, it will install a new instance of AS in that box named POWERPIVOT. In this instance you won't be able to load any regular molap cubes. This POWERPIVOT instance is on VertiPaq mode (everything is in memory), and it's designed to load the embedded databases from the PowerPivot-enabled Excel workbooks.

 

Did I answer your question? Please let me know if I can help you any further...

 

 

Thanks,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 2

Hi Mariano,

Thank you for getting back to me.

We have a multi tier setup for our environment.

1. ServerDB is our db server with SQLServer Service Default Instance and SQL Server Analysis Default Instance already installed.  This server has all the SharePoint databases on there.

2. We have other web servers for SharePoint components etc.

Are you suggessting that we should NOT install the SQLServer Service PowerPivot on the ServerDB?  That we should install PowerPivot on a web server, which will create a new SQL Instance\PoverPivot and AnalysisService\PowerPivot?

I am guessing that ServerDB should only have SQL Instance\PoverPivot and AnalysisService\PowerPivot instances, where both the PowerPivot database and SharePoint databases reside on this single SQL Instance.  So instead of 2 SQL Instance and 2 Analysis Service Instance, we would only have 1 SQL Instance and 1 Analysis Service instance all PowerPivot?  Please let me know if this is wrong.

Thank you

Steve

 

Answer 3

 

Hello Steve,

 

PowerPivot for SharePoint installs a AS instance named POWERPIVOT, and this instance is in VertiPaq mode. PowerPivot for SharePoint will not install on your ServerDB unless it has SharePoint 2010 Enterprise in that box. I'm not saying you shouldn't do it, because I don't know the specifics of your needs and the resources you have available. I'm just saying that in case your ServerDB is just a server hosting your databases, PowerPivot for SharePoint won't install.

Your ServerDB will host, likely, SharePoint's databases and any other SQLServer (relational) databases and Analysis Services databases (not powerpivot). And depending on the rest of your SharePoint farm, you can choose where to install PowerPivot for SharePoint.

 

Does that help? If not, just let me know. :)

 

 

Best,

Mariano

 

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.