Test-EnduranceSqlConnection.ps1

# Example usage:
# . C:\temp\Test-EnduranceSqlConnection.ps1
# Test-EnduranceSqlConnection '192.168.253.127\Corflow' -Interval ([timespan]::FromSeconds(1))

function Test-Key {
    [CmdletBinding()]
    param (
        [System.ConsoleKey]$Key
    )

    while ([console]::KeyAvailable) {
        $k = [console]::ReadKey()
        if (-not $Key -or ($k.Key -eq $Key)) {
            $true
        }
    }

    $false
}

function Wait-Key {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [timespan]$Wait,

        [System.ConsoleKey]$Key
    )

    $sw = New-Object 'System.Diagnostics.Stopwatch'
    $sw.Start()
    do {
        $waitTime = ($Wait - $sw.Elapsed).TotalMilliseconds
        if ($waitTime -gt 250) {
            $waitTime = 250
        }

        Start-Sleep -Milliseconds $waitTime

        if (Test-Key -Key $Key) {
            $true
            return
        }
    } while ($sw.Elapsed -lt $Wait)

    $false
}

function Test-EnduranceSqlConnection {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]
        $ServerInstance,

        [int]
        $ConnectRetryCount = 0,

        [timespan]
        $Interval = [timespan]::FromSeconds(1)
    )
    
    begin {
        Import-Module Organimmo.Base

        $csb = New-Object 'System.Data.SqlClient.SqlConnectionStringBuilder'
        $csb['Data Source'] = $ServerInstance;
        $csb['ConnectRetryCount'] = 0
        $cred = Get-OrganimmoCredential "SqlServer-$ServerInstance"
        $cred.Password.MakeReadOnly()
        $sqlcred = New-Object 'System.Data.SqlClient.SqlCredential' -ArgumentList $cred.Username, $cred.Password
        $conn = New-Object 'System.Data.SqlClient.SqlConnection' -ArgumentList $csb.ToString(), $sqlcred
        $conn.Open()
        $sw = New-Object 'System.Diagnostics.Stopwatch'
        $sw.Start()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = 'SELECT 1'
    }
    
    process {
        $failureCount = 0
        $lastFailure = $null
        while ($true) {
            $failureException
            try {
                $result = $cmd.ExecuteScalar()
            } catch {
                $exception = $_
                ++$failureCount

                $datetime = Get-Date

                if ($lastFailure) {
                    $failureDuration = $datetime - $lastFailure
                } else {
                    $failureDuration = $null
                }
                [PSCustomObject]@{
                    DateTime = $datetime
                    LastFailure = $lastFailure
                    FailureDuration = $failureDuration
                    ConnectDuration = $sw.Elapsed
                    FailureCount = $failureCount
                    Exception = $exception.Exception.Message
                }

                $lastFailure = $datetime

                # Reopen connection
                if ($conn.State -ne 'Open') {
                    $conn.Open()
                    $sw.Restart()
                }

                $sw.Restart()
            }
            Write-Verbose "$(Get-Date) $result $($conn.State) $($sw.Elapsed) $failureCount"
            if (Wait-Key 'x' -Wait $Interval) {
                break
            }
        }
    }
    
    end {
        Write-Verbose "Closing connection"
        $conn.Close()        
    }
}