dbatools icon indicating copy to clipboard operation
dbatools copied to clipboard
verified publisher icon dataplat

Get-DBAStartupParameter against an FCI randomly returns null results or arrays of arbitrary length

Open hotsauceror opened this issue 1 year ago • 4 comments

Verified issue does not already exist?

No, I did not search

What error did you receive?

$ get-dbastartupparameter -sqlinstance VNN.DOMAIN\mssqlserver -credential $t1

ComputerName         : VNN.DOMAIN
InstanceName         :
SqlInstance          :
MasterData           : f:\sql\bin\mssql\MSSQL15.MSSQLSERVER\MSSQL\Data\master.mdf
MasterLog            : f:\sql\bin\mssql\MSSQL15.MSSQLSERVER\MSSQL\Data\\mastlog.ldf
ErrorLog             : f:\sql\bin\mssql\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG
TraceFlags           : {4618, 4610, 9567}
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      : -df:\sql\bin\mssql\MSSQL15.MSSQLSERVER\MSSQL\Data\master.mdf;-ef:\sql\bin\mssql\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lf:\sql\bin\mssql\MSSQL15.MSSQLSERVER\MSSQL\Data\\mastlog.ldf;-T4618;-T4610;-T9567

$ get-dbastartupparameter -sqlinstance VNN.DOMAIN\mssqlserver -credential $t1


ComputerName         : VNN.DOMAIN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN.DOMAIN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN.DOMAIN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN.DOMAIN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN.DOMAIN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :



$ get-dbastartupparameter -sqlinstance VNN -credential $t1


ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :



get-dbastartupparameter -sqlinstance VNN -credential $t1                                                                                                                                                             

ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :

ComputerName         : VNN
InstanceName         :
SqlInstance          :
MasterData           : {}
MasterLog            : {}
ErrorLog             : {}
TraceFlags           : None
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      :



$ get-dbastartupparameter -sqlinstance STANDALONE -credential $t1                                                                                                                                                             

ComputerName         : STANDALONE
InstanceName         :
SqlInstance          :
MasterData           : f:\sql\sys\mssql\master.mdf
MasterLog            : f:\sql\sys\mssql\mastlog.ldf
ErrorLog             : f:\sql\bin\mssql\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG
TraceFlags           : {4610, 4618, 9567}
DebugFlags           : None
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      : -df:\sql\sys\mssql\master.mdf;-ef:\sql\bin\mssql\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lf:\sql\sys\mssql\mastlog.ldf;-T4610;-T4618;-T9567

Steps to Reproduce

get-dbastartupparameter -sqlinstance $VNN -credential $CRED

or

$result = get-dbastartupparmaeter -sqlinstance $VNN -credential $CRED 
# please include variable values (redacted or fake if needed) for reference

Please confirm that you are running the most recent version of dbatools

2.1.14

Other details or mentions

This behavior only happens with FCIs.

I am running this against a two-node multi-subnet FCI. I have confirmed that the FCI is online and in good state. I'm pulling the master data and log paths for some code that moves the system dbs. The script that performs the work randomly fails with "cannot call a method on a null object" and in rarer cases "there is no TraceFlags property on the object".

I ran the same code successfully against a half-dozen standalone instances last week and all returned the expected result set, every time.

The FCI behavior is unpredictable, and varies inconsistently with whether I pass the 'short' name, the FQDN, etc. Sometimes it returns an array where the first object is null and the rest are correct; sometimes it returns an array of three objects, sometimes it returns an array of up to eight objects, and sometimes it returns a single object. I assume it is iterating over multiple variants of the FCI name and over each cluster node, but I cannot put any rhyme or reason to it. Nor can I figure out why sometimes it returns exactly what it ought, and then fails several times in a row, with different results.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

5.1.19041.4780

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.8.9261.0

hotsauceror avatar Sep 10 '24 00:09 hotsauceror

Sorry. I did search for existing errors, and the closest I could find was #7035. That didn't seem related at all.

hotsauceror avatar Sep 10 '24 00:09 hotsauceror

Probably related: I find that Set-DbaStartupParameter only behaves itself if I pass in the name of the named instances. If I have a computer called "foo" and the only instance on it is called "Production", then -sqlinstance foo will return the same issues as what you see but -sqlinstance foo\Production works. This happens with both AGs and standalone instances, but I find the AGs inconsistent as you have.

