计划任务执行Test-SqlAvailabilityGroup脚本遇异常问题求助

我编写了一段PowerShell脚本，用于检查双服务器集群的SQL Server Always On可用性组（AG）状态以判定集群健康度。手动在两台服务器上运行脚本均正常，但通过计划任务执行时出现异常：主节点运行符合预期，备用节点却返回空结果而非Healthy状态。已尝试以管理员用户、Network Service和System身份运行该计划任务，均未解决问题。

脚本原内容：

$svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $hostname;
$svr.ConnectionContext.StatementTimeout = 0; 

foreach ($availabilityGroup in $svr.AvailabilityGroups)
{
    $agName = $availabilityGroup.Name
    $primary = $availabilityGroup.PrimaryReplicaServerName

    $witnessFile = Get-ClusterResource -Name \"File Share Witness\" | Select-Object *
    Write-Output(\"AG Name: \" + $agName)
    Write-Output(\"Primary Node: \" + $primary)
}

#test availability group health
$agPath = \"SQLSERVER:\\SQL\\$($Primary)\\DEFAULT\\AvailabilityGroups\\$($agName)\"
$agHealth = Test-SqlAvailabilityGroup -Path $agPath

$clusterNodes  = Get-ClusterNode 
$agStatus = \"UNHEALTHY\"
$clusterState = \"HEALTHY\"
$witnessFileStatus = \"UNHEALTHY\"

foreach ($node in $clusterNodes)
{
    if ($node.State -ne \"Up\") 
    {
        $clusterState = \"UNHEALTHY\"
    }
}

if ($agHealth.HealthState -eq \"Healthy\")
{
    $agStatus = \"HEALTHY\"
}

if ($witnessFile.State -eq \"Online\")
{
    $witnessFileStatus = \"HEALTHY\"
}

Write-Output(\"AG Status: \" + $agStatus)
Write-Output(\"Cluster State: \" + $clusterState)
Write-Output(\"Witness File Status: \" + $witnessFileStatus)  

问题根源分析 #

1. 变量大小写与作用域问题：脚本中循环内定义的是小写$primary，后续拼接路径时误用大写$Primary，PowerShell变量大小写敏感，备用节点执行时可能导致$Primary为空，进而让$agPath无效，Test-SqlAvailabilityGroup返回空结果。
2. SQLSERVER:\ PSDrive访问权限：计划任务环境可能未自动加载SqlServer模块，或运行账号无权限访问主节点的SQLSERVER:\路径。
3. 集群资源访问异常：备用节点上执行Get-ClusterResource时，若账号无集群资源读取权限，会导致$witnessFile为空，后续状态判断失效。
4. 无错误捕获机制：原脚本未处理任何异常，一旦某步骤失败直接导致后续逻辑输出空或错误结果。

修复后的脚本 #

# 强制加载SqlServer模块，避免计划任务环境未加载
Import-Module SqlServer -ErrorAction Stop

# 明确当前服务器主机名，避免$hostname未定义
$hostname = $env:COMPUTERNAME
$svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $hostname
$svr.ConnectionContext.StatementTimeout = 0

# 初始化变量，防止循环后变量未定义
$agName = $null
$primary = $null
$witnessFile = $null

foreach ($availabilityGroup in $svr.AvailabilityGroups) {
    $agName = $availabilityGroup.Name
    $primary = $availabilityGroup.PrimaryReplicaServerName

    # 捕获集群资源获取失败的异常
    try {
        $witnessFile = Get-ClusterResource -Name "File Share Witness" -ErrorAction Stop | Select-Object *
    } catch {
        Write-Output "获取文件共享见证资源失败: $_"
        $witnessFile = $null
    }

    Write-Output "AG Name: $agName"
    Write-Output "Primary Node: $primary"
}

# 校验AG名称和主节点有效性，避免后续逻辑空指针
if (-not $agName -or -not $primary) {
    Write-Output "未找到有效的可用性组或主节点"
    exit 1
}

# 测试AG健康，处理路径访问异常
$agPath = "SQLSERVER:\SQL\$primary\DEFAULT\AvailabilityGroups\$agName"
try {
    $agHealth = Test-SqlAvailabilityGroup -Path $agPath -ErrorAction Stop
} catch {
    Write-Output "测试AG健康状态失败: $_"
    $agHealth = $null
}

# 检查集群节点状态
$clusterNodes = Get-ClusterNode -ErrorAction SilentlyContinue
$clusterState = "HEALTHY"
if ($clusterNodes) {
    foreach ($node in $clusterNodes) {
        if ($node.State -ne "Up") {
            $clusterState = "UNHEALTHY"
            break
        }
    }
} else {
    $clusterState = "UNHEALTHY"
    Write-Output "无法获取集群节点信息"
}

# 判定AG状态
$agStatus = "UNHEALTHY"
if ($agHealth -and $agHealth.HealthState -eq "Healthy") {
    $agStatus = "HEALTHY"
}

# 判定见证文件状态
$witnessFileStatus = "UNHEALTHY"
if ($witnessFile -and $witnessFile.State -eq "Online") {
    $witnessFileStatus = "HEALTHY"
}

Write-Output "AG Status: $agStatus"
Write-Output "Cluster State: $clusterState"
Write-Output "Witness File Status: $witnessFileStatus"

计划任务额外配置建议 #

1. 权限配置：确保计划任务运行账号同时具备：
   • 本地服务器管理员权限
   • 主备节点SQL Server的AG监控权限（需添加对应登录账号）
   • 集群资源的读取权限
2. 执行策略：计划任务的PowerShell命令行添加-ExecutionPolicy Bypass，避免脚本执行被阻止，示例：

   powershell.exe -ExecutionPolicy Bypass -File "C:\Scripts\Check-AGHealth.ps1"
   

3. 日志输出：配置计划任务输出到日志文件，方便排查错误，示例：

   powershell.exe -ExecutionPolicy Bypass -File "C:\Scripts\Check-AGHealth.ps1" >> C:\Logs\AGHealthCheck.log 2>&1
   

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