Detecting SQL Server offline in ASP.NET Application

I was recently given an assignment to create an aspx page that shows when backend SQL database is offline. There are two scenarios when a database is unreachable. First, the database server itself is down, .NET framework in this case throws a SQL exception with property "Number" set to 2,53 or –2. Second case, the server is reachable but it refuses to serve the request because of various reasons for example: server paused, login failed, network error and etc. This information is stored in a system table "sys.messages" in master database on SQL server.

In SQL server 2005/2008 under “master” db context run this query:

select * from sys.messages

gives you a list of messages that sql server supports. I wanted only the ones related to database connectivity issue. Here is a list of messages I hand picked for the assignment:

• 2,53: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
• -2: Client Timeout
• 4060: Cannot open database "%.*ls" requested by the login. The login failed.
• 17142: SQL Server service has been paused. No new connections will be allowed. To resume the service, use SQL Computer Manager or the Services application in Control Panel.*
• 18452: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.%.*ls
• 18456: Login failed for user
• 18312,18320,18330 : sql server service paused.
• 233: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Now I have the numbers I need to put them into use somewhere. The best spot seems to be Application_Error function in global.asax.

protected void Application_Error(object sender, EventArgs e)
{
    var exp = Server.GetLastError().GetBaseException();
    if (exp is System.Data.SqlClient.SqlException)
    {
        var sqlExp = exp as System.Data.SqlClient.SqlException;
        if (new[] { -2, 2, 53, 233, 4060, 17142, 18452, 18456, 18312, 18320, 18330 }.Contains(sqlExp.Number))
        {
            Server.ClearError();
            HttpContext.Current.Response.Redirect("~/Error/Offline.htm", true);
        }
    }
}

A last note here I used an .htm error page instead of aspx. In a lot of asp.net applications, there is database access in either Application_Start or Session_Start. If the error page was an aspx page, Application_Start or Session_Start will get called depending on the state of the application when users are being redirected and thus resulting in an infinitely loop.

Finally decided to start blogging.

That was lazy me...