ReeceGoding avatar Jun 20 '25 21:06 ReeceGoding

@claude can you ultrathink about this problem and offer suggestions? refer to both dbatools docs and microsoft docs

potatoqualitee avatar Nov 28 '25 16:11 potatoqualitee

Claude finished @potatoqualitee's task —— View job

Analyzing Get-DbaStartupParameter FCI Issue

I've completed a deep analysis of the issue. Here are my findings:

Tasks

  • [x] Read and understand Get-DbaStartupParameter implementation
  • [x] Identify how computer name resolution works for FCIs
  • [x] Analyze registry access patterns in clustered environments
  • [x] Research FCI-specific behaviors and startup parameter storage
  • [x] Provide detailed analysis of root causes
  • [x] Suggest concrete solutions

Root Cause Analysis

After examining the code in Get-DbaStartupParameter.ps1, Invoke-ManagedComputerCommand.ps1, Resolve-DbaNetworkName.ps1, and related functions, I've identified several interconnected issues specific to Failover Cluster Instances (FCIs):

Issue 1: Multi-Subnet IP Resolution Race Condition

Location: /home/runner/work/dbatools/dbatools/public/Resolve-DbaNetworkName.ps1:193-201

When Resolve-DbaNetworkName resolves an FCI VNN (Virtual Network Name):

  1. DNS returns multiple IP addresses (one per subnet in a multi-subnet FCI)
  2. The function pings all IPs with a 1-second timeout
  3. It uses the first IP to respond
foreach ($ip in $ipaddresses) {
    $reply = $ping.Send($ip, $timeout)
    if ($reply.Status -eq 'Success') {
        $ipaddress = $ip.IPAddressToString
        break
    }
}

Problem: In a multi-subnet FCI with network latency variance, different IPs may respond first on different runs, leading to:

  • Connection attempts to different cluster nodes
  • Different WMI query results depending on which node responds
  • Inconsistent results across sequential calls

Issue 2: WMI Service Query Ambiguity

Location: /home/runner/work/dbatools/dbatools/public/Get-DbaStartupParameter.ps1:81

The WMI query filters by display name:

$wmisvc = $wmi.Services | Where-Object DisplayName -eq $displayName

Problem: In certain FCI configurations, especially when querying physical cluster nodes directly:

  1. The ManagedComputer WMI object may enumerate services from multiple cluster nodes
  2. Multiple services with the same display name could exist (one per node)
  3. The Where-Object filter could return an array of services instead of a single service
  4. When $wmisvc is an array and you access $wmisvc.StartupParameters, PowerShell returns an array of all StartupParameters from all services

Issue 3: Instance Name Resolution for Default Instances

Location: /home/runner/work/dbatools/dbatools/public/Get-DbaStartupParameter.ps1:66-73

$computerName = $instance.ComputerName
$instanceName = $instance.InstanceName
# ...
if ($instanceName.Length -eq 0) { $instanceName = "MSSQLSERVER" }

Problem: When you specify just the VNN without an instance name:

  • VNN → Gets parsed as computer name only, instance name is empty → defaults to "MSSQLSERVER"
  • VNN.DOMAIN → Same issue
  • VNN\MSSQLSERVER → Correctly parsed

This explains @ReeceGoding's observation: "if I have a computer called 'foo' and the only instance on it is called 'Production', then -sqlinstance foo will return the same issues but -sqlinstance foo\Production works."

Issue 4: The Empty Results ({} instead of paths)

The {} output for MasterData, MasterLog, and ErrorLog indicates an empty array is being returned instead of a string. This happens when:

  1. $wmisvc.StartupParameters returns $null or empty (service not found or inaccessible)
  2. $params = $wmisvc.StartupParameters -split ';' results in an empty array
  3. $params | Where-Object { $_.StartsWith('-d') } returns an empty array @()
  4. The PSCustomObject displays this as {}

Why This Only Affects FCIs

Standalone instances:

  • Single IP address → Deterministic name resolution
  • Single node → WMI always queries the correct node
  • Service enumeration is straightforward

FCIs:

  • Multiple IPs → Non-deterministic ping responses
  • Multiple cluster nodes → WMI connection could target any node
  • Virtual resources → Service visibility depends on which node you query
  • Cluster-aware services → May appear differently in WMI depending on query source

