Edit: 9/2/2016 Check out my latest post that puts most of this work in video format. 🙂
One of the things that comes up in my SharePoint Administrator classes a lot is people who are looking for an easy guide to just get SQL Server up and running so they can deploy SharePoint. They aren’t looking for best practices or optimal configurations just cut-to-the-chase, what they should do, instructions. So in this blog post I have attempted to provide that. If you are an old pro at SQL installs (meaning you have done it more than twice) this post isn’t for you. If you are new to it all then read on.
These instructions were written using Windows 2008 R2 but I did use them on a Windows 2012 VM and the steps were the same except I did not have to install the .NET piece.
There are three things you need to get a SQL Server ready. Install, configure max degrees of parallelism, and setup your SQL permissions.
Installing SQL Server 2012 for SharePoint
- Run setup.exe
- From the SQL Server Installation Center click on Installation on the left hand side of the page.
On the right hand side of the page click on New SQL Server stand-alone or add features to an existing installation.
- After the Setup Support Rules run, click OK.
- Enter your appropriate Product Key and then click Next.
- Select I accept the license terms.
- Select Send feature usage data to Microsoft….
- Click Next.
- When the Setup Support Rules screen pops up review any errors or warnings you get. If nothing bad has happened the Next button will be available to click. Click Next.
- For Setup Role select SQL Server Feature Installation and click Next.
On the Feature Selection screen this is where you need to be smart. In order to make SharePoint run you only need to select one check box, Database Engine Services. I would highly recommend you also check Management Tools – Basic and Management Tools – Complete.
Now when you read all of these awesome features you might be thinking “I want to kick the tires of Reporting Services – SharePoint” or some other random feature. That is great, tire kicking is fun and important but if you are reading the blog post to get SQL Server installed correctly for SharePoint then you probably aren’t ready to start randomly installing features. Even if you were ready to install them you would still most likely come back and do them after SharePoint was up and running, not before. So let’s ignore them for now and click Next.
On the Installation Rules screen SQL will make some checks. In this case my Server did not have the Microsoft .NET Features installed. You will need to manually add the feature now. While you do go ahead and leave this SQL window open.
- To add the .NET Windows Server feature click on Start > All Programs > Administrative Tools > Server Manager.
- From the right side of the screen click on Features.
- Over on the left side of the screen click on Add Features.
- Check the box for .NET Framework 3.5.1 Features.
- When you check the box a window for Add Features Wizard will appear telling you the additional required roles. Click Add Required Role Services.
- Click Next.
- At the Web Server (IIS) screen click Next.
- Accept all of the defaults and click Next.
- At the Confirm Installation Selections click Install.
- At the Installation Results screen make sure everything was successful and then click Close.
- Jump back over to your SQL Installation Rules screen and click the Re-run button.
- If the tests are Passed click Next to continue.
- Assuming this is the only install of SQL Server on this server then you are going to want to take all of the default settings for the Instance Configuration screen. If you have other SQL Instances installed on this server you are more advanced than reading this blog post. J Click Next.
- At the Disk Space Requirements screen click Next.
For the Server Configuration screen it is asking you what accounts you want to run SQL Server as. The only service you are worried about right now is the SQL Server Database Engine. This service should always be run as a domain account not a local account. Next to SQL Server Database engine click on NT Service\MSSQLSERVER and a drop down arrow will appear.
- Click <<Browse…>>.
- Select your SQL Service account and click OK.
Enter the account password and click Next.
On the Database Engine Configuration screen there are lots of changes you could make and over time you will learn about these options but for the purpose this guide you will except all of the defaults. You only need to click Add Current User before you continue on.
If you were building a production capable SQL Server best practice 101 would be to store your data and log files on different volumes. By default SQL Server will store everything on the C: drive. If you want change that behavior take a gander at the Data Directories tab.
- Once you are ready click Next.
- For the Error Reporting screen select Send Windows and SQL Server Error Reports…
- Click Next.
- At the Installation Configuration Rules screen click Next.
- You are Ready To Install, so click Install.
- When the installation finishes you may be prompted to restart. This is not directly because of SQL Server but instead because of other recent installs you have done that you haven’t rebooted since. In my case because I added the .NET feature. When SQL is all done click Close. If you have any other open windows at this point you can close them as well. You are all done.
- If you got the pop up screen to do a reboot it will not automatically happen, you will need to Reboot on your own.
Max Degree of Parallelism
Now that you have SQL Server all installed there is one more configuration change you need to do in order to make SharePoint happy. You need to change the max degree of parallelism. Don’t ask me what that is. Something about number of processors and how SQL uses them. Unfortunately SQL Server defaults to 0 and SharePoint 2013 necessitates, demands, forces, requires, and otherwise really wants you to set it to 1. So make the change. If you are running SharePoint 2010 this change is not required but is recommended.
Now if you look at the link I gave you saw a bunch of fancy SQL to change it. Barf! Let’s change it the easy way, with a mouse.
- From your newly installed SQL Server click Start > All Programs > Microsoft SQL Server 2012 > SQL Server Management 2012.
- On the Connect to Server screen click Connect. If for some reason the Server name: field is blank you would just type in the name of the server.
- At the top of the Object Explorer window you see your server. Right click on it.
- From the menu that appears click Properties.
- In the Select a page section click Advanced.
- Scroll to the bottom and change Max Degree of Parallelism from 0 to 1.
- Click OK.
That does it. No need to reboot or anything else to make the change take effect.
One more quick change since we already have SQL Server Management Studio running. You need to give the Windows Account that you plan to install and configure SharePoint with some elevated rights in SQL. Typically we recommend you use a dedicated account (I like the name sp_install) and this account will need the following roles on the SQL Server.
- Make sure you still have SQL Server 2012 Management Studio open on your SQL Server.
- From Object Explorer expand out Security.
- Under Security expand out Logins.
- Right click on Logins.
From the menu choose New Login….
- For Login name: enter domain\sp_install or whatever account you will be logged into SharePoint as when you do the install.
- On the left, under select a page click Server Roles.
Check the box for dbcreator and securityadmin. Also, leave public selected.
- Click OK.
Hooray! Now you have your whole SQL Server ready to rock and roll so you can install SharePoint.