SQL Server is down that handles ASP.NET Session St

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SQL Server is down that handles ASP.NET Session St

Author

Topic

bizwax
Starting Member

5 Posts

Posted - 2006-09-26 : 13:25:05

How do I gracefully handle when an SQL Server is down that handles ASP.NET Session State?

Scenario:
Hosted ASP.NET 2.0 website.
Website is load balanced on 2 web servers.
Website is using a single Sql Server to maintain session state.

Problem:
The Sql Server maintaining our session state was temporarily brought down for 2 hours by my web host. Unfortunately this also brought my entire website down, too, with many unhandled errors encountered. I have error handling in place in all of the web pages, including global error handling within the global.asax file and a custom error page redirect. But the error handling and custom error page was bypassed when the session database server was inaccessible, resulting in unexpected results.

I am able to recreate this problem in a test environment by simply putting a bogus server name in the connection string to the session database server. I have not yet found a way to handle the error. The unhandled error generated is:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

How can my ASP.NET website detect that our session database server is down/inaccessible and gracefully handle the error?

Thanks,
Mark

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-26 : 14:27:21

Would advice you get a new host.

Also provider ? named pipes ? Its better to use TCP/IP accross a network

bizwax
Starting Member

5 Posts

Posted - 2006-09-26 : 17:42:09

quote:
Originally posted by afrika

Would advice you get a new host.

Also provider ? named pipes ? Its better to use TCP/IP accross a network

True, the web host is at fault for the database server being down. And I also agree that TCPIP is a better way to connect.

Aside from these items, can you suggest a way to gracefully handle a downed/inaccessible SQL Server session state server from within an ASP.NET website?

Thanks,
Mark

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-26 : 18:32:09

>>can you suggest a way to gracefully handle a downed/inaccessible SQL Server session state server from within an ASP.NET website?

use your backups, and restore on a shared db

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-26 : 19:03:23

quote:
Originally posted by afrika

>>can you suggest a way to gracefully handle a downed/inaccessible SQL Server session state server from within an ASP.NET website?

use your backups, and restore on a shared db

How does that gracefully handle it?

Tara Kizer

dfiala
Posting Yak Master

116 Posts

Posted - 2006-09-26 : 20:26:59

The problem is that this happening higher up in the application pipeline than producing a page. the application cannot even create a session when the SQLServer is down. Off the top of my head the way to handle this most easily might be in IIS and a custom HTML page. You might also need to fidget with ISASPI (if you're in 2.0) to make sure that the ASP.NET process is not responsible for the HTML pages.

All of this could be a problem if you don't have full access to your server.

It's an interesitng problem, I'll take a look at more tomorrow.

Dean Fiala
Very Practical Software, Inc
Now with Blogging...
http://www.vpsw.com/blogbaby
Microsoft MVP

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-27 : 00:34:04

Are you using Microsoft's methods for storing session data in SQL Server (http://support.microsoft.com/kb/317604/), or are you rolling your own?

could you put some code in place that checks for your database prior to attempting to establish a session? If the initial check for the DB fails, then you could display a sorry page. I guess I don't understand why you can't handle a database connection error in your code.

-ec

bizwax
Starting Member

5 Posts

Posted - 2006-09-27 : 12:36:03

quote:
Originally posted by eyechart

Are you using Microsoft's methods for storing session data in SQL Server (http://support.microsoft.com/kb/317604/), or are you rolling your own?

could you put some code in place that checks for your database prior to attempting to establish a session? If the initial check for the DB fails, then you could display a sorry page. I guess I don't understand why you can't handle a database connection error in your code.

Thanks for the reply, EC.

I am not rolling my own. I am using the Microsoft method of storing session state within the SQL Server.

To better understand my problem, try putting the following line in the system.web section of a web.config file on any ASP.NET website. Then see if you can find out how to handle the generated error:

<sessionState mode="SQLServer" sqlConnectionString="data source=BOGUS;user id=BOGUS;password=BOGUS;" cookieless="false" timeout="20"/>

(i.e. "BOGUS" is a non-existent server on my network. If you have a server on your network named BOGUS, then change BOGUS to a different non-existent hostname.)

I, too, don't fully understand why I can't handle this error from within ASP.NET, but that appears to be the case.

Thanks,
Mark

bizwax
Starting Member

5 Posts

Posted - 2006-09-27 : 12:49:23

quote:
Originally posted by dfiala

The problem is that this happening higher up in the application pipeline than producing a page.

Thanks for the reply, Dean.

This error seems to be generated from the ASP.NET engine. Can it still be handled by IIS?

The error is also recorded as a Warning in the Application Event Log int the Web Event category. I find it odd that this is a "Warning", yet halts the entire website with unhandled errors.

I created a test web app that recreates the inaccessible session state server, and the following is saved in the event log:

Event Type: Warning
Event Source: ASP.NET 2.0.50727.0
Event Category: Web Event
Event ID: 1309
Date: 9/27/2006
Time: 10:48:33 AM
User: N/A
Computer: WebServer
Description:
Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 9/27/2006 10:48:33 AM
Event time (UTC): 9/27/2006 3:48:33 PM
Event ID: b4443ce555ba4823b7cf534ef476683f
Event sequence: 4
Event occurrence: 1
Event detail code: 0

Application information:
Application domain: dfa84f73-1-128038456900573443
Trust level: Full
Application Virtual Path: /SqlSessionState
Application Path: C:\SwDev\RnD\SqlSessionState\
Machine name: WebServer

Process information:
Process ID: 3212
Process name: WebDev.WebServer.EXE
Account name: Domain\User

Exception information:
Exception type: SqlException
Exception message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Request information:
Request URL: http://localhost:1574/SqlSessionState/Default.aspx
Request path: /SqlSessionState/Default.aspx
User host address: 127.0.0.1
User: Domain\User
Is authenticated: True
Authentication Type: NTLM
Thread account name: Domain\User

Thread information:
Thread ID: 4
Thread account name: Domain\User
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)

Thanks,
Mark

dfiala
Posting Yak Master

116 Posts

Posted - 2006-09-27 : 13:10:32

>>This error seems to be generated from the ASP.NET engine. Can it still be handled by IIS?
That's why it likely has to be handled by IIS, or at least somewhere higher up than the application.

http://msdn2.microsoft.com/en-us/library/ms178473.aspx shows the application life-cycle.

I haven't been been able to locate any good info on how to hook in higher up, which is why I suggested IIS.

Dean Fiala
Very Practical Software, Inc
Now with Blogging...
http://www.vpsw.com/blogbaby
Microsoft MVP

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-27 : 13:28:55

quote:
Originally posted by bizwax

quote:
Originally posted by eyechart

Are you using Microsoft's methods for storing session data in SQL Server (http://support.microsoft.com/kb/317604/), or are you rolling your own?

could you put some code in place that checks for your database prior to attempting to establish a session? If the initial check for the DB fails, then you could display a sorry page. I guess I don't understand why you can't handle a database connection error in your code.

Thanks for the reply, EC.

I am not rolling my own. I am using the Microsoft method of storing session state within the SQL Server.

To better understand my problem, try putting the following line in the system.web section of a web.config file on any ASP.NET website. Then see if you can find out how to handle the generated error:

<sessionState mode="SQLServer" sqlConnectionString="data source=BOGUS;user id=BOGUS;password=BOGUS;" cookieless="false" timeout="20"/>

(i.e. "BOGUS" is a non-existent server on my network. If you have a server on your network named BOGUS, then change BOGUS to a different non-existent hostname.)

I, too, don't fully understand why I can't handle this error from within ASP.NET, but that appears to be the case.

Thanks,
Mark

I guess my response read the wrong way. I am actually surprised that there is no way to handle this exeception - seems like an oversight by microsoft. Do you have to handle session in the SQL Server, or can you use the other methods microsoft provides (inproc or stateserver)?

This kind of thing is a little outside my area of expertise (can you tell?), but my web dev coworkers tell me that they manually put up a sorry page when a webapp is down for a prolonged period of time. Either they have no way of handling this type of problem or it just hasn't been worth the time to figure out.

-ec

bizwax
Starting Member

5 Posts

Posted - 2006-09-28 : 13:27:19

Thanks all for the brainstorming. I found a fix.

While I was already using the Application_Error event in global.asax to process unhandled errors, I had to handle the session state server outage a little differently. In simplified form, here is what I had to implement:

Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)
If Server.GetLastError.Message.ToString.Contains("Unable to connect to SQL Server session database") Then
' This is an unrecoverable error.
' You cannot redirect to another page from here or you will simply generate
' another application error event and be caught in an endless loop.
' You must generate your error response to the client here without redirection.
Response.Clear()
Response.Write("Unrecoverable error encountered.")
Response.Flush()
Server.ClearError()
Else
' recoverable application error processing goes here
End If
End Sub

Let me know if you need any addition info on this topic.

Thanks,
Mark

dfiala
Posting Yak Master

116 Posts

Posted - 2006-09-28 : 13:58:36

Nicely figured out.

Dean Fiala
Very Practical Software, Inc
Now with Blogging...
http://www.vpsw.com/blogbaby
Microsoft MVP

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-28 : 15:53:01

nice job, thanks for posting your solution. no doubt someone else will run into this issue..

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources