Skip to content

How to install SQL 2016 on Server Core 2016

#1. Create a service account for SQL in AD.

#2. Add that service account to the computers Local Admin groups.

#3. Create a Silent Install INI file. Lots of details can be found here but here’s an example for a Named Instance, in this case “VMM”:

; ssNoVersion Configuration File
[OPTIONS]

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

; Specifies features to install, uninstall, or upgrade. The lists of features include SQLEngine, FullText, Replication, AS, IS, and Conn.
FEATURES=SQLENGINE,FullText,Replication,AS,IS,Conn

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the ssNoVersion Database Engine (SQL), or Analysis Services (AS).
INSTANCENAME="VMM"

; Specify the Instance ID for the ssNoVersion features you have specified. ssNoVersion directory structure, registry structure, and service names will incorporate the instance ID of the ssNoVersion instance.
INSTANCEID="VMM"

; Account for ssNoVersion service: Domain\User or system account.
SQLSVCACCOUNT="domain.local\SQL_SERVICE_ACCOUNT"

; Windows account(s) to provision as ssNoVersion system administrators.
SQLSYSADMINACCOUNTS="domain.local\admin1,domain.local\admin2"

; The name of the account that the Analysis Services service runs under.
ASSVCACCOUNT= "domain.local\SQL_SERVICE_ACCOUNT"

; Specifies the list of administrator accounts that need to be provisioned.
ASSYSADMINACCOUNTS="domain.local\admin1,domain.local\admin2"

; Specifies the server mode of the Analysis Services instance. Valid values are MULTIDIMENSIONAL, POWERPIVOT or TABULAR. ASSERVERMODE is case-sensitive. All values must be expressed in upper case.
ASSERVERMODE="MULTIDIMENSIONAL"

; Optional value, which specifies the state of the TCP protocol for the ssNoVersion service. Supported values are: 0 to disable the TCP protocol, and 1 to enable the TCP protocol.
TCPENABLED=1

;Specifies acceptance of License Terms
IAcceptSQLServerLicenseTerms="True"

#4. Copy that file to the server into C:\Temp or somewhere similar

#5. Run setup from command line like so:

D:\Setup.exe /Q /SQLSVCPASSWORD="SERVICEACCOUNTPASSWORD" /ASSVCPASSWORD="SERVICEACCOUNTPASSWORD" /ConfigurationFile=c:\temp\SilentInstall.ini

#6. Use SQLCMD.EXE to enable remote access, change the hostname and instance name as needed:

c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn>SQLCMD.EXE -S SQL01\VMM
1> EXEC sys.sp_configure N'remote access', N'1'
2> GO
Configuration option 'remote access' changed from 1 to 1. Run the RECONFIGURE statement to install.
1> RECONFIGURE WITH OVERRIDE
2> GO
1> quit

#7. Optionally set the SQL Browser to Autostart:

sc config SQLBROWSER start= auto
net start SQLBROWSER

#7. Use PowerShell to create basic Firewall Rules:

powershell.exe
New-NetFirewallRule -DisplayName “SQL Default Instance” -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 Server Browser Service” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Server Browse Button Service” -Direction Inbound –Protocol UDP –LocalPort 1433 -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
New-NetFirewallRule -DisplayName “SQL Analysis Services” -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow
New-NetFirewallRule -DisplayName “SQL Browser” -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow
New-NetFirewallRule -DisplayName “HTTP” -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow
New-NetFirewallRule -DisplayName “SSL” -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow

#8. Use PowerShell to create the SQL instance specific firewall rule that allows for dynamic ports of non-default SQL Instances, notice that the instance name (VMM) is used twice, once for the name of the rule and once in the program path:

powershell.exe
New-NetFirewallRule -DisplayName “SQL Instance (VMM)” -Direction Inbound -Program "C:\Program Files\Microsoft SQL Server\MSSQL13.VMM\MSSQL\Binn\sqlservr.exe" -Action allow

#9. This isn’t needed if you enabled TCP in the INI file but I found Microsoft’s own documentation to be incorrect and it made me angry, so here’s how to actually enable TCP, change the ServerInstance Name as needed:

powershell.exe
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.SqlWmiManagement.dll'
$mc = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='VMM']/ServerProtocol[@Name='Tcp']"
$Tcp = $mc.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$Tcp.Alter()
$Tcp
Published inTech

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *