PowerPivot for SharePoint

Business intelligence (BI) is a multistep process that traditionally has been heavily dependent on IT. Microsoft PowerPivot breaks with tradition by giving end users the tools they need to own some or all of the process. IT sets up the infrastructure, and end users implement the steps. As Figure 1 shows, end users utilize two tools: Microsoft PowerPivot for Excel 2010 and Microsoft SQL Server PowerPivot for SharePoint.

Figure 1: Traditional approach vs. PowerPivot approach

PowerPivot for Excel 2010 is a free data analysis tool that lets end users gather, store, model, and analyze data. PowerPivot for SharePoint lets end users share that data. I already discussed PowerPivot for Excel 2010 in “A Walkthrough of PowerPivot for Excel 2010”, so I’ll concentrate on PowerPivot for SharePoint here. After I define PowerPivot for SharePoint and describe its architecture, I’ll discuss several common use cases and provide some administration pointers.

Related: Business Intelligence in SharePoint 2010

Definition

PowerPivot for SharePoint is a service that extends Microsoft SharePoint Server 2010 to support PowerPivot workbooks. Specifically, it’s a named instance of SQL Server Analysis Services (SSAS) 2008 R2 Enterprise Edition running in the new SharePoint Integrated mode. In this mode, SSAS is part of a SharePoint 2010 Enterprise environment, with databases and cubes sourced from Microsoft Excel workbooks that have been published to a SharePoint document library.

Architecture

Administrators need to be familiar with the PowerPivot for SharePoint architecture in order to install, deploy, and manage it. I’m not going to review the installation steps here because SQL Server Books Online (BOL) does a good job of explaining how to install PowerPivot for SharePoint to new and existing SharePoint environments. But I do want to review some of the key components, because the actual deployment involves several layers that can be a bit daunting to understand, especially if you’re new to SharePoint.

Figure 2, which is from the white paper "Microsoft SQL Server PowerPivot Planning and Deployment," identifies key architectural components, including:

• Analysis Services service. This component is the named instance of SQL Server 2008 R2 Enterprise Edition running in SharePoint Integrated mode. The instance is always named PowerPivot.
• PowerPivot System service. This service acts as the communication layer for the Analysis Services service. In Figure 2, you’ll see that this system service is wrapped by another box labeled Service Applications. You can think of a service application as a resource that makes a system service available to the sites in the SharePoint farm. The service application is created and configured automatically when installing PowerPivot for SharePoint using the New Server installation option; otherwise, it can be created manually using the SharePoint Central Administration console.
• PowerPivot Web service. This thin middle-tier connection manager is deployed on the SharePoint web front-end server. It detects external requests for PowerPivot data and forwards them to the service application, which in turn communicates with the PowerPivot System service.
• PowerPivot Gallery. Although it’s called the PowerPivot Gallery, this component is a special-purpose SharePoint document library that contains features (e.g., a Silverlight control) to display and work with PowerPivot workbooks. For example, when a PowerPivot workbook is deployed to the PowerPivot Gallery, a thumbnail image of each worksheet is generated to provide a preview of the workbook.
• PowerPivot Management Dashboard. Exposed through the SharePoint Central Administration console, this web page is used to monitor and administer the overall PowerPivot for SharePoint deployment. (I’ll discuss what the dashboard monitors later.)
• SharePoint timer jobs. Similar to SQL Server Agent jobs, SharePoint timer jobs are used to collect statistics and usage data about PowerPivot. SharePoint timer jobs are scheduled and executed through the SharePoint Timer Service.

Note that in terms of physical layout, it’s certainly possible to install all the components on one dedicated server, such as the HP Business Decision Appliance. This appliance is a single-server implementation that can support 60 to 80 concurrent users, equating to hundreds of real-world end users. (For more information, see "HP Business Decision Appliance.") However, larger enterprise deployments will likely involve multiple dedicated servers. The "Technical diagrams (SharePoint Server 2010)" web page provides sample topologies.

Figure 2 has several arrows denoting communication paths between the various components. To help you better understand the paths, I'll walk you through four common use cases:

1. Saving a workbook to the PowerPivot Gallery
2. Viewing a workbook in a browser
3. Interacting with a workbook in such a way that invokes the PowerPivot for SharePoint components
4. Interacting with a workbook in such a way that turns a PowerPivot workbook into a data source

Use Case 1

Suppose that an end user saves an Excel 2010 workbook to the PowerPivot Gallery. In response, SharePoint saves the entire Excel workbook. Remember, the PowerPivot Gallery is just a special type of SharePoint document library.

In this use case, the various PowerPivot web applications and service applications shown in Figure 2 aren’t used. The Excel workbook is simply stored in a SharePoint content database.

Use Case 2

Suppose that an end user wants to use Internet Explorer (IE) to view a workbook in the PowerPivot Gallery, so the person navigates to the gallery and clicks the workbook. In response, IE first communicates with the PowerPivot Gallery. Excel Web Access and Excel Calculation Services then retrieve the workbook from the content database and render it in the browser, as Figure 3 shows. These two components are part of Excel Services, a SharePoint service application that lets you load, calculate, and display Excel workbooks. (For more information about Excel Services, see the “Excel Services Architecture” web page.)

Figure 3: PowerPivot workbook in a browser

Once again, this use case doesn’t involve any of the PowerPivot web applications or service applications. By default, when an Excel workbook is rendered through Excel Services, only a static copy of the workbook is displayed. An additional action must be invoked before PowerPivot for SharePoint gets involved, as the next use case demonstrates.

Use Case 3

