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.

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)

• Enable self-service BI as a natural part of users day-to-day activities in Excel 2013:
• Access and mash-up data from any source (PowerPivot). Documentation related to PowerPivot in Excel 2013(https://go.microsoft.com/fwlink/p/?LinkID=255958).
• Stunning visualizations and data discovery (Power View). Documentation related to Power View in Excel (https://go.microsoft.com/fwlink/p/?LinkID=255957).
• Work with hundreds of millions of rows of data (powered by xVelocity in-memory technologies).
• Discover, assess and audit user created spreadsheets via SharePoint Server 2013 Preview.
• A new version of the Reporting Services add-in for SharePoint and an updated SharePoint mode report server that supports SharePoint 2013. For more information, see the following:
  • The "SQL Server 2012 Service Pack 1 (SP1)" section of What's New (Reporting Services).
  • Supported Combinations of SharePoint and Reporting Services Components .
• A new architecture for SQL Server 2012 SP1 PowerPivot that supports a PowerPivot server outside a SharePoint 2013 farm. A Windows Installer package (spPowerpivot.msi) that enhances the PowerPivot for SharePoint experience. Additional features include PowerPivot Gallery, schedule data refresh, and management dashboard. For more information, see the following:
  • The "SQL Server 2012 Service Pack 1 (SP1)" section of What's New (Analysis Services).
  • Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1) .
• Share and collaborate on self-service BI assets via SharePoint Server 2013 Preview and SQL Server 2012 SP1.

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

Comments

• Anonymous
  January 01, 2003
  Very informative, Thanks for sharing!!

• Anonymous
  March 13, 2013
  Thx a lot for this! It saved me a lot of time ;)

• Anonymous
  August 11, 2013
  Excellent and insightful article. Thank you!!! you share very informative information on <a href="http://www.indusa.com”>SharePoint 2013 Services</a>...

• Anonymous
  September 06, 2013
  Great article if you have a choice to use SQL 2012, almost no informtion if you have NO choice but to stay on SQL 2008 R2, at least for the next year. So what can I expect fo lose in functionality by this decision? I am not so concerned the unavailabilty of new features as as I am what will no longer function.

• Anonymous
  September 19, 2013
  Thanks For sharing such a great & useful information... please share more on: http://www.indusa.com

• Anonymous
  December 22, 2013
  Pingback from SQL 2008R2 SP1 Reporting Services for SharePoint 2013 is not supported | Gokan Ozcifci

• Anonymous
  December 22, 2013
  Pingback from SQL 2008R2 SP1 Reporting Services for SharePoint 2013 is not supported | Gokan Ozcifci

• Anonymous
  January 02, 2014
  Pingback from SQL 2008R2 Reporting Services for SharePoint 2013 is not supported | Gokan Ozcifci

• Anonymous
  January 02, 2014
  Pingback from SQL 2008R2 Reporting Services for SharePoint 2013 is not supported | Gokan Ozcifci

• Anonymous
  January 13, 2016
  Nice.. Blog.. Thanks..