patternsqlMinor
Unattended install to listen on specified interface
Viewed 0 times
installunattendedlisteninterfacespecified
Problem
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:
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?
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=SQLAm 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?
Solution
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.
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.
Note this script is not well-tested so you may need to tweak it for your scenario.
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 1433EDIT:
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.
Code Snippets
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 1433Function 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"Context
StackExchange Database Administrators Q#242337, answer score: 5
Revisions (0)
No revisions yet.