Installing SQL Server 2016 like a boss

Every once in a while you need to install SQL Server. It could be the SQL Server Express edition because you are using the Database for MDT Lite Touch, or the full SQL Server to support a System Center product like ConfigMgr Current Branch. Either way, both versions supports installing updates as part of the setup, and here follows the steps to install SQL Server 2016 with SP1 fully automated/unattended.

Step-by-Step guide

In the scenario the SQL Server setup is intended for ConfigMgr Current Branch but you use the same technique for all types of SQL Installs. The overview steps are:

• Prepare the SQL Setup files
• Prepare the unattended file for the SQL Setup engine
• Run the installations (yes there are more then one now)

Step 1 – Prepare the SQL Server 2016 Setup files

Back in the days it was quite common that you slipstreamed in updates yourself for SQL, but luckily, now Microsoft does it for you. So for example, there is a download of SQL Server 2016 Standard that has SP1 in it already.

Note: Starting with SQL Server 2016, the management tools (SQL Server Management Studio), are no longer included, and have to be downloaded separately.

1. Anyway, first, I downloaded and extracted the SQL Server 2016 with SP1 x64 ISO to the C:\Setup\SQL Server 2016 folder.  

2. The next step is to download SQL Server Management Studio (16.5.1) from this link: http://go.microsoft.com/fwlink/?LinkId=531355 and save it in the C:\Setup\SQL Server Management Studio folder.

Step 2 – Prepare the unattended file for the SQL Server 2016 Setup engine

When installing SQL Server, you should always use an unattend file for the following reasons:

• It's automated
• Yes, because its AUTOMATED!
• You have all setup options easily summarized
• You automatically have your setup documented
• It's faster than a manual setup

Note: Since SQL Server 2016 doesn't include SQL Server Management Studio anymore, you can't use the SSMS and ADV_SSMS features in your unattend file.

Below is an sample unattend file I used for a SQL Server 2016 installation intended for ConfigMgr Current Branch. The TempDB files are stored on F: , the SQL Database files are stored on G: , and the SQL Log files are stored on H: – For more info about ConfigMgr (and SQL) disk layout and sizing see this post: https://deploymentresearch.com/420/Sizing-your-ConfigMgr-2012-R2-Primary-Site-Server

; ************************************************************************************************************************
;
; Created:    December 14, 2016
; Version:    1.0
;
; Disclaimer:
; This script is provided "AS IS" with no warranties, confers no rights and
; is not supported by the authors or DeploymentArtist.
;
; Author - Johan Arwidmark
;     Twitter: @jarwidmark
;     Blog   : https://deploymentresearch.com
;
; ************************************************************************************************************************

;SQL Server 2016 Configuration File
[OPTIONS]

; Agree to terms

IACCEPTSQLSERVERLICENSETERMS="True"

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.

ACTION="Install"

; Detailed help for command line argument ENU has not been defined yet.

ENU="True"

; Parameter that controls the user interface behavior.

; UIMODE="Normal"

; Setup will not display any user interface.

QUIET="True"

; Setup will display progress only, without any user interaction.

QUIETSIMPLE="False"

; Specify whether SQL Server Setup should discover and include product updates.

UpdateEnabled="True"

; Specifies features to install, uninstall, or upgrade.

FEATURES=SQLENGINE,RS,CONN

; Specify the location where SQL Server Setup will obtain product updates.

UpdateSource="MU"

; Displays the command line parameters usage

HELP="False"

; Specifies that the detailed Setup log should be piped to the console.

INDICATEPROGRESS="False"

; Specifies that Setup should install into WOW64.

X86="False"

; Specify the root installation directory for shared components.

INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

; Specify the root installation directory for the WOW64 shared components.

INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions.

INSTANCENAME="MSSQLSERVER"

; Specify the Instance ID for the SQL Server features you have specified.

INSTANCEID="MSSQLSERVER"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft.

SQMREPORTING="True"

ERRORREPORTING="False"

; RSInputSettings_RSInstallMode_Description

RSINSTALLMODE="DefaultNativeMode"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases.

ERRORREPORTING="True"

; Specify the installation directory.

INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; Agent account name

AGTSVCACCOUNT="NT Service\SQLSERVERAGENT"

; Auto-start service after installation.

AGTSVCSTARTUPTYPE="Manual"

; CM brick TCP communication port

COMMFABRICPORT="0"

; How matrix will use private networks

COMMFABRICNETWORKLEVEL="0"

; How inter brick communication will be protected

COMMFABRICENCRYPTION="0"

; TCP port used by the CM brick

MATRIXCMBRICKCOMMPORT="0"

; Startup type for the SQL Server service.

SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3).

FILESTREAMLEVEL="0"

; Set to "1" to enable RANU for SQL Server Express.

ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine.

SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: DomainUser or system account.

SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"

; Windows account(s) to provision as SQL Server system administrators.

SQLSYSADMINACCOUNTS="VIAMONSTRA\Administrator" "BUILTIN\Administrators"

; Default directory for the Database Engine backup files.

SQLBACKUPDIR="G:\SQLBCK"

; Default directory for the Database Engine user databases.

SQLUSERDBDIR="G:\SQLDB"

; Default directory for the Database Engine user database logs.

SQLUSERDBLOGDIR="H:\SQLLOG"

; Directory for Database Engine TempDB files.

SQLTEMPDBDIR="F:\SQLTMP"

; Provision current user as a Database Engine system administrator for SQL Server 2012 Express.

ADDCURRENTUSERASSQLADMIN="False"

; Specify 0 to disable or 1 to enable the TCP/IP protocol.

TCPENABLED="1"

; Specify 0 to disable or 1 to enable the Named Pipes protocol.

NPENABLED="0"

; Startup type for Browser Service.

BROWSERSVCSTARTUPTYPE="Disabled"

; Specifies which account the report server NT service should execute under.

RSSVCACCOUNT="NT AUTHORITY\SYSTEM"

; Specifies how the startup mode of the report server NT service.

RSSVCSTARTUPTYPE="Automatic"

Note: If you use these samples outside the ViaMonstra hydration environments, you probably want to change the account(s) to provision as SQL Server system administrators.

SQLSYSADMINACCOUNTS="VIAMONSTRA\Administrator" "BUILTIN\Administrators"

 

Step 3 – Run the installations

In this section you first start the unattended setup of SQL Server 2016, and then you start the unattended setup of SQL Server Management Studio.

Start by creating the SQLConfigurationFile.ini file, per the sample in Step 2, in the C:\Setup folder, and run the setup.exe with the /CONFIGURATIONFILE=C:\Setup\SQLConfigurationFile.ini switch.

Running the unattended setup.

Or, have a script (wrapper) run it.

Or, have the task sequence run it.

Or, have the task sequence run a script (wrapper) that runs it. This is the recommended option.

You get the point. Automation is King, and as long it is automated you are not very likely to hear me complain (much) 🙂

Verifying the SQL Server 2016 Setup

To verify that the setup completed, check the setup logs in C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log.

Installing SQL Server Management Studio

Next step is to install SQL Server Management Studio unattended. You do that by running the SSMS-Setup-ENU.exe with the /install /quiet /norestart switches. Like this:

SSMS-Setup-ENU.exe /install /quiet /norestart

Verifying the SQL Server Management Studio Setup

To verify that the setup completed, check the setup logs in %USERPROFILE%\AppData\Local\Temp\SsmsSetup (%TEMP%).

Written by Johan Arwidmark