Wednesday, February 27, 2013

Instruction Guide for Installing SQL Server 2012 SP1 for SharePoint 2013

This instruction guide will walk you through how to install SQL Server 2012 SP1 for SharePoint 2013 on Windows Server 2012.

Assumptions
  • You have a clean install of Windows Server 2012 x64
  • You are installing SQL Server 2012 in a disconnected environment. That is, the SQL Server has no internet access. Note that the instructions don't vary much if you are installing in a connected environment.
  • You are doing a basic installation with minimal components...no Reporting Services, Analysis Services, or BI tools, for example. You can always add those later.

Step 1

Install .NET 3.5 on Windows Server 2012. This is simply a Windows Server 2012 Feature. If you don't do it now, SQL Server 2012 will try to install it for you. It may fail, however, because SQL Server 2012 installation media doesn't include .NET 3.5 and it may not be able to access the appropriate installation source.

Step 2

Once you have installed .NET 3.5, start the installation of SQL Server 2012 SP1. Select the "Installation" link on the left hand side of the installer and then select "New SQL Server stand alone installation or add features to an existing installation"

 
Step 3

Next the setup support rule checker will run. If everything passes, it will look like this (note that I clicked "View detailed report" to look at the pretty green check boxes).

 
Step 4
 
Enter the appropriate license key if necessary

 
Step 5

Check the box to accept the license terms. In a disconnected environment, I do not send feature usage data to Microsoft.

Step 6

In a disconnected environment, you will not be able to download product updates from the installer and you will get this snazzy error message. Click Next

 
Step 7

Select the basic components to install. The Database Engine Services is obviously the reason we are installing this in the first place. So select "Database Engine Service". I usually install the complete management tools on the SQL Server (although this is not necessary) so that I can easily manage things from the server if I need to.

Note that in the image below, it says "Windows feature(s) to be turned on: Microsoft .NET Framework 3.5" You will see this if you did NOT install .NET 3.5 as I recommended in step 1.
 
If you did NOT install .NET 3.5 as recommended in step 1, you will also get this warning on the next screen. It clearly states that it may not be able to access Microsoft .NET Framework 3.5 (although it will try), which is why I recommend installing it in the beginning yourself.



If you did install .NET 3.5 as recommended in step 1, you should see a screen like this at this point:



Step 8

At this point, you will pick the named instance and the instance root directory. I always setup my SharePoint SQL Servers with a Named Instance. Note that you do not normally need to change the Instance ID. That will populate for you based on the Named instance. This is also where you specify the instance root directory. Normally you want this to be your data drive.


Step 9

In this step, you specify the service accounts that both the database engine and the SQL Server Agent will use. In the screen shot below, I am using the default settings. But you actually want to specify a domain account. For details, please see Todd Klindt's SharePoint 2013 service account suggestions here

If you were following Todd's suggestions, you would use YOURDOMAIN\sql_server for both the Agent and the database engine. Note also that the installer defaults the SQL Server Agent Startup Type to Manual. I usually set that to Automatic, however, because I am usually scheduling some kind of job in the environment I am working in. If you plan to schedule SQL jobs in SQL Server, set the Startup Type for the SQL Server Agent to Automatic


Step 10

The default SQL Server 2012 Collation is shown in the screen shot below. Typically, you do not want to use that collation, especially if you are upgrading a SharePoint 2010 environment.

The safest bet is to set the collation Latin1_General_CI_AS_KS_WS.

You can find more information about it here: http://support.microsoft.com/kb/2008668

In order to set the correct collation, select the customize button and check the appropriate boxes shown in the second screenshot below.

 
 
Step 11
 
On the next screen, you will select either Windows Authentication Mode or Mixed Mode for SQL Server. Generally, unless you have a reason to do otherwise, select Windows Authentication Mode. At this point you can also add SQL Server Administrators.
 
 
Step 12
 
Assuming you are following all of the appropriate recommendations and requirements for your environment, you will want to specify the appropriate default locations on the Data Directories tab. In the screen shot below, I have everything point to C:, but typically you would have it broken down as follows:
 
DATA Drive: User database directory (this is where SharePoint databases will go. In this case, SharePoint is the user)
 
TEMPDB Drive: Temp DB Directory and Temp DB log directory.
 
LOG Drive: User database log directory.
 
 
Step 13
I typically do not enable FILESTREAM unless I am using Remote BLOB Storage.
 
 
Step 14
 
In a disconnected environment, I do not send Windows and SQL Error Reports to Microsoft
 
 
Step 15
 
One more rule check:
 
 
Step 16
 
Click Install:
 
 
If you have any additional notes or suggestions, please let me know!
 

22 comments:

  1. Instruction guide for installing SharePoint 2013 next ?

    ReplyDelete
  2. Great guide.. You have covered all the best practices.. Good stuff..

    ReplyDelete
  3. It's highly recomended to move Log to another drive, as well as data should not be stored on the volumne C default. Re-allocating helps managed data effectively.

    ReplyDelete
  4. Anonymous, I said in my comments on step twelve that the drives should be broken out as follows:

    DATA Drive: User database directory (this is where SharePoint databases will go. In this case, SharePoint is the user)

    TEMPDB Drive: Temp DB Directory and Temp DB log directory.

    LOG Drive: User database log directory.

    The graphic shows all C drive, but drive letters are going to vary by installation for other alternative drives.

    ReplyDelete
    Replies
    1. Hi Doug,

      Thanks for posting the above steps.

      I am going to install my first instance of sharepoint 2013.

      We will be going with a 2 tier approach, SQL 2012 on our DB server and SP 2013 with web and app on the other server which will part of a farm.

      In your approach you installed very little SQL options, in my scenario where i have a dedicated DB server, should i install more SQL options such as Sharepoint services?

      Thanks in advance,
      Gerry.

      Delete
    2. I recommend installing only what you need. If this is your first installation, I recommend selecting the options I outlined above (the minimum) and adding on as you need it. You do not need to install any SharePoint functionality in SQL server unless you plan on doing business intelligence. And even then, I recommend installing the basic functions first, getting SharePoint working and then adding on the Business Intelligence.

      Delete
  5. Doug, you didn't say whether to install tabular or dimensional.

    ReplyDelete
  6. Edtunes, tabular or dimensional is for the analysis services instance. This guide is for installing the database instance required for SharePoint. I may cover analysis services and PowerPivot in another post.

    ReplyDelete
  7. I work with a client that chose to install almost all of the features in SQL 2012. I see on some recommendation sites that they install SQL components a bit at a time. It seems to me that this is done for a reason and not all at once. We are looking to use a lot of the BI reporting, powerpivot, and the SharePoint Access App.

    What is the best practice?

    ReplyDelete
  8. Yes Like Ethan, we are also looking to use the full BI Stack with powerpivot, excel services in Sharepoint 2013. Any details regarding setting up or configuring this will greatly be appreciated.

    ReplyDelete
    Replies
    1. Ethan and Preetham, I recommend installing the SQL Server 2012 database instance on your SQL server first. Get your SharePoint installation up and running and working properly. Then add Analysis Services for SharePoint (PowerPivot) on the appropriate SQL Server. Note that you have to have Service Pack 1 when installing SQL Server Analysis Services for SharePoint 2013.

      I provide an overview of the PowerPivot components here: http://www.sharepointdoug.com/2013/04/powerpivot-component-overview.html There are lots of links in that post with installation instructions. I hope to do a post soon that covers the installation in more detail.

      Delete
  9. I am installing a development environment on my local workstation without using a VM. I am on our company domain and don't have the freedom to create a new administrator account. Since the SQL 2012 and SharePoint 2013 are installed locally what is the impact of using my regular authenticated account as my SQL admin account and SharePoint admin account?

    ReplyDelete
  10. Great post. Thanks a lot! For those like me who forgot to install .NET 3.5 prior to SQL Server 2012, can save the date with the following link from Daniel Classon at http://www.danielclasson.com/install-net-framework-35-server-2012/

    ReplyDelete
  11. Sir i have failed to installing management tools in sql server 2012,how can over come this problem

    ReplyDelete
  12. how about collation??

    ReplyDelete
  13. I am facing issue in step 9. i want to give service account but when i click on password it is getting hanged. What is the issue.
    My service account is domain account and part of local server administrator.

    Now i have put NT account for installation i am planning to change it after installation would it work for sharepoint 2013.

    ReplyDelete
  14. Great post! I noticed that your link to the KB article for collation settings was only for MOSS/WSS3, though. I contacted MS to see if the settings were for 2013 as well. They pointed me to an article stating the collation setting is the same but for adding a server to an existing farm. I confirmed with them that this also covers a brand new install, so the same collation settings continue in 2013 as in MOSS/WSS3. Thought I'd pass that along.

    ReplyDelete
  15. Nice blog post. Many beginners will sure learn from this post. Thanks for posting this.

    ReplyDelete
  16. Nice blog post. A great guide for even just basic SQL installation (not using SharePoint).

    ReplyDelete
  17. As a programmer it is very needed to me. Good job buddy.

    internet provider

    ReplyDelete
  18. You just made something that I thought was so difficult be, truly, so easy! Thanks for the post!

    ReplyDelete
  19. This being really informative post to read and know how to install sql. Thanks for sharing this valuable information.

    ReplyDelete