SQL Server自动化部署中SQLPS脚本设置IPALL端口失败求助

我正在实现SQL Server全自动化安装，需求是启用TCP/IP并将IPALL的TCP端口设置为指定值（例如12345）。当前部署流程分为两部分：

1. 批处理安装SQL Server，运行正常：

echo Silent SQL Install, Please Wait...
echo.
Pushd SQLEXPRADV_x64_ENU
SETUP.EXE /ConfigurationFile=ConfigurationFile.ini

2. 调用PowerShell脚本设置端口，但执行失败：

Powershell.exe -executionpolicy bypass -File "PS_Port_12345.ps1"

但直接在PowerShell控制台中运行以下代码可以正常工作：

Import-Module SQLPS -DisableNameChecking -Force
($Wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $env:COMPUTERNAME)
($uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='MYSQLTEST']/ServerProtocol[@Name='Tcp']")
# Getting settings
($Tcp = $wmi.GetSmoObject($uri))
$Tcp.IsEnabled = $true
($Wmi.ClientProtocols)
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="12345"
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
$Tcp.Alter()
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties

我推测问题原因：SQLPS模块安装后，PowerShell控制台能自动识别模块路径，但通过批处理调用PS1文件时无法加载该模块环境，导致脚本失败。尝试过修改路径、复制模块到系统目录，均无效。

1. 显式指定SQLPS模块完整路径 #

SQLPS模块的路径通常位于SQL Server安装目录下，版本号需对应你的SQL Server版本（例如2019对应150，2016对应130）。在PS1脚本开头直接用完整路径导入：

# 替换为你的SQL Server版本对应的路径
$sqlpsPath = "C:\Program Files\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS"
Import-Module $sqlpsPath -DisableNameChecking -Force

2. 绕过SQLPS模块，直接加载SMO程序集 #

如果导入模块仍有问题，可以直接加载SQL Server Management Objects (SMO)的相关程序集，避免依赖SQLPS模块：

# 加载SMO核心程序集
$requiredAssemblies = @(
    "Microsoft.SqlServer.Management.Common",
    "Microsoft.SqlServer.Smo",
    "Microsoft.SqlServer.SmoExtended",
    "Microsoft.SqlServer.WmiEnum"
)

foreach ($asm in $requiredAssemblies) {
    try {
        [System.Reflection.Assembly]::LoadWithPartialName($asm) | Out-Null
    }
    catch {
        Write-Error "Failed to load assembly: $asm"
        exit 1
    }
}

# 端口设置逻辑（优化为按属性名匹配，避免索引兼容性问题）
$wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $env:COMPUTERNAME
$uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ServerInstance[@Name='MYSQLTEST']/ServerProtocol[@Name='Tcp']"
$tcpProtocol = $wmi.GetSmoObject($uri)
$tcpProtocol.IsEnabled = $true

$ipAllSettings = $wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']")
$ipAllSettings.IPAddressProperties | Where-Object { $_.Name -eq "TcpPort" } | ForEach-Object {
    $_.Value = "12345"
}

$tcpProtocol.Alter()

3. 确保批调用时使用管理员权限 #

修改SQL Server配置需要管理员权限，可在批处理中通过RunAs启动PowerShell：

Powershell.exe -executionpolicy bypass -Command "Start-Process Powershell.exe -ArgumentList '-executionpolicy bypass -File ""PS_Port_12345.ps1""' -Verb RunAs"

4. 自动检测SQLPS模块路径 #

在PS1脚本开头添加自动检测逻辑，避免硬编码路径：

# 自动查找SQLPS模块
$sqlpsModule = Get-Module -ListAvailable SQLPS | Select-Object -First 1
if (-not $sqlpsModule) {
    # 遍历常见SQL Server版本路径
    $commonVersions = @("150", "140", "130", "120")
    foreach ($ver in $commonVersions) {
        $testPath = "C:\Program Files\Microsoft SQL Server\$ver\Tools\PowerShell\Modules\SQLPS"
        if (Test-Path $testPath) {
            $sqlpsModule = $testPath
            break
        }
    }
}

if (-not $sqlpsModule) {
    Write-Error "SQLPS module not found on this machine"
    exit 1
}

Import-Module $sqlpsModule -DisableNameChecking -Force

内容的提问来源于stack exchange，提问作者Tika9o9