Set Up SQL Server 2012 as a SharePoint 2013 Database Server

When I did a SharePoint 2013 installation at the SharePoint & Exchange Forum 2013, one of the attendees asked me a few questions about how I had installed and configured SQL Server 2012 in preparation for SharePoint 2013. I thought to myself, "This topic would make a great article!" That is indeed the topic of this article, and its upcoming companion pieces.

Related: Configure SQL Server 2012 for SharePoint 2013 (Part 2)
Related: Fine-Tune Your SQL Server 2012 Configuration for SharePoint 2013 (Part 3)

In this article, I'll walk through the installation process for SQL Server 2012 SP1, and in the companion articles, I'll explain the post-installation configuration changes I made to SharePoint 2013. As an added bonus, I've provided a 27-minute video walkthrough of the same SQL Server installation and configuration.

Are you excited? I know I am! Let's get started.

Installation Preparations

The first step is to obtain your SQL Server installation media. According to the official Microsoft 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 under Specify 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."