For this post I will go through the install process for SQL server 2016. It is fairly straight forward but there are additional concerns with the setup depending on what you will be using SQL server for.
To begin with start the installer from your downloaded .iso file. Click Installation and choose ‘New SQL Server stand-alone installation or add features to an existing installation’.
This will start the SQL Server 2016 Setup wizard. Either choose a trial or enter your product key now.
Accept the terms and click Next, after the rule check decide whether or not you want to use Windows update to keep SQL Server 2016 up to date.
The Setup files are then installed
As I will be using SQL Server 2016 with SCCM 2016 I have selected ‘Database Engine Services’ and ‘Reporting Services’ – your needs may vary. Unfortunately we cannot install the client tools (i.e. Management Studio) at this stage as we could with previous versions of SQL server but we will come to that later. When ready click Next.
Choose whether you want a named instance or are sticking with the default instance. You can also choose a different name for the instance. Having multiple instances allows you to have multiple installations of SQL Server on a single Windows operating system. Click Next to continue.
At this stage you can specify a domain account to use for each SQL service (recommended). Enter the credentials for the account you are using and then click the collation tab.
Usually you would be fine with the default collation settings but SCCM 2016 requires a collation mode of SQL_Latin1_General_CP1_CI_AS. (This is very important – if you do not select the correct collation mode it can cause serious issues later on). Click Next when ready.
Here select the authentication mode you will be using for SQL server. I always use Mixed Mode for extra compatibility with some legacy applications that still require it. This just means that you have to set an extra password for the SA account. Here I will also specify a domain admin account that I want to be an SQL Server administrator. When ready click the ‘Data Directories’ tab.
I prefer to separate out my SQL installations in three locations:
1. C:\ Drive – for the operating system and SQL Server program files
2. D:\ Drive for all database files
3. E:\ Drive for log files and backups
I leave the TempDB and FILESTREAM settings as default, but you may need to configure them differently. Click Next when ready.
If you are installing Reporting services, select ‘Install and configure’ and click Next
When ready verify the install options and click Install
SQL Server 2016 will now be installed using the specified options
You should see a success screen like the below when complete, click close
You will now need to install the SQL management tools to be able to manage the server. From the .iso media start the SQL installer again and select ‘Installation’ and then ‘Install SQL Server Management Tools’.
This basically takes you to a website where you can click ‘Download SQL Server Management Studio 17.2’
Once downloaded start the installer and follow the prompts in the wizard
Once the install is complete you will need to restart the server. When the restart is complete open SQL Management Studio. You should now be able to connect to your SQL instance using Windows authentication.
You can now manage the SQL Server instance
Open the SQL Server Configuration manager and go to SQL Server Network Configuration. Click Protocols for MSSQLSERVER and click on Named Pipes. change the setting to Enabled and click ok.
Open the TCP/IP Properties and click IP addresses. All of the IPs should have the port set as 1433 and should be Active. You should now be able to connect to your SQL server remotely via Management Studio (you may have to enable access through port 1433 on the Windows firewall).
SQL Server 2016 is now installed and ready for use.
nice article