大网数据广告活动
服务器运维问答

Set static TCP port in SQL Server unattended installation

· 52CE 我爱测

I have an unattended installation of SQL Server 2016 using the .INI answer file. In my answer file, I enable TCP like this: ; Specify 0 to disable or 1 to enable the TCP/IP protocol. TCPENABLED="1" Once the installation is finish…

问题原文

原帖链接 · Ondrej Tucny · CC BY-SA 4.0

I have an unattended installation of SQL Server 2016 using the .INI answer file. In my answer file, I enable TCP like this:

; Specify 0 to disable or 1 to enable the TCP/IP protocol. 
TCPENABLED="1"

Once the installation is finished, I also create an alias using a PowerShell script. So far so good. However, the SQL Server instance is installed with dynamic ports enabled and I can't see a way to specify a static TCP port (I want to use the standard 1433) in the answer file. Hence the alias won't work.

How can I set a static TCP port, either via the answer file, or using PowerShell?

已采纳解答

原帖链接 · Ondrej Tucny · CC BY-SA 4.0

After long hours searching for a viable solution I came up with this PowerShell function that can set the TCP port of a specific SQL Server instance:

function SetPort($instance, $port)
{
    # fetch the WMI object that contains TCP settings; filter for the 'IPAll' setting only
    # note that the 'ComputerManagement13' corresponds to SQL Server 2016
    $settings = Get-WmiObject `
        -Namespace root/Microsoft/SqlServer/ComputerManagement13 `
        -Class ServerNetworkProtocolProperty `
        -Filter "InstanceName='$instance' and IPAddressName='IPAll' and PropertyType=1 and ProtocolName='Tcp'"

    # there are two settings in a list: TcpPort and TcpDynamicPorts
    foreach ($setting in $settings)
    {
        if ($setting -ne $null)
        {
            # set the static TCP port and at the same time clear any dynamic ports
            if ($setting.PropertyName -eq "TcpPort")
            {
                $setting.SetStringValue($port)
            }
            elseif ($setting.PropertyName -eq "TcpDynamicPorts")
            {
                $setting.SetStringValue("")
            }
        }
    }
}

Now, this function is used like this:

SetPort "SQLInstance" 1433

The script shall be run elevated, i.e. Run as Administrator.

Credits go to this blog post which pointed me to the right direction.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2018-08-23)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。