Unattended SQL Server Install to Listen on Specified Interface

installationsql server

I'm looking to install several versions of SQL Server side-by-side to run simultaneously. I'd like to have them listen on different IP addresses.

They will be running on Windows Server 2019 Core.

I currently install using something like this:

Setup.exe /qs /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SAPWD="MYSECRETPASSWORD" /SQLSVCINSTANTFILEINIT="True" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS /SECURITYMODE=SQL

Am I right that there are no switches on Setup.exe that specify the IP address to listen to? If not what is the simplest way of configuring this from the command line after Setup.exe has run?

Best Answer

With SQL Server on Windows, the registry entries can be modified using PowerShell. Below is an example with SQL Server 2017 paths for the default instance. Of course, the instance will need to be restarted for changes to become effective.

Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpdynamicports -Value ''
Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpport -Value 1433

EDIT:

One can also target specific network interfaces by identifying the desired key and changing as desired. Below is an example PS function that finds the entry by IP address and modifies the values. This can be used to enable/disable TCP for each instance and interface as needed.

Function Set-SqlInterfaceProtocol($SQLInstanceTcpPath, $IPV4Address, $Enabled, $Active, $Port) {

    $interface = Get-ChildItem $SQLInstanceTcpPath `
        | ForEach-Object {Get-ItemProperty $_.pspath} `
        | Where-Object -Property IPAddress -EQ $IPV4Address

    if($interface -eq $null) {
        throw "No entry found for IP address $IPV4Address"
    }

    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "Enabled" -Value $Enabled
    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "Active" -Value $Active
    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "TcpPort" -Value $Port
}

$SQLInstanceTcpPath = "HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp"
Set-SqlInterfaceProtocol -SQLInstanceTcpPath $SQLInstanceTcpPath -IPV4Address "10.1.1.123" -Enabled "1" -Active "1" -Port "1433"

Note this script is not well-tested so you may need to tweak it for your scenario.

Related Question