Setup SharePoint 2013 Single Server Environment – Part 4: SQL Server setup

Intro

This series describes the details and required steps for setting up a SharePoint 2013 Single Server Environment. This is typically used by developers in order to write custom solutions without interfering with one another. This type of environment is also is good for evaluation, training and demonstration purposes.
The series contains 6 parts:
  1. Environment Details
  2. VM setup
  3. Windows Server 2012 setup
  4. SQL Server setup
  5. SharePoint Installation and Initial Configuration
  6. Post-Installation Steps

SQL Server setup

This article contains the required steps to properly setup the SQL Server environment to properly run a single server SharePoint 2013 environment.

Install SQL Server 2012 Features

As per the previous article in this series, we should have downloaded the SQL Server 2012 ISO installation file and added it to the VM as a Virtual Optical Drive by now.
  1. From the virtual drive containing SQL installation, Run SETUP.EXE
  2. Select at least the following Core Features:
    1. Database Engine Services
    2. Management Tools – Basic
    3. Management Tools – Complete
  3. Select additional features based on specific needs

Setup Max Degrees of parallelism

Update the following PowerShell script, specifying the appropriate server instance.

#### Set MDOP to 1
Import-Module "sqlps" -DisableNameChecking
$sql = "EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO"
Invoke-Sqlcmd -Query $sql [-ServerInstance "SERVER\instance"]

Open a PowerShell console with administrator credentials and execute the above script.

Note: If you are unable to execute PowerShell commands on the server, you may need to change the execution policy. See this link for reference. 

Delegate Permissions

  1. Open SQL Management Studio
  2. Create SQL Login for sp_admin service account
  3. Assign server roles:
    1. dbcreator
    2. securityadmin

 

<< Windows Server 2012 Setup SharePoint Installation & Config..>>

 Reference

Using the Set-ExecutionPolicy Cmdlet in PowerShell