Building SharePoint 2016 development environment – Part 8 – Installing SQL 2016 ready for SharePoint 2016.

Posted on by

A few years ago I wrote “Build your SharePoint 2013 development machine on Windows Server 2012” series, I mainly work in the cloud now, but as the blogs was so popular, I thought I would create a new series for the newer version of SharePoint.

You can access other parts of this post below.

Setting up directories for SQL.

We are going to set up following directories on the d: drive.

  • Bring up Explorer by click on Windows Key + E
  • Double click on the D drive.
  • Create the following directories.
    • D:\SQL
    • D:\SQL\Data
    • D:\SQL\Logs
    • D:\SQL\Temp\Data
    • D:\SQL\Temp\Logs
    • D:\SQL\Backup

Installing SQL Server 2016 Standard Edition 64 bit

From the part 5 of these blogs we have already inserted the ISO file into the virtual machine. By opening Explorer inside the virtual machine, you should see DVD Drive with the SQL disk inserted.

  • Double click on the DVD drive, or run Setup.exe
  • The SQL Server Installation Center will open up. On the left hand side, click Installation.
  • Click on New SQL Server stand-alone installation or add features to an existing installation
  • The product key should already be entered, click Next, accept the License Terms. Click Next.
  • Tick Use Microsoft Update to check for updates (Recommended). Click Next.
  • Setup install rules will identify any problems that might occur when installing SQL Server Setup support files. They should all passes. (Ignore warnings) Click Next.
  • On the Feature Selection Page. Select Database Engine Services, Full-Text and Semantic Extractions for Search. Click Next.
  • On the Installation Rules page click Next.
  • On the Instance Configuration page click Next.
  • On the Server Configuration page, change the SQL Server Agent Start Up Type to Automatic. Also change the SQL Server Database Engine to use your SP_SQL account, and enter the password. Pa55w0rd
  • Click Next
  • On the Database Engine Configuration page, select Mixed Mode and enter the password and confirm password as Pa55w0rd. And Click Add Current User.
  • On the Data Directories tab, change the location of the directories.
    • Data Root Directory – d:\SQL
    • User Database Directory – d:\SQL\Data
    • User Database Log Directory – d:\SQL\Logs
    • Backup Direcotory – d:\SQL\BackUp
  • On the TempDB tab
    • Data Directories – d:\SQL\Temp\Data
    • Log Directory – d:\Sql\Temp\Logs
  • On the FILESTREAM tab, tick Enable FileStream for Transact-SQL access and Enable FILESTREAM for file I/O access. Click Next.
  • On the Ready to Install page, click Install
  • After installation your SQL Server 2016 is installed. You may be asked to reboot.

Installing SQL Server Management Tools

It seems that SQL 2016 have now separated out the SQL Server and SQL Server Management Tools. So this is an additional step to carry out.

  1. Back on the SQL Server Installation Center (run setup.exe from DVD drive if not showing), click on Install SQL Server Management Tools
  2. This takes you off to a website address https://msdn.microsoft.com/en-us/library/mt238290.aspx to download the SQL Server Management Studio (SSMS) seperately.
  3. Click the download link, and run it. (Or Save and run it afterwards) Over 800MB in size.
  4. Click Install.
  5. Once installed, you will get a success message. Close this screen.

Configuring SQL Server 2016

  1. From the start screen type SQL Server Configuration Manager and select the application.
  2. Click to expand SQL Server Network Configuration (not the 32 bit), and choose Protocols for MSSSQLServer, and ensure TCP/IP and Named Pipes are enabled. To enable them right click them and select Enable. Click OK at the warning.
  3. Close the SQL Server Configuration Manager.

Apply the DisableLoopbackCheck Registry Fix

  1. Click the Windows PowerShell icon in the Taskbar.
  2. Run the following PowerShell commands, pressing [Enter] after each one:
    $regKeyPath = "HKLM:\System\CurrentControlSet\Control\Lsa"
    $key = "DisableLoopbackCheck"
    New-ItemProperty –Path $regKeyPath –Name $key –Value "1" –PropertyType dword
    view raw DisableLoopBackCheck.ps1 hosted with ❤ by GitHub

Giving SP_Setup account access

Note: If you continue without doing the DisableLoopback Check registry you will get an error message saying “Login Failed. The login is from an untrusted domain and connot be used with Windows authentication”

  1. From the start screen type SQL Server 2016 Management Studio and open the application.
  2. Change the server name to SQL2016 (we set a host name in an earlier post)
    and logon as Windows authentication.
  3. Expand Security from the object explorer and right click Logins and select New Login…
  4. Next to the Login Name click Search
  5. Ensure the Location is set for Entire Directory, and then type SP_Setup and click Check Names.
  6. Click OK.
  7. On the left hand panel, select the Server Roles page, and tick dbcreator, securityadmin and sysadmin. Then click OK.

Now we can think about Installing SharePoint 2016.

Now is another good time to shut down both Domain Controller, and the SharePoint machine and take a checkpoint/snapshot before continuing.