This Question have no Text answers yet!

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.



Video about Handling database going offline for 1 minute to update in query