SQL AlwayOn High Availability Default Port

Internal SQL DEV & QA environments may have SQL listening to the default port of 5022; thus, if there’s an application that seizes such port upon rebooting, then SQL AG is will fail. The endpoint configured on that port is the transport for data changes between primary and secondary.

The manual CLI method to discover services and listening ports would be

# Find the PID of service running on port 5022
netstat -aon | find /I "5022"

# Check the running tasks to translate pid to executable name
tasklist /fi "pid eq {pid_number}

PowerShell Scripting is a better way to fix this issue, and that is provided below:

$port="5022" #change this port number if instance port listening is modified
$desiredProcess="sqlservr"
$pids=""
$processNames=@()

<# Old Method
$records=(netstat -aon | findstr :$port)
foreach ($record in $records) {
    #$record.line.split(" ",[System.StringSplitOptions]::RemoveEmptyEntries)
    $pidIndex=$record.LastIndexOf(' ')
    $pidNumber=$record.substring($pidIndex, $record.length-$pidIndex)
    $pids+=$pidNumber
    $processNames+=,@(Get-Process -Id $pidNumber).ProcessName
}
#>

$pids=(Get-NetTCPConnection -LocalPort $port).OwningProcess
$pids | foreach{
    $processNames+=,@(Get-Process -Id $_).ProcessName
    }

if ($processNames | ? {$_ -like $desiredProcess}){
    "The desired program $desiredProcess is currently already running on port $port."
} else {
    "The desired program $desiredProcess is currently NOT running on port $port"
    
    if ($pids) {
        $pids | foreach {
                Stop-Process -Id $_ -Force  
        }
    }
    "Now starting desired program $desiredProcess..."
    Restart-Service MSSQLSERVER -force
    
    }

Leave a Reply

Your email address will not be published. Required fields are marked *