Suppose that the end user who opened the workbook in Figure 3 refreshes the data connection. In response, Excel Services first re-queries the external data source. The Analysis Services OLE DB Provider then communicates with the PowerPivot System service, which in turn makes a request to the Analysis Services service. The Analysis Services service loads the Excel workbook into memory and returns the query results.

This same communication process occurs when a user changes a slicer. New to Excel 2010, slicers provide buttons that you can click to filter PivotTable data. Referring back to Figure 3, the Estimate, Century, and Decade_Desc boxes are all slicers. The two PivotCharts and the PivotTable in the worksheet are connected to them. For example, if the end user clears the filter applied to the Estimate slicer, the charts and table will be refreshed to display both actual and estimated values.

I want to reiterate a subtle but important point. Refreshing a data source or changing a slicer causes Excel to re-query the PowerPivot data model, but the PowerPivot data model doesn’t re-query any of its data sources. A PowerPivot data model is a point-in-time copy of data from one or more source systems. A PowerPivot data model is updated one of two ways: manually in the Excel 2010 client or through a separate SharePoint scheduling process. (I’ll discuss these methods later.)

Use Case 4

When you use PowerPivot for SharePoint, you can turn a PowerPivot workbook into a data source. In the top left corner in Figure 2, notice the three orange boxes labeled Analysis Services OLE DB Provider, Analysis Management Objects (AMO), and ADOMD.NET. These three technologies are client libraries used by products such as SQL Server Reporting Services (SSRS), PerformancePoint Services, and Excel PivotTables to communicate with SSAS cubes. When a PowerPivot workbook is published to SharePoint, it becomes a URL-addressable data source that looks just like a standard SSAS cube.

For example, suppose that an end user designs a report in Report Builder 3.0. As Figure 4 shows, the data source is SSAS. However, the data source’s connection string points to the PowerPivot workbook stored in SharePoint rather than pointing to a server. In this scenario, the PowerPivot Web service connects directly to the PowerPivot System service. Excel Services isn’t used.

Figure 4: Report that uses a PowerPivot workbook as a data source

Why would end users want to connect to a PowerPivot workbook as a data source? Even though the Excel 2010 client and Excel Services provide a great interface, there might be situations in which the other technologies are preferable. For example, with SSRS, end users can schedule a report subscription to deliver reports via email on a recurring basis. With PerformancePoint Services, end users can create a dashboard that includes Key Performance Indicators (KPIs) and interactive web-based grids and charts. And with the standard PivotTable interface in Excel 2007 and Excel 2003, end users can connect to a PowerPivot workbook in SharePoint without having to download the entire workbook. For very large workbooks or environments in which not all users have Excel 2010, this option provides a great way of using the PowerPivot data model.

Administration of Data Refreshes

As I mentioned previously, a PowerPivot data model is updated one of two ways: manually in the Excel 2010 client or through a separate SharePoint scheduling process. The data refresh functionality is disabled by default, so an end user or administrator needs to enable it. When a PowerPivot Gallery is displayed in either the Gallery or Carousel view, a small Calendar icon appears to the right of the workbook. Clicking this icon brings up the Manage Data Refresh page shown in Figure 5. To get to this page in the Document Library view, click the down arrow and select the Manage PowerPivot Data Refresh option. In the Manage Data Refresh page, select the Enable check box and create a schedule to refresh one or more of the data sources used by the PowerPivot data model You can also manually refresh the data model by selecting the Also refresh as soon as possible check box.

Figure 5: The Manage Data Refresh page

To clarify, this data refresh capability rebuilds the PowerPivot data model and refreshes the workbook data connection so that the PivotTables and PivotCharts reflect the updated model. From my experience, this is the kind of refresh experience end users expect—not the Excel Services refresh command, which simply re-queries the existing PowerPivot data model.

Administration of the PowerPivot Deployment

Giving end users the ability to easily connect to and consume millions of rows of data is a bit frightening. To manage and monitor the PowerPivot deployment, administrators can use the PowerPivot Management Dashboard, which Figure 6 shows. As I mentioned previously, this dashboard is a built-in component of PowerPivot for SharePoint and is accessible from the SharePoint Central Administration console. To clarify, this is a tool for administrators and not end users.

Figure 6: The PowerPivot Management Dashboard

The PowerPivot Management Dashboard provides performance data for the SSAS instance (e.g., query response time, CPU utilization, memory utilization), general usage statistics for each workbook saved to the SharePoint site, and information about workbook data refreshes. Interestingly, the performance and usage statistics data is collected on a schedule and maintained in a PowerPivot workbook named PowerPivot Management Data.xlsx. In other words, the PowerPivot Management Dashboard uses PowerPivot to monitor PowerPivot.

A New Paradigm

PowerPivot is a new paradigm in BI, which Microsoft calls managed self-service BI. Looking ahead, Microsoft has committed to further PowerPivot investments in the next release of SQL Server, which is code-named Denali. If you’d like to learn about those investments, check out the SQL Server BI Blog "BI for the Masses (including Excel & DBA Pros) Continues…." Even if your organization hasn’t yet deployed PowerPivot, I encourage you to get up to speed on its concepts and architectural components, because they will be the building blocks for a major part of Microsoft’s future BI capabilities.

Backing Up SharePoint 2010 Content, Configurations, and Components

Backing up and restoring SharePoint 2010 databases, content, and configurations can be a complex endeavor. Microsoft has made some significant improvements to the native tools, including the addition of features that let administrators back up an entire farm configuration, an individual web application, an individual site collection, or even specific content. In addition, administrators can now connect to unattached content databases and restore content from them. With that said, it isn’t immediately obvious which backup and restore tools are best for which situations and how to use them. So, I’ll first shed some light on the available backup and restore tools, then concentrate on the tools with which administrators should be familiar.

