By Jack Douglas


2019-07-08 11:10:49 8 Comments

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?

2 comments

@Dan Guzman 2019-07-08 11:44:19

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.

@Mehmet YAVUZ 2019-07-08 15:11:48

You can't have more than one IP adress for standalone sql servers. Which is server's IP adress. You can (and must) only set the port after setup.

Related Questions

Sponsored Content

1 Answered Questions

install postgresql in unattended error

1 Answered Questions

[SOLVED] initdata: No memory for kernel buffers

1 Answered Questions

1 Answered Questions

[SOLVED] How can I connect to SQL Server from All IPs?

2 Answered Questions

2 Answered Questions

2 Answered Questions

[SOLVED] Dictate edition during unattended install?

Sponsored Content