How to force connection timeout in Powershell when failing to connect to SQL Server-CodePudding

I would like to create simple ping algorithm to continuously query remote SQL Server instance using Powershell. My goal is to detect small network outages, caused most likely by firewall rule changes.

while($true)
{
    $ClientDate = Get-Date
    $ServerDate = Invoke-Sqlcmd -Query "SELECT SYSDATETIME() AS [Time];" -ServerInstance "RemoteSQLInstance" -ConnectionTimeout 1

    "Client = "   $ClientDate   "| Server = "   $ServerDate.Time

    Start-Sleep -Seconds 1
}

Mentioned code works just fine when the instances is reachable, but when the instance becomes unreachable, it takes the Powershell a standard 30 seconds to output an error message (A network-related or instance-specific error occurred while establishing a connection to SQL Server). I was hoping that by introducing -ConnectionTimeout 1 parameter I can force the error message to appear right away (after 1 second) and not in default 30 seconds, but it doesn't change the default 30 seconds timeout. I guess this parameter works only if SQL Server instance is reached through network and SQL Server is failing to establish a handshake within given time.

How can I tell Powershell to not wait default 30 seconds when making remote connection to SQL Instance? Or any remote connections in general?

CodePudding user response：

In some instances, it does appear that the -ConnectionTimeout parameter is not honored by the Invoke-SqlCmd cmdlet.

In the past I've worked around this issue by rolling my own function that uses the