What database support is needed

I get this question a lot and I thought that it was a good one to address here in the blog.

If you look at the PowerPivot for SharePoint architecture, you know that we use several SQL Server 2008 R2 components within the architecture, but how about SharePoint itself . . . What RDBMS does PowerPivot with SharePoint 2010 use? and does it need to be R2?

The answer is that we use the farm’s regular SharePoint RDBMS database instance. We do NOT need SQL 2008 R2 relational engine support for the SharePoint farm. SharePoint 2010 requires 64-bit SQL Server 2005 SP3 or later (yes, 64-bit is required), see http://technet.microsoft.com/en-us/library/cc262485(office.14).aspx for more details. And PowerPivot lives quite happily in that environment.

But you know the answer cannot be quite that straightforward as just that, so here is a more in-depth answer. When you install PowerPivot using the EXISTING FARM option, we simply take whatever RDBMS is configured with the existing SharePoint Farm. This can be SQL Server 2005 SP2 or later. We ensure that any resources that we use on that farm, such as the RDBMS database that each PowerPivot service application creates, is done so in such a way that we are compatible with SQL Server 2005. Our stored procedures use 2005 syntax; we don’t use any 2008 TSQL commands or syntax.

But for NEW FARM, we do lay down a SQL Server 2008 R2 RDBMS. Remember that for NEW FARM, then scenario is a single machine farm and that farm is going to be created and configured automatically by the SQL Server 2008 R2 setup. You don’t have to do anything on the SharePoint side – we do everything for you. Given that you have nothing on the server, and we are inside an R2 setup environment, the RDBMS that we lay down and configure is R2 (surprise, surprise, surprise). Thus in this environment (NEW FARM), you do get an R2 RDBMS.

The second aspect to this question (oriented to data refresh) is: What RDBMS does PowerPivot with SharePoint 2010 pull data from? and does it need to be R2? The answer to this is that data refresh will pull data from whatever provider is specified on the connect string and what versions of its data source that it supports. As the data provider is being called in-process from the AS engine, you need 64-bit versions of the providers and ODBC drivers. If using SQL Server, this typically means the SQL Native Client, which means PowerPivot data refresh pulls data from SQL Server 2000, 2005, 2008 or 2008 R2 – 32-bit or 64-bit (like most providers, the ‘bit-ness’ of the SQL Native Client does not dictate the ‘bit-ness’ of the data source itself). If using other providers, take a look at the versions of their software that they connect to and support. For example the Oracle provider might only support V9i or earlier.

BTW: So far all of this topic was about how PowerPivot connects to data outside itself. Internally all of PowerPivot components are 64-bit SQL Server 2008 R2 versions: the web services are obviously R2, but so are the msolap OLEDB provider, ADOMD.NET, AMO, the SSAS engine (in vertipaq mode), and everything else.