Related: Delivering BI through SQL Server and SharePoint

Knowing Your Options

There is a wide array of options to back up a SharePoint environment:

• SQL Server tools. SharePoint content that’s stored in SQL Server databases can be backed up using SQL Server’s built-in backup and restore tools. You can initiate the backup as a one-time task or as a scheduled job. SharePoint database backups can be combined with other SharePoint backups, such as those available through SharePoint’s Central Administration site or the SharePoint 2010 Management Shell. Unlike SharePoint restore procedures, SQL Server restore procedures can’t restore item-level objects. Only complete database restores are possible.
• Central Administration tools. The Central Administration site includes several tools that can be used to backup and restore the SharePoint environment. However, not all backup and restore options are available when using the Central Administration tools.
• SharePoint 2010 Management Shell. SharePoint 2010 introduces the SharePoint 2010 Management Shell, which is built on top of Windows PowerShell. By executing SharePoint-specific PowerShell commands in the SharePoint 2010 Management Shell, administrators can gain some functionality that isn’t available through the Central Administration tools.
• Recycle Bin. Originally introduced in the SharePoint 2007 wave of products, this tool is used by users and administrators. Data can be restored by users within 30 days of deletion and thereafter by site collection administrators. SharePoint 2010 SP1 added the Site Recycle Bin, which lets site collection administrators restore entire sites that have been deleted.
• AppCmd. Using the AppCmd command-line tool, administrators can back up the Internet Information Services (IIS) 7.0 configuration file on a Windows Server 2008 machine. The IIS 7.0 configuration file consists of web.config and applicationHost.config files. When a system failure occurs, administrators can restore the IIS 7.0 configuration from the backup file.
• Stsadm. Although available in SharePoint 2010, Stsadm has been deprecated and is provided only to support backward compatibility. PowerShell is the preferred option—and in certain situations, the only option—for managing the SharePoint 2010 environment.
• Microsoft System Center Data Protection Manager (DPM) 2010. DPM 2010 is Microsoft’s enterprise backup tool. It does snapshot-level backup and restore of SharePoint content, providing for full-farm or item-level recovery. DPM is a separate component that isn’t included with SharePoint 2010.
• Third-party backup tools. Multiple vendors have backup tools for SharePoint 2010 that offer advanced functionality, such as item-level restores.

By using the SQL Server backup and restore tools and SharePoint 2010 Management Shell in addition to the backup and restore options in Central Administration, you can be fully prepared in the event of outages and other problems. The SQL Server tools and SharePoint 2010 Management Shell give SharePoint administrators more backup and restore options—particularly those related to restoring SharePoint databases, performing incremental backups, and scheduling—that aren’t available when using the Central Administration tools. So, let’s take a closer look at these three tool sets.

Using the SQL Server Tools

Fully loaded, a SharePoint 2010 environment will have about 25 databases that contain a significant amount of crucial content. (By fully loaded I mean deployed with FAST Search Server 2010 for SharePoint; Microsoft Office Web Apps; all service applications; and content, logging, and configuration databases.) That’s a huge number of databases to maintain and back up. To minimize the potential for lost content, it’s crucial to have a solid database backup plan.

There are many options available to back up and restore SharePoint databases. In addition to the Central Administration tools and SharePoint 2010 Management Shell, other options include using SQL Server Management Studio (SSMS) or T-SQL scripts. You can also use a third-party database backup tool.

No matter which tool you use, it’s important to understand that when you back up the databases, the search index and any web server customizations aren’t backed up. So, database backups generally aren’t the only backup option chosen by most administrators.

SQL Server offers many different types of database backups, including:

• Full backup. The entire database (including the transaction log) is backed up.
• Differential backup. All the data changes since the last full backup are recorded.
• Transaction log backup. All transactions performed against the database since the last full backup or the last transaction log backup are backed up.
• File and file group. A portion of the database is backed up.
• Partial backup. All data in the primary group, every read-write file group, and any specified files are backed up. File groups marked as read-only are skipped.
• Differential partial backup. Although similar to the partial backup, this backup only records changes to the data in the file groups since the previous partial backup.
• Copy-only backup. Unlike the other types of backups, a copy-only backup isn’t recorded in the database and isn’t used for restoration. Any type of backup (e.g., full, differential, partial) can be a copy-only backup.

Here’s an example of how to use SSMS to perform a full backup:

1. Open SSMS.
2. In the Connect to Server dialog box, select the name of the database server you want to connect to and click Connect.
3. In the left pane of Object Explorer, expand the Server folder, then the Database folder.
4. Select the SharePoint database you want to back up.
5. Right-click the database, select Tasks, then click Back Up.
6. In the General page of the Back Up Database window shown in Figure 1, confirm the name of the database to be backed up and confirm that the Backup type option is set to Full.
7. Set the Backup component option to Database.
8. In the Backup set section, enter the name and description for the database backup.
9. In the Destination section, select Disk or Tape. (The Tape option will be grayed out if there are no tape devices attached to the database server.) You can back up information to as many as 64 disk devices or tape devices. If multiple devices are specified, the backup information will be spread across those devices. All the devices must be present to back up the database. If you need to add a device, click Add, enter the requested information, and click OK to return to the Back Up Database window.
10. Click Options in the Select a page pane to configure advanced backup options. (Alternatively, you can click OK to initiate the backup if you don’t want to configure any advanced backup options.)
11. In the Options page’s Overwrite media section, you can choose to add the backup to an existing media set or new media set. If you want to add the backup to the existing media set, you have the option of appending it to the existing backup set or overwriting all existing backup sets. For this example, maintain the default settings of Back up to the existing media set and Append to the existing backup set.
12. In the Options page’s Reliability section, leave the Verify backup when finished option selected. Microsoft recommends that you leave it selected so that your database backup is verified. However, the verification process extends the time it takes to complete the database backup.
13. In the Options page’s Reliability section, select Perform checksum before writing to media. This option ensures that the database backup is completed without any errors. However, the checksum process extends the time it takes to complete the database backup.
14. In the Options page’s Reliability section, select Continue on error. This option ensures that the database backup will continue even if an error is encountered. However, selecting this option will have an impact on the performance of the server because it increases CPU overhead.
15. Click OK to execute the backup.
16. Review the backup operation’s success or failure error messages and click OK to finalize.
17. Repeat steps 2 through 16 for any additional SharePoint databases.

Using the Central Administration Tools

The most obvious choice to back up SharePoint configurations and components is to use the built-in tools in the Central Administration site. The site’s Backup and Restore page comes with two options: Farm Backup and Restore and Granular Backup. Using the Farm Backup and Restore option, administrators can back up and restore a complete farm, a farm’s configuration, or individual components in a farm. New to SharePoint 2010, you can use the Granular Backup option to back up a site collection, export a document library or list, and recover data from an unattached content database.

As with previous versions, scheduling backups via the Central Administration site isn’t an option. However, you can perform a one-time backup. For example, to back up a farm configuration, you need to perform the following steps:

1. Open the Central Administration site on a SharePoint server. Select All Programs on the Start menu, click Microsoft SharePoint 2010 Products, and choose SharePoint 2010 Central Administration.
2. Select Backup and Restore in the Quick Launch toolbar.
3. Select the Perform a backup option in the Farm Backup and Restore section.
4. In the Select Component to Back Up page, select the Farm component and click Next.
5. In the Select Backup Options page shown in Figure 2, select Full in the Backup Type section.
6. In the Data to back up section on the same page, select Back up only configuration settings.
7. In the Backup location text box on the same page, enter a backup location.
8. Click Start Backup.

After starting the backup, SharePoint 2010 displays the Backup and Restore Job Status page. It might take several minutes for the backup process to appear on the page, depending on the backup type and the data being backed up. It might be necessary to refresh the screen occasionally to see the updated status. You can also monitor the backup progress by clicking the View History link, which shows the historical backup progress rather than the current status.

You can view the backup files in the location you selected in step 7. You’ll find an XML manifest and files with .bak extensions. The XML manifest is required for a restore, so don’t delete it.

Restoring content is more difficult than backing up content. Fortunately, restoring content is fairly straightforward with the Central Administration tools. For example, here are the steps to take to restore a farm:

1. Create a new farm to which to apply the restore if it doesn’t already exist.
2. Open the Central Administration site on a SharePoint server.
3. Select Backup and Restore in the Quick Launch toolbar.
4. Select the Restore from a backup option in the Farm Backup and Restore section.
5. In the Backup and Restore History page, enter where your backup files are located in the Backup Directory Location text box.
6. In the Top Component column, select the farm that you want to restore and click Next.
7. It will take a few minutes before the Select Component to Restore page appears. On this page, select the farm components you want to restore and click Next.
8. In the Select Restore Options page, select either the New configuration option or theSame configuration option. Choose the New configuration option if you want to restore the data to a farm with a different configuration, in which case it’ll have different computer names, web application names, and database server names. Choose the Same configuration option if you want to restore the data to the same farm, in which it’ll have the same computer names, web application names, and database server names.
9. Click Start Restore to commence the restore process.

Using the SharePoint 2010 Management Shell

The SharePoint 2010 Management Shell makes it easy to automate backup and restore processes. All the backup and restore options in the Central Administration site can be initiated in the SharePoint 2010 Management Shell. For example, to back up a farm configuration, you’d execute the following PowerShell command from the SharePoint 2010 Management Shell:

                              backup-spfarm -BackupMethod Full                                -Directory C:\Backup\                                -ConfigurationOnly                              

(Although this command wraps here, you'd enter it all on one line in the SharePoint 2010 Management Shell. The same holds true for the following command that wraps.) To back up a single web application, you’d execute this command:

                              backup-spfarm -BackupMethod Full                                -Directory C:\Backup\                                -Item http://webapplicationname                              

Besides automating backup and restore processes, administrators can schedule their backups—something not possible with the Central Administration tools. For more information about using PowerShell for SharePoint backup and recovery, see the “Backup and recovery cmdlets (SharePoint Foundation 2010)” web page.

Developing a Backup Strategy

The wide array of tools to back up SharePoint content, configurations, and components can be quite daunting. However, you can develop a backup strategy that has the correct mix of tools based on your business requirements. A good place to start is to use the SQL Server tools and SharePoint 2010 Management Shell along with the backup and restore options in Central Administration. With these tools, you can effectively back up your SharePoint environment and restore it if needed, without any loss to your business.

Configure SQL Server 2012 for SharePoint 2013

If you're using SQL Server 2012 as the database server for SharePoint 2013, you must perform a particular set of steps to install SQL Server 2012 and then configure it specifically to host your SharePoint 2013 farm.

Set MAXDOP to 1

In "Set Up SQL Server 2012 as a SharePoint 2013 Database Server," we installed a SQL Server instance, but there are a few tweaks we need to make before that instance can work with your SharePoint 2013 setup. To perform these tweaks, your first step is to log into SQL Server as an account that has elevated permissions to the SQL Server instance. In the accompanying video, I logged in and made the tweaks via the sqlinstall account that I used to install SQL Server.

After SQL Server is installed, open SQL Server Management Studio (SSMS). With the exception of the last tweak I suggest, all the changes are made to the SQL Server instance. To access the instance's settings, right-click the instance name and click Properties from the flyout menu.
The first change I make after SQL Server 2012 is installed is to set the max degree of parallelism option (MAXDOP) to 1—a setting that is a requirement for creating the SharePoint 2013 farm. This required setting is new in SharePoint 2013; in SharePoint 2010 setting MAXDOP to 1 was only recommended. By default MAXDOP is set to 0, which tells SQL Server, "Parallelize as much as your bad self wants to." To change the setting to 1, in the properties for the SQL Server instance, go to the Advanced tab. You'll find the MAXDOP option under the Parallelism group, as shown in Figure 1. Set it to 1 and click OK.

Figure 1: Setting Max Degree of Parallelism to 1 in SQL Server 2012

What exactly is MAXDOP, anyway, and why is it so important to SharePoint? MAXDOP is a setting that defines how many CPUs SQL Server uses when it's executing a query. That's great, if the queries were written to take advantage of that setting. If they weren't, splitting the execution among multiple processors can have unpredictable results. Unpredictable is acceptable for birthday presents, or the ending to O. Henry stories, but it isn't so good for SharePoint. SharePoint is unpredictable enough on its own without SQL Server pitching in. Because SharePoint's stored procedures were not all written to handle parallelism, MAXDOP has to be 1.

Set Permissions for SharePoint

The next change I will make in my SQL Server configuration is to give my SharePoint installation account the permissions it needs to create a SharePoint farm and to give permissions to the other SharePoint service accounts. As described in my blog post "Service Account Suggestions for SharePoint 2013," this account is sp_install. SharePoint's demands are pretty reasonable—it doesn't need to be sysadmin or anything crazy like that. Sp_install just needs the dbcreator and securityadmin server roles. This makes good sense as sp_install needs to create the farm databases and give the other service accounts permissions.
To give the sp_install account the permissions it needs, in SSMS navigate to Security, Logins in Object Explorer. Right-click and select New Login. Under General, type the username and make sure you include the domain. Then on the Server Roles page, shown in Figure 2, select the dbcreator and securityadmin check boxes and verify that the public check box is still selected. Then click OK.

Figure 2: Assigning Permissions to the sp_install Account

Let me offer a few words of advice about setting the sp_install permissions. SharePoint assumes that those three roles, dbcreator, public, and securityadmin, have the default set of permissions in SQL Server. Don't alter those permissions. I've seen DBAs in very secure environments try to lock down these three roles. Doing so will most certainly break SharePoint in crazy and unusual ways. That might not happen right away, and it might not happen to you when you're using the interface. It could be a monthly timer job that fails, for instance. Also, don't change any SQL Server permissions that SharePoint sets. SharePoint is very fussy, and if it sets permissions, it really needs them. Because of SharePoint's rigidity on its SQL Server permissions, I recommend that you put SharePoint in its own SQL Server instance. SharePoint will thank you, and so will your DBAs.

Script Your SQL Server Config Settings

In the spirit of making SQL Server configurations more efficient, I script all the settings I've described in this article and "Fine-Tune Your SQL Server 2012 Configuration for SharePoint 2013." Scripting these changes makes them easier to document and tougher to screw up. It also appeals to my lazy tendencies. Because I make these same changes every time I configure a SQL Server instance, I have a file that contains them, which looks like what you see in Figure 3.

Figure 3: Script to Configure a SQL Server Instance

If you click Execute, that script will make all the changes to configuration settings that I covered in this article and "Fine-Tune Your SQL Server 2012 Configuration for SharePoint 2013," except the backup compression setting. (I forgot to add it.) Don't worry, I won't make you type all that yourself. You can download that script, SQLTweaks.sql. If you do decide to script your SQL Server configuration changes, make sure you test them thoroughly. Fancy scripts are a double-edged sword. Although they are a great way to quickly and easily make a lot of good changes, they're also just as apt to quickly and easily destroy stuff.
You might be thinking that I must have some mad T-SQL scripting skills to figure out how to script each of those settings. If only that were the case! In reality, SSMS makes it very easy to turn any series of clumsy mouse clicks into a beautiful T-SQL script. In the top left corner of all the dialog boxes is a Script button. That magical button takes all the changes you've made to the dialog box and turns them into a T-SQL script.
As you can see in Figure 4, after I added the correct server roles, but before I clicked OK, I clicked the Script button and scripted my changes to a new query window. I did that three or four times, then copied and pasted each script into one window. I saved those scripts out as SQLtweaks.sql, and voilà: instant Internet hero!

Figure 4: Scripting Configuration Changes to a New Query Window

SharePoint farms support their databases being in multiple SQL Server instances. If you decide to spread your databases across multiple instances, remember to make these same changes to your subsequent instances. If you have your own SQLtweaks.sql handy, making the configuration changes to multiple instances should be pretty easy.

Poking a Hole in the Firewall

Before your SharePoint server will be able to actually contact your SQL Server instance and take advantage of it, we'll need to make sure that the firewall that's running on your SQL Server instance will allow SharePoint. These instructions will assume that the firewall running on the server is the Windows Firewall, and we'll also assume the firewall is running in its default configuration. If you're running a different firewall, the changes will be similar.
We have to tell the firewall to allow incoming traffic to SQL Server. This can be done in a couple of different ways: through the UI or from a command prompt (my preference). The command-prompt method is easier to reproduce, and it's more impressive to your friends and coworkers.
Because we're running SQL Server as the default instance on our server, the following line will make the appropriate holes in the firewall while preventing our servers from being hacked by bored 12-year-olds:

netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT

If you use a SQL Server instance that is not the default instance, things get a little trickier. To get the story on that, see "Configure the Windows Firewall to Allow SQL Server Access."

Set Up SQL Server 2012 as a SharePoint 2013 Database Server

Installation Preparations

The first step is to obtain your SQL Server installation media. According to the officialMicrosoft SharePoint 2013 requirements document, SharePoint 2013 supports SQL Server 2008 R2 SP1 (and later) and SQL Server 2012 RTM (and later). For this article, I used SQL Server 2012 SP1 Standard Edition. I chose SP1 for a couple of reasons. First, SP1 is newer and more advanced than SQL Server 2012 RTM. It also won't require patching as soon and is likely more secure and stable than the RTM version. Second, a few of SharePoint's fancy business intelligence (BI) pieces require SP1, so having SP1 installed will make it easier for you if you want to use those BI pieces later. I used SQL Server 2012 Standard Edition, but SharePoint works well with the other supported editions. You can use the free Express edition or the super-cool and expensive Enterprise edition, depending on your needs. (See the SQL Server 2012 Editions information for a list of features provided by each SQL Server edition, including Express.)

If your SQL Server installation is an ISO image file and you're running Windows Server 2012 R2, Server 2012 R2 has some new handy functionality that will help you. You can right-click an ISO file and simply mount it as a drive letter. You no longer need to crack open the ISO file and copy the files out. When you're finished installing SQL Server 2012, you just right-click your phony optical drive and click Eject. Hooray for technology!

Accounts

Before installing SQL Server 2012 SP1, I created two Active Directory (AD) accounts. I used one account to install SQL Server; the other account is used for running the SQL Server instance. These accounts are somewhat analogous to the sp_install and sp_farm accounts that I recommend for SharePoint. In this case, I created the accounts sqlinstall and sqluser. Like sp_install, sqlinstall is a domain account and a local administrator on the box on which I'm installing the software.

When I installed SQL Server, I configured it to run its services as sqluser. There are three obvious questions that you might have about the install account. First, why didn't I just install SQL Server as sqluser? Second, why didn't I install it as a domain administrator? Finally, why didn't I install it as sp_install?

My answers to these questions are as follows. First, I didn't install SQL Server as sqluser because I prefer to do least-privilege installations, and sqluser doesn't need to be, and shouldn't be, a local administrator on the SQL Server instance. If someone interjects some malicious or just plain shoddy code into a stored procedure, I want to limit the damage that the code can do.

The install account, however, does need to be a local administrator. So I use a separate local admin account to install the software. I don't use the domain administrator or an account that's a member of the domain administrators group because once again, I like a least-privilege model, and the installer doesn't need to be a domain admin—therefore it doesn't get to be a domain admin. Also, if the account that installs SQL Server is a domain admin, it will, with good intentions, make some changes to AD, including creating a service principal name (SPN) for SQL Server. This won't immediately cause you any issues, but if you ever switch SharePoint to use Kerberos, the SPN might be problematic. SQL Server could possibly create an SPN that could break Kerberos authentication for SharePoint if you ever change the service account that SQL Server uses. Thus you're better off manually creating the SPN for SQL Server when you need it.

Having SQL Server's SPN (MSSQL/sq01.contoso.com in this case) assigned to two accounts will absolutely break Kerberos. When it comes to troubleshooting, Kerberos is a worthy adversary. And because you didn't create the original SPN, you'll never think to look there. Do yourself a favor and don't let that happen. (Yes, you can use the command setspn -X to find duplicate SPNs, but you need to know you're looking for duplicate SPNs to use that. Kerberos is tricky that way.)

Finally, even though the requirements seem the same, I don't install SQL Server as sp_install. When we configure our SharePoint farm, we will do so as sp_install. If the account that creates the SharePoint farm has elevated permissions in SQL Server, the account will try to make some changes to the farm. Namely the account will change the max degree of parallelism option (MAXDOP) to 1. Although that particular change isn't a bad thing, I don't like the idea of SharePoint making changes to SQL Server that I don't know about. Plus, that least-privilege principle comes up again. The sp_install account doesn't need to be a local admin on the SQL Server box, so it doesn't get to be.

If you don't like having a lingering account that's a local admin on your SQL Server box (and who does?), you can disable the account after SQL Server is installed and configured. Don't delete the account, though, because you should use it later when you patch your SQL Server system. If you have multiple SQL Server instances in your organization, I recommend using a different pair of sqlinstall and sqluser accounts for each instance. You don't want to accidentally break someone else's SQL Server instance. Even worse, you don't want another SQL Server DBA accidentally breaking yours. It happens all too often.

The Setup

After you've downloaded and mounted the media and created your accounts, log in as sqlinstall and run setup.exe at the root of the drive. Doing so will launch an installer. Much like SharePoint, SQL Server has a splash screen and installer, but unlike SharePoint, SQL Server's installer resembles a labyrinthine maze. When the installer comes up, click Installation at the left to display the installation options. For our sample installation, I clicked New SQL Server stand-alone installation or add features to an existing installation, as shown in Figure 1. Setup will then run some checks on the machine, such as making sure that your install account has adequate permissions and that the correct .NET Framework versions are installed. If your machine has any issues here, make sure you fix them. Then click OK to move on.

Figure 1: SQL Server Installation Center

The next screen has you enter your license key or run SQL Server in a trial mode. SharePoint will run just fine if SQL Server is in trial mode, right up to the moment that the trial ends. That's not a fun phone call to take and not a fun problem to troubleshoot or fix. If you have a legitimate SQL Server license, now is the time to enter it. Then click Next.

In the next step, Setup will check to see whether any important SQL Server updates should be installed. Unless there's a compelling reason not to, install any patches that Setup recommends, then click Next. The SQL Server installation process will install a very helpful patch that fixes a nasty MsiInstaller bug that was introduced in SP1. Allowing the installer to install patches will save you a lot of time later on.

Once you're past the update step, Setup will install some installation files and run some more checks. If there are any issues to fix, tend to them and click Next. If your SQL Server system doesn't have Internet access, Setup might stall a bit at this point. Don't worry about that; after the requests time out, Setup will continue.

The next screen (Figure 2) will ask you whether you want to install individual features or all features with the defaults. We will choose the SQL Server Feature Installation option, so that we can customize our installation with the SQL Server features specifically needed for SharePoint.

Figure 2: SQL Server 2012 Setup Screen

The Features

After selecting SQL Server Feature Installation and clicking Next, a list of SQL Server features is displayed, as shown in Figure 3. We really need only one SQL Server feature for SharePoint: Database Engine Services. However, I will also install the Management Tools (Complete) feature, which gives you handy tools such as SQL Server Management Studio. As you browse through the list of features, you might be tempted to check more features than you really need. But unless you're going to use a particular feature immediately, I don't recommend installing it. If you want to add a feature later, such as SQL Server Reporting Services, you can just run Setup again and add the feature to your existing instance. After selecting the features, leave the feature directories as they appear in the dialog and click Next.

Figure 3: Selecting SQL Server Features to Install

Click Next again until you reach the Instance Configuration screen. If you can't explain the difference between a SQL Server instance and SQL Server to your mother, take a quick look at my "SQL Terminology for SharePoint Admins" blog post. In this example, we'll install SQL Server to the default instance. When we reference the default instance, it's just the server name, so that's easy to keep track of. Internally SQL Server calls the default instance MSSQLSERVER. In this installation, we'll go ahead and install the binaries on the C drive, as shown in Figure 4.

Figure 4: Configuring a SQL Server 2012 Instance

Click Next. The next screen will display the drive space requirements for each drive. After you review that information, click Next again. You'll see the Server Configuration screen, shown in Figure 5, where you will set the SQL Server service account names and passwords.

Figure 5: Setting the SQL Server Service Account Names and Passwords

Accounts Again

We'll use the sqluser account. This account doesn't need any elevated privileges in either AD or on the SQL Server instance. The account we're using for installing SQL Server will take care of all that. Use the sqluser account for both the SQL Server Agent and the SQL Server Database Engine. If you want to get fancy, you could run the Agent as a different account, but that isn't necessary. You don't need to make any changes to the Collation tab. In previous versions of SharePoint and SQL Server, the collation had to be set very specifically. That's no longer the case. Technological advances save the day again.

Click Next to advance to the Database Engine Configuration screen, shown in Figure 6, where you'll set up authentication. The default authentication mode is Windows authentication, and we're fine with that. However, we do want to add an account underSpecify SQL Server administrators at the bottom of the screen. This account will be our back-door account in case we need to get into SQL Server to do some management. The account will have unrestricted access to SQL Server, so it should be a highly guarded account. In the past, I've used something like the Domain Admin or sqlinstall account. This account should be necessary only for emergencies.

Figure 6: Setting Up SQL Server Authentication

Location, Location, Location

Now click the Data Directories tab to set the location where SQL Server will store various files. This gets into one of the trickier bits of a SQL Server installation. There's a lot of guidance available on how you should lay out the files for a SQL Server instance. However, following that guidance can be expensive. Ideally the operating system, TempDB, your data files, your log files, and your backups would all be on separate spindles. That's five different drive locations. You'll also want those drives to be fault tolerant, so that's a minimum of 10 disks. In some situations, that layout is feasible, but in general having all the recommended disks would be too costly to be practical.

Keeping those SQL Server file types separated will improve both performance and reliability. In many cases, the database engine is writing from one of those file types to another. If the different types of files are on separate spindles, the read and write operations aren't fighting against each other for the drive's attention. Separating them will increase reliability by keeping one file type from filling up the drive that the other file types are on. You can't kill SQL Server because your backups filled up your data drive, for instance. Or, one of SQL Server's favorite tricks is to let one of the log (.ldf) files fill up a drive, usually the C drive, thus collapsing the entire SQL Server instance. Putting the .ldf files on their own drive keeps that from happening.

The layout in Figure 7 shows a best-case scenario, with the exception of TempDB, which I forgot to move. All of those locations can be changed after installation, but it's easiest to lay out the files correctly at installation time.

Figure 7: Best-Case-Scenario Layout for Database Engine in SQL Server 2012

The Grand Finale

I know there have been a lot of screens so far, and a dizzying amount of clicking Next. Don't fret, we're almost at the end of the installation process. After we click Next a couple more times, we'll see the screen in Figure 8, which provides a summary of the install decisions we've made so far.

Figure 8: Verifying the SQL Server 2012 Features to Be Installed

This screen has a hidden gem at the bottom: a configuration file path. Without us even asking, SQL Server generated a ConfigurationFile.ini that saved all our configuration options. This file can be used as immediate documentation of your SQL Server instance, and it can be used to install another instance with the same settings. Because this is just a text file, you can open it and tweak it however you'd like for your next install. To install SQL Server using a ConfigurationFile.ini, you have to run setup.exe from a command prompt (alternatively, you could use Windows PowerShell) and pass the file location as a parameter.

At this point you have two things left to do. The first is to click Install and watch the magic happen. The second is to lean back and admire your handiwork. Although SQL Server won't give you a gold star for a job well done, it will give you a happy screen like the one in Figure 9 verifying that your installation was successful.

Figure 9: SQL Server 2012 Installation Success Screen

If you've followed the process described in this article, you'll soon be rewarded with a solid SQL Server installation that you can show your friends, and one that should work well for any SharePoint farm. Installing is only half (though arguably the largest half) of what you need to do, though. After the SQL Server instance is installed, you still need to do some configuration before it's ready for SharePoint. I cover this part of the process in "Configure SQL Server 2012 for SharePoint 2013" and "Fine-Tune Your SQL Server 2012 Configuration for SharePoint 2013."

How to design your disk system for maximum performance at the database level

Disk input/output (I/O) performance is the single biggest constraint on database performance in the SharePoint world. Running SharePoint on an underpowered or poorly designed SQL Server environment will slow performance throughout the farm.

Related: Monitoring SQL Server and SharePoint BI Components

Because it's equally important to make sure SQL Server databases are kept highly available, some level of disk redundancy is usually factored into physical designs for database servers. Disk redundancy is often classified by RAID levels. After I explain how the various RAID levels can help performance and availability, I'll share some tips on how to design your disk system for maximum performance at the database level.

RAID Fundamentals

RAID technologies predate PCs and modern x86 server designs. Three of the most common levels are RAID 1, RAID 5, and RAID 10, so let's take a closer look at them.

RAID 1. In a RAID 1 design, you have mirrored pairs of disks. If one disk fails, the other has a full copy of all the data and goes on operating. RAID 1 is a high-performance technology but requires purchasing 2GB of raw disk space for every 1GB of data because you need two sets of identical data. So, although RAID 1 might initially seem like an inexpensive solution because there are only two disks involved, it can be an expensive one if you have a lot of data to store.

RAID 5. In this RAID level, you use groups of disks to create a large synthetic unit. For example, five 150GB disks could be combined to create one hybrid 600GB storage pool.

At this point, you might thinking, "Wait a minute, that's not correct!" Although I'm not great at math, I know that 150GB ´ 5 should equal 750GB, not 600GB. However, in RAID 5, one disk is dedicated to calculating a checksum, or parity bit, every time data is written to the group. That way, if any of the single disks fail, the data can be redistributed to other disks by reversing the parity calculations. As a result, RAID 5 is highly efficient at optimizing the volume of data stored. It's also great at handling high-volume read operations -- more disks working together means more systems to read bits from a disk platter and fewer chances for bottlenecks.

Write operations are a different story. Because every write operation requires a calculation, high-volume write operations are slower than they would be in a traditional disk system. However, for SharePoint, that's not always the worst thing, because SharePoint is a read-heavy database consumer.

RAID 10. Some people make a distinction between RAID 1+0 (stripes across mirrors) and RAID 0+1 (mirrors of stripes). This debate doesn't really matter in SharePoint designs. What does matter is that out of all the RAID levels, RAID 10 offers the highest availability and highest performance possible. However, RAID 10 requires purchasing extra disks for parity and mirroring -- more than 2GB of raw disk space for every 1GB of data, effectively more than doubling storage costs.

Design Tips

SQL Server databases are the largest consumers of disk space in SharePoint. Thus, designing your disk system for maximum performance at the database level is more crucial than for any other level. Here are some general design tips.

Don't virtualize SQL Server. If possible, don't virtualize SQL Server because it's already an integration platform. If you have to virtualize SQL Server, try to limit the virtualization to test or development systems. Avoid using it for production systems because it raises the bar in disk engineering to get good performance. SQL Server databases stored in virtualized disk files are inherently slow compared with dedicated physical disks.

Use multiple logical drive letters. It's usually a good idea to break up SQL Server databases into multiple logical drive letters because database files, transaction logs, backup files, and temporary databases (tempdb) benefit from having multiple independent sets of disk spindles. Adding more spindles spreads the load across multiple parallel operations when data is being written to the database.

If you're going to use a SAN or virtualized environment, make sure you understand where those logical drive letters will be mapped. For example, if the D, E, and F drives will all point back to separate LUNs on your SAN, but those LUNs will be part of the same storage group and same set of physical disks, splitting those files into multiple drive letters will add complexity without significant performance gains.

Use RAID 10 judiciously. RAID 10 is great, but you might not be able to justify it for all applications. For example, it might be overkill for backup files. Balancing disk performance and cost is a reasonable trade-off. One possible design is to use:

• RAID 1 on boot disks
• RAID 5 on data disks
• RAID 10 on log disks
• No RAID or RAID 5 on backup disks

Break large content databases into multiple database files. If you have large content databases, you can engineer better performance by breaking each large database into multiple database files. Each database file should be on a separate disk.

Presize SQL Server databases. SQL Server databases can be set to automatically grow as needed, but this can lead to massive file fragmentation. Presizing the databases to a sufficient size at the outset helps ensure contiguous file allocations. Note that SQL Server's tempdb database is heavily used by SharePoint, so you should presize it to about 20 percent of the size of the single largest content database.

If you want to use automatic database growth settings instead of presizing your databases, you should set the databases to grow in 50MB to 100MB clumps and not by percentage. Setting a 100GB database to grow in 10 percent increments means the database essentially stops to add 10GB or more on each increment. Using a small clump size will lead to more frequent, but smoother, steady state (i.e., continuous) growth.

Design a High-Performance Database Environment

If you use RAID and follow the general design tips, you can design a high-performance database environment that's also highly available. This is essential to a smoothly running SharePoint system.