Hello Ibendlin - thanks for getting back to me, I appreciate it. I have worked out what the issue is and you are on the right track with your questions. Of course I didn't mention the thing that turns out is causing the problem! The supplier of the platform upon which the SQL Server is hosted required us to install a ZeroTier peer-to-peer VPN connection between the two machines and when running on Windows, ZeroTier is known to periodically drop the connection. In order to resolve this I have done two things as suggested on the ZeroTier community forum - i) I have setup a scheduled task to restart the ZeroTier service every 15 minutes, and ii) setup another scheduled task which pings the SQL Server every 5 seconds, just to keep the connection open. This appears to have worked! Thanks again for getting in touch. PS - I know from experience when searching for a solution that it is useful to have example scripts, so here's the Powershell script I'm using to ping the SQL Server - it includes useful logging which I used to check if ZeroTier is dropping the connection for a few seconds every 15 minutes as it restarts - which it is.
function pingtest{
# Set the name and the location of the log file
$loglocation = "[path to log file goes here]\pinglog.txt"
while($true){
# Set the name or IP of the server you want to ping
$servername = "[Server URL or IP address goes here]"
$test = Test-Connection -computername $servername -Count 1 -Quiet
$timestamp = Get-date
if ($test -eq 'true'){
Out-File -Append -InputObject "$timestamp - success" $loglocation
Write-Verbose "$timestamp - success" -Verbose
}else{
Out-File -Append -InputObject "$timestamp - fail" $loglocation
Write-Verbose "$timestamp - failure" -Verbose
}
# Set the interval in seconds to wait until the script runs again
start-sleep -s 5
}}
Clear-Host
pingtest
