The purpose of this article is to quickly cover the steps on how to install SQL 2017. In particular this installation will serve a SharePoint 2016 Server deployment. Since for my homelab I’m running more testing around Microsoft Office 365 for both Online and On-Premises environments this was the chance to start with a clean install of the SharePoint 2016 with the latest patches and features. The idea is to create a new installation of SharePoint 2016 followed by the latest patches. All covered in a dedicated article.
The good news about SQL 2017 is that it is available with a Developer Edition. This one covers all the features of the Enterprise Edition and is supported only for test and development environments. and it also makes a good candidate for homelab scenario too. More information about what’s included in each edition is available here.
When deploying SQL as a backbone for SQL facing applications there are certain design principles that should be adopted. SharePoint as one of these applications makes no exception. It is always a good idea to review and apply the SQL best practices for the particular application.
In the case of SharePoint deployments the following settings and configurations follow:
- Do not enable auto-create statistics on SharePoint content databases.
- Do not manually restrict database size in SQL Server.
- Set Max Degree of Parallelism (MAXDOP) to 1 for instance of SQL Server that serves the SharePoint databases
- Create a SPAdmin user login in SQL with the DBCreator, Public, SecurityAdmin and SysAdmin roles. The reason for these specific SQL Server Roles is due to the fact on how the various SharePoint APIs “call and operate” with SQL objects like SQL Databases (eg. when creating Site Collections etc..)
- TempDBÂ and the volume where it is sitting should be capable enough to “mount” SharePoint Databases during restores.
Other recommendations will be addressed on a separate article with the most popular configurations.
How to install SQL ready for SharePoint 2016
The process to install SQL 2017 is pretty simple and straight forward. the recommendation is to run this on a fully updated Windows Server 2016. This will speed up some of the pre-reqs install like .NET dependencies. First thing would be to download the latest image, mount the ISO and execute with local admin rights the wizard.
In the installation section of the wizard the links to install SQL 2017 on a new node. This article covers the stand-alone deployment. A similar approach can be used with Failover SQL Clusters as covered in this article based on SQL 2014.
In the Product Key section the option to choose the Edition. The Developer Edition provides the same functionalities as per the Enterprise one and only for test and development environments. Not for Production use.
Depending on the selected Edition the wizard will show the pertinent EULA.
The wizard now shows the main steps to prepare for SQL 2017 installation. Ideally an up to date Windows 2016 Server will shorten this phase for missing updates.
Next is a matter of choosing the main SQL components. From this wizard the Database Engine Service will suffice for SharePoint 2016. The same wizard can be used on a later stage to add other components like SQL Server Reporting Services (SSRS), Client Tools and a lot more.
Default instance name is MSSQLServer. For single instance this should be accepted. If planning to install separate instances on the same SQL Server it is a good idea to name them after the application these are serving. Something like SP2016 in this example.
In the server configuration the wizard allows to specify which SQL services and pertinent account names should be used to start such services. Default configuration is fine using the built-in NT Service.
In the case of the collation the Database Engine should be set to standard Code Page 1252 or Latin1 General Case Insensitive. It is important to get this one right now and avoid configuration issues at a later stage.
In the Database Engine Configuration the steps to enable the authentication mode (Windows authentication or Mixed mode) plus which uses account have admin privileges to the SQL Server Database Engine.
In the Data Directories the location for all system Databases, User Created databases and Backup Directory. Ideally these should be on separate volumes. Even better when each one of them has dedicated partitions for Database Data files separated by Database Logs files. For a test install all DBs can sit on the same volume providing there is enough space for growth.
With TempDB further settings are available to fine tune the size, location and allocation of TempDB files for better performances.
The FILESTREAM option allows to offload the “blob” content from a SQL Database to an external share by leaving a “stub” pointing at the pertinent content file. This has the advantage of
- reducing the size of the SQL Database
- increase the performances for large files as they are sitting on a dedicated storage
- increase SQL Queries execution
- be completely transparent to the SQL facing application (Microsoft SharePoint in this case)
For the time being and a test environment this feature can be disabled.
The wizard has now all the information to install SQL 2017 on Windows Server 2016. Very nicely all the configuration steps have been captured into Configuration File that can be used for unattended deployments of SQL 2017 using the same configuration.
At this point the wizard can proceed with the deployment.
A few moments later the process to install SQL 2017 is completed successfully.
Install Management Studio
Next from the same wizard to install SQL 2017 the link to deploy the Server Management Studio.
The wizard will automatically point and check the latest version of SQL Management Studio (v18.0 at the time of writing).
After downloading the package is a matter of running with local admin rights and choose the location for the main binaries install.
The wizard automatically detects the components and shows the entire process.
A final restart is required in order to complete the setup.
Before proceeding with SharePoint installation it is important to set the Max Degree of Parallelism to “1”.
As mentioned earlier the other important configuration is about the SharePoint Admin service account privileges on the SQL server. This can be checked by Security > Logins > SPAdmin user. This account should have explicit permissions as DBCreator, Public, SecurityAdmin and SysAdmin.
Thanks for information
You’re welcome and thanks for your comment!