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.

If you followed the instructions I provide in "Set Up SQL Server 2012 as a SharePoint 2013 Database Server," you're just a few steps away from having a SQL Server instance that will serve admirably as the database back end to your SharePoint 2013 farm.

Related: Set Up SQL Server 2012 as a SharePoint 2013 Database Server (Part 1)
Related: Fine-Tune Your SQL Server 2012 Configuration for SharePoint 2013 (Part 3)

In this article, I discuss the two configuration steps that you must perform to enable SQL Server as a SharePoint database server. In a companion article, "Fine-Tune Your SQL Server 2012 Configuration for SharePoint 2013," I explain some optional configuration tweaks that I like to make to my SQL Server instances for optimal performance. Now let's dig into the details.

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."

Time for SharePoint

Once you've made all the necessary and optional configuration changes to your SQL Server instance, it's time to dust off that SharePoint setup download and introduce your SQL Server instance to the SharePoint farm. If you need further assistance, here is some additional reading to help you along your way.