Issues and Benefits Implementing SharePoint 2013 using SQL Server 2008 R2 vs SQL Server 2012 SP1

As many of our customers are getting ready to deploy SharePoint 2013, choosing the appropriate version of SQL is critical!! SQL Server 2008 R2 SP1 is the minimum required version of SQL for a database server in a SharePoint 2013 farm (Hardware and software requirements for SharePoint 2013), however, there are impacts of using SQL Server 2008 R2 If you are deploying the enterprise edition of SharePoint 2013 and you are planning on using Access Services 2013 or some of the Business Intelligence (BI) features as they require SQL Server 2012 SP1. In this blog post I'm gathering information to highlight some of these impacts, and show the benefits of using SQL Server 2012 SP1.

Here is a list of SharePoint 2013 Enterprise Edition Service Applications and components that will require SQL Server 2012 SP1 or maybe impacted if using SQL Server 2008 R2:

Access Services 2013:

Requires:

  • SharePoint Server 2013 on at least Windows 2008 R2
  • SQL Server 2012 Standard or SQL Server 2012 Enterprise
  • SQL Server 2012 Feature Pack Components on the SharePoint Server:
    • Microsoft SQL Server 2012 Local DB (SQLLocalDB.msi)
    • Microsoft SQL Server 2012 Data-Tier Application Framework (Dacframework.msi)
    • Microsoft SQL Server 2012 Native Client (sqlncli.msi)
    • Microsoft SQL Server 2012 Transact-SQL ScriptDom (SQLDOM.MSI)
    • Microsoft System CLR Types for Microsoft SQL Server 2012 (SQLSysClrTypes.msi)

 The following are the software prerequisites for creating and modifying Access apps:

  • Access 2013 (required for Access app design)
  • A web browser (required for viewing and updating data)

Please note Access Services 2010 is there for backward compatibility only!! By default, you cannot create a web database by using Access 2013. However, you can still view and edit a web database that was previously created by using Access 2010 and SharePoint Server2010

For more information, see White Paper: Office 2013--Access Services Setup for an On-Premises Installation

Excel Services Application:

When Excel Services 2013 is deployed with SQL Server 2008 R2, users should be able to interact with basic Excel workbooks in the browser, however, to be able to use the new Excel 2013 features such as advanced data models, an instance of SQL Server 2012 SP1 CTP3 or CTP4 Analysis services is required.

The following table summarizes the different levels of BI features you can choose to install and configure.

Level

Features

Install or Configure

SharePoint Only

Native Excel Services Features

Excel Services and other services included with SharePoint Server 2013.

SharePoint with Analysis Services in SharePoint Mode

Interactive PowerPivot workbooks in the browser

  • Install Analysis Services in SharePoint mode.
  • Register Analysis Services Server in Excel Services.

SharePoint with Reporting Services in SharePoint Mode

Power View

  • Install Reporting Services in SharePoint mode.
  • Install Reporting Services add-in for SharePoint.

All PowerPivot Features

  • Access to workbooks as a data source from outside the farm.
  • Schedule Data refresh.
  • PowerPivot Gallery.
  • Management Dashboard.
  • BISM link file content type.

Deploy PowerPivot for SharePoint 2013 add-in.

 

Report server and Reporting Services Add-in

SharePoint 2013, can ONLY use the SQL Server 2012 SP1 version of the Report Server and Reporting Services add-in for SharePoint. For more info see Supported Combinations of SharePoint and Reporting Services Components.

Power View in SharePointServer 2013

Requires SQL Server 2012 Service Pack 1 (SP 1) editions that enable Power View and Silverlight 5. See System requirements for Power View

PowerPivot for SharePoint 2013

SQL Server 2012 SP1: To be able to use the new Excel 2013 features such as advanced data models on SharePoint Server 2013, an instance of SQL Server 2012 SP1 CTP3 Analysis Services must be installed in SharePoint deployment mode.

 

As we can see if you need to deploy Access Services 2013 or take full advantage of the new features of the BI stack, you will need SQL Server 2012 SP1. SQL Server 2012 SP1 offers many new features such as SQL Server Backup and Restore with the Windows Azure Blob Storage Service, and Cross-Cluster Migration of AlwaysOn Availability Groups in addition to the following enhanced SharePoint BI features, here is an excerpt from What is new in SQL Server 2012 for SharePoint 2013 BI TechNet article.

What’s new In SQL Server 2012 SP1 for Business Intelligence (With Office and SharePoint Server 2013)

Summary:

In summary, If you are serious about the BI features of SharePoint 2013 and the power of the new Access Services 2013 Service Application, you will need SQL Server 2012 SP1. This is how I look at it, you’ll have to upgrade off SQL 2008 R2 at some point, why not skip a migration and start on SQL Server 2012 SP1!!

Resources:

Software requirements for business intelligence in SharePoint Server 2013

What’s new In SQL Server 2012 SP1 for Business Intelligence (With Office and SharePoint Server 2013)

SQL Server BI Features with SharePoint 2013

System requirements for Power View

Supported Combinations of SharePoint and Reporting Services Components.

White Paper: Office 2013--Access Services Setup for an On-Premises Installation