Microsoft Documentation Context

Per Microsoft's FCI documentation:

  1. FCI Registry Storage: Startup parameters for FCIs are stored in the registry of each cluster node, but the active configuration is only on the node currently hosting the instance.

  2. WMI Access: When you connect to an FCI via WMI:

    • If you connect to the VNN, you should reach the active node
    • If you connect directly to a physical node name, you get that node's local view
    • The Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer class may behave unexpectedly with cluster resources depending on how the IP is resolved

  3. Service Visibility: SQL Server services in an FCI are cluster resources. When querying WMI:

    • Querying the active node shows the service as running
    • Querying an inactive node may show the service as stopped or not present
    • Some cluster configurations expose services on all nodes

Recommended Solutions

Solution 1: Require Explicit Instance Names for FCIs (Quick Fix)

Modify the parameter validation to require explicit instance names when the detected configuration suggests an FCI:

# After line 70
if (-not $instance.InstanceName) {
    # Detect if this is an FCI by checking cluster configuration
    # If FCI detected, throw meaningful error
    Stop-Function -Message "For Failover Cluster Instances, you must specify the full instance name (e.g., 'VNN\InstanceName' instead of just 'VNN'). This ensures accurate startup parameter retrieval." -Continue
}

Solution 2: Use Cluster-Aware Name Resolution (Recommended)

Enhance the name resolution to detect and properly handle FCIs:

  1. Add FCI detection before WMI connection
  2. For FCIs, explicitly query the cluster to get the active node
  3. Connect to the active node directly instead of relying on ping race conditions
  4. Use the SQL Server instance name from cluster configuration

This would require integration with Windows Cluster PowerShell cmdlets:

# Pseudo-code
$clusterGroup = Get-ClusterGroup -Name "SQL Server ($instanceName)"
$activeNode = $clusterGroup.OwnerNode.Name
# Connect to $activeNode directly

Solution 3: Ensure Single Service Result (Defensive)

Add validation to ensure WMI query returns exactly one service:

# Replace line 81 in Get-DbaStartupParameter.ps1
$wmisvc = $wmi.Services | Where-Object DisplayName -eq $displayName

# Add validation
if (-not $wmisvc) {
    throw "SQL Server service '$displayName' not found on $computerName"
}
if ($wmisvc -is [array]) {
    throw "Multiple SQL Server services with name '$displayName' found on $computerName. This may indicate a cluster configuration issue. Please specify the full instance name (e.g., 'ComputerName\InstanceName')."
}

Solution 4: Use -Turbo for Name Resolution (Workaround)

Modify line 70 to use -Turbo mode, which skips the ping-based IP selection and uses DNS-only resolution:

# Current: line 70
$computerName = (Resolve-DbaNetworkName -ComputerName $computerName).FullComputerName

# Modified
$resolved = Resolve-DbaNetworkName -ComputerName $computerName -Turbo
$computerName = $resolved.FullComputerName

This reduces variability but doesn't solve the fundamental FCI handling issues.

Immediate Workarounds for Users

Until a fix is implemented, users experiencing this issue should:

  1. Always specify the full instance name: Use VNN\InstanceName instead of just VNN
  2. Connect directly to the active node: Determine which cluster node is active and connect to it directly: PhysicalNode\InstanceName
  3. Use a retry loop: Wrap calls in a retry with validation: 
    $result = $null
$attempts = 0
while ($attempts -lt 5 -and (-not $result -or -not $result.MasterData)) {
    $result = Get-DbaStartupParameter -SqlInstance $VNN\InstanceName -Credential $cred
    if ($result.MasterData -and $result.MasterData -notmatch '^\{.*\}$') {
        break
    }
    Start-Sleep -Milliseconds 500
    $attempts++
}

Recommended Implementation Priority

  1. Solution 3 (Defensive validation) - Quick win, prevents most error scenarios
  2. Solution 1 (Require explicit names) - Provides clear guidance to users
  3. Solution 2 (Cluster-aware resolution) - Comprehensive fix but requires more development
  4. Solution 4 (Turbo mode) - Minor improvement, worth trying

Would you like me to implement any of these solutions? I recommend starting with Solutions 1 and 3 combined, as they provide immediate improvement with minimal risk.

claude[bot] avatar Nov 28 '25 16:11 claude[bot]