Connection: how to survive server restart?

[Lucvdv]

Is there a way to determine if a server is still running, without having to
rely on exceptions thrown when a query is executed?


I'm trying to teach my VB app how to survive short server downtimes.
I've got one static (shared) connection object that's just sleeping most of
the time.

Once this connection has been established, almost everything is done using
new connections opened with code like this:

cn2 = New SqlClient.SqlConnection(cn1.ConnectionString)
cn2.Open

Start of the problem is that cn2.ConnectionState always returns Open, even
if the server was unavailable at the time of the "Open" call.

A subsequent attempt to run a query on the new connection results in error
11, "general network error".

So far it's no big problem, I can catch this exception and loop until the
operation succeeds. I created wrappers around the data access methods I'm
using (ExecuteReader, ExecuteScalar and ExecuteNonQuery) to handle it.

The important part is:

Public Function SqlExecReader(ByVal cmd As SqlCommand) As SqlDataReader
Try
SqlExecReader = cmd.ExecuteReader
Catch ex As SqlClient.SqlException
If ex.Number = 11 Then
Dim f As New frmWaitDatabase
f.Show()
Do
Try
Application.DoEvents()
System.Threading.Thread.Sleep(1000)
If cmd.Connection.State = ConnectionState.Closed Then _
cmd.Connection.Open()
SqlExecReader = cmd.ExecuteReader
Catch ex2 As SqlClient.SqlException
cmd.Connection.Close() ' initially not done here
....
Loop While SqlExecReader Is Nothing
f.Close
...

But after a couple of passes, I get another exception instead:

InvalidOperationException: "Timeout expired. The timeout period elapsed
prior to obtaining a connection from the pool. This may have occurred
because all pooled connections were in use and max pool size was
reached."

Initially the "...Connection.Close()" line wasn't there, because the
connection state _was_ "Closed" after the failure. Adding the explicit
Close() didn't stop this exception from occurring either.


It sounds dangerous to loop on this one too, because I might fail to detect
it if it occurs for another reason (I could end up in an endless loop if
there really are too many connections in use).

It doesn't seem normal either that ado.Net is using up connections until
the pool runs out, by just closing and reopening the same connection.

 

[William \(Bill\) Vaughn]

Not really. When you open a connection, the pooler opens it for you and
holds the connection open after it's closed so if the server goes down you
won't have any indication--no events are fired to notify you. When you try
to reopen, the pooler tries to access the (now broken) connection and fails
and returns the exception to your code. Your only option is to retry and
this might take several retries as all of the pooled connections are bad.
Once the pool is flushed, the next open will succeed (assuming the server is
back up). Until ADO 2.0, you'll have to handle these errors on your own.
It really does no good to poll anyway. It's like calling a theater to see if
there are any seats left for tonight's show. By the time you get there the
theater might have burned down--or just be sold out.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Lucvdv]

Not really. When you open a connection, the pooler opens it for you and
holds the connection open after it's closed so if the server goes down you
won't have any indication--

Thanks. It's about what I expected.