How to do it...
The following configuration will install SQL Server 2016 with the following components: Database Engine, Reporting Services, and Full Text:
Reporting Services and Full Text are required by OpsMgr.
- Log in as RLLAB\SQL-admin or another account that has SQL system admin rights.
- Create a folder named SqlConf in C:\.
- Open Notepad and copy the following into a new file:
;SQL Server 2016 Configuration File [OPTIONS] ; SQL Server License Terms IAcceptSQLServerLicenseTerms="True" ; Setup Work Flow: INSTALL, UNINSTALL, or UPGRADE. ACTION="Install" ; Privacy statement when ran from the command line. SUPPRESSPRIVACYSTATEMENTNOTICE="True" ; Microsoft R Open and Microsoft R Server terms IACCEPTROPENLICENSETERMS="True" ; English Version of SQL Server ENU="True" ; Setup UI will be displayed, without any user interaction. QUIETSIMPLE="True" ; Discover and include product updates during setup. UpdateEnabled="True" ; Microsoft Update will be used to check for updates. USEMICROSOFTUPDATE="True" ; Specifies features to install, uninstall, or upgrade. FEATURES=SQLENGINE,FULLTEXT,RS ; Update Location for SQL Server Setup (MU or share) UpdateSource="MU" ; Setup log should be piped to the console. INDICATEPROGRESS="False" ; Setup should install into WOW64. X86="False" ; Specify a default or named instance INSTANCENAME="MSSQLSERVER" ; Installation directory for shared components. INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server" ; Installation directory for the WOW64 shared components. INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server" ; Instance ID for the SQL Server features INSTANCEID="MSSQLSERVER" ; Specifies which mode report server is installed in. RSINSTALLMODE="DefaultNativeMode" ; TelemetryUserNameConfigDescription SQLTELSVCACCT="NT Service\SQLTELEMETRY" ; TelemetryStartupConfigDescription SQLTELSVCSTARTUPTYPE="Automatic" ; Specify the installation directory. INSTANCEDIR="C:\Program Files\Microsoft SQL Server" ; Agent account name AGTSVCACCOUNT="RLLAB\sql-svc" ; Auto-start service after installation. AGTSVCSTARTUPTYPE="Automatic" ; Startup type for the SQL Server service. SQLSVCSTARTUPTYPE="Automatic" ; Level to enable FILESTREAM feature at (0, 1, 2 or 3). FILESTREAMLEVEL="0" ; Specifies a collation type SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS" ; Account for SQL Server service SQLSVCACCOUNT="RLLAB\sql-svc" ; SQL Server system administrators. SQLSYSADMINACCOUNTS="RLLAB\rladmin" "RLLAB\sql-admin" ; Provision current user as a SQL Administrator 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" ; Add description of input argument FTSVCACCOUNT FTSVCACCOUNT="NT Service\MSSQLFDLauncher" ; Startup type for Browser Service. BROWSERSVCSTARTUPTYPE="Automatic" ; Specifies which account the report server NT service RSSVCACCOUNT="RLLAB\sql-svc" ; Specifies the startup mode of the report server service RSSVCSTARTUPTYPE="Automatic"
To enable silent SQL Server installation, you need to switch the parameter QUIETSIMPLE to False and next set the parameter QUIET to TRUE.
- Save the file as SQLConfigurationFile.ini in c:\sqlconf. Now that you have created the configuration file, let's proceed with the installation of SQL 2016 by using the file.
- Open the command prompt or PowerShell with administrative rights (Run as administrator).
- Navigate to SQL Server Source Media path and type the following:
Setup.exe /SQLSVCPASSWORD="P@ssw0rd1" /AGTSVCPASSWORD="P@ssw0rd1" /ASSVCPASSWORD="P@ssw0rd1" /RSSVCPASSWORD="P@ssw0rd1" /ConfigurationFile=c:\sqlconf\SqlConfigurationFile.ini
- Press Enter for the installation to start:
- You should see a window showing the installation progress (as shown in the preceding screenshot). The installation will proceed without user input.
Starting with SQL Server 2016, SQL Management Studio installation has been separated out from the SQL Server installation. Now it's available as a separate package that you need to download and install it manually. See https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.
- Also, I normally open Windows Firewall ports after installation to enable remote use of SQL Services. PowerShell automates this task:
New-NetFirewallRule -DisplayName "SQL Database Engine" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName "SQL Admin Connection" -Direction Inbound -Protocol TCP -LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName "SQL Browser Service" -Direction Inbound -Protocol UDP -LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName "SQL Service Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action allow
New-NetFirewallRule -DisplayName "SQL Debugger/RPC" -Direction Inbound -Protocol TCP -LocalPort 135 -Action allow