Handling database going offline for 1 minute to update in query

Question

A vendor provides a readonly MS SQL database that we interact with using PHP.

This database updates it's data at 630AM and 1230AM our time. This causes the database to be offline for less than one minute, but that's enough time for cronjobs that run to hit it, and occasionally for end users to hit the offline database.

The logs show the error is a connectivity one with:

Cannot open user default database. Login failed. (severity 11)
General SQL Server error: Check messages from the SQL Server (severity 11)
Login failed for user 'USER'. (severity 14) 
General SQL Server error: Check messages from the SQL Server (severity 14)
Unable to connect to server: SERVER

If the server goes down in the middle of an operation, rather than the connection to the database, the logs show these errors:

The SELECT permission was denied on the object 'TABLE', database 'DATABASE', schema 'dbo'. (severity 14)
General SQL Server error: Check messages from the SQL Server (severity 14)
Query failed

What's the best way to handle this? Queries and Connections are paired with an "or die" stopping processing. Would it be best to throw a sleep(60) to pause execution for a minute and retry the query/connection? Here's the current example. The connection is from a shared require_once include at the start of the page, so there usually is some other operations that occur before the query.

$mslink = mssql_connect('SERVER', 'USER', 'PASS');

if (!$mslink || !mssql_select_db('TABLE', $mslink)) {
    die('Unable to connect or select database!');
}

$strSQL = "SELECT X,Y,Z FROM TABLE WHERE FOO = 'BAR'";
$objQuery = mssql_query($strSQL) or die("Error Query [" . $strSQL . "]");

I've considered detecting the time and delaying it before the connection (which should mean the query would be okay), or letting it fail and then sleeping and trying again (which seems like a worse idea- since it'd lead to log entries).

Would this be a good place for try/catch instead of using the if true functionality? I've not done anything with try/catch before.

I'm hoping a solution with the connection is easiest as that would cover the codebase with one change versus doing it on every query. There is no framework or anything, this is straight PHP (and procedural, I know, please be gentle). The plan is to move from ext\mssql to PDO in the future sometime.

Answer 1

I came up with an answer I was content with. Knowing that the server is offline for up to a minute every six hours, I catch the failure on connection then sleep for a few seconds until a retry is made, with an upper cap of waiting 60 seconds. This has worked great and started to return better data on just how long the other server is unavailable during the update.

I also switched from MSSQL (deprecated in PHP7) to PDO and SQLSRV. This below code is for SQLSRV as it is the closest drop in replacement for my initial question.

This code is stored with the connection details so does not need to be with every query attempt, just in the main include when connecting.

$mslink = sqlsrv_connect($serverName, $connectionOptions);
if (!$mslink) {

$ms_sql_errors = sqlsrv_errors();

if ($ms_sql_errors[0]['SQLSTATE'] == 42000) { // This is the database update error
    error_log("Cannot open database at $serverName", 0);
} elseif ($ms_sql_errors[0]['SQLSTATE'] == "HYT00") {
    error_log("Timeout - is the address $serverName correct?", 0);
} elseif ($ms_sql_errors[0]['SQLSTATE'] == 28000) {
    error_log("Login Failed!", 0);
} elseif ($ms_sql_errors[0]['SQLSTATE'] == "08001") {
    error_log("Network error connecting to $serverName", 0);
} else {
    error_log($ms_sql_errors[0]['SQLSTATE'], 0);
}
while (!$mslink && $failcount < 6) {
    $failcount++;
    sleep(10); // Attempt to reconnect after $serverName disappears
    $mslink = sqlsrv_connect($serverName, $connectionOptions);
    if (!$mslink) {
        error_log("$serverName retry $failcount failed to connect after a 10 second sleep!, will wait 10 seconds and try again...", 0);
        if ($failcount >= 6) {
            error_log("$serverName connection FAILED AFTER $failcount 10 second sleeps, stopping", 0);
        }
    } else {
        error_log("DW connection worked after $failcount 10 second sleep(s)", 0);
    }
}
}

I hope this helps anyone else who doesn't want to do quite so much legwork to cope with a known and expected short blip of access to SQL Services.