database connection loss

  • Thread starter Thread starter AMDRIT
  • Start date Start date
A

AMDRIT

There are a couple of things you can do outside of ADO to test that the
serveris available and listening on the desired port.

Netstat will tell you if the server is:

1. Available and online
2. Reporting that it is listening on the desired DB TCP/IP ports (i.e.,
SQL server is by default port 1433)

Telnet will tell you:

1. The server is available and online
2. Connection to the desired port is possible

Niether of these solutions will tell you that the server is bottlenecked,
experiencing long queues, in single use mode, or in a unstable state.

In your situation, I would plan worse case and code in that manner.

Make use of transactions, and minimize connectivitiy when possible.
Devise failover solutions that allow for resuming once a failed thread
restarts.
Log exceptions for later review
Request SLA from Network Support along with best times for processing


Prepare data prior to committing it
Open Connecion
store prepared data locally with a processed flag
create a transaction
commit data
complete transaction
Close connection
mark prepared data as processed
remove local cache

:Failover

Open locally cached prepared data
Open connection
create transaction
commit data
complete transaction
Close Connection
mark prepared data as processed
remove local cache
 
Hallo!

I'm working with C# .NET 2.0, implementing Client/Server Applications which
are connecting via Network to SQL-Server or Oracle Databases.
To stay independent from the underlaying Database I use
System.Data.Common.DBConnection and .DBCommand.

How can I keep aware from connection losses (network not availeable,
db-server not available...)?
Are there any strategies to detect this broken connections, and how can I
implement reconnecting to the database?

The only solution I figured out, is creating a thread which frequntly
performs a SELECT 1 (MSSQL) or SELECT 1 FROM DUAL (Oracle) within a
try-catch block. When an exception occurs I have to send my other
worker-threads to sleep, till I'm able to reopen the database connection and
my Select-Statement works again. Then I have to trigger the other threads to
reopen their connections and continue working (every thread gots his own
DBConnection).

For me this seems a little bit to complicated and insecure.
It also could happen that one of the worker threads gots a timeout due
locking or delayed server responstime, and will loose his Connection in fact
of this exception. Such, or similar problems I can not get handled with my
solution.

Are there any suggestions, patterns or automathisms with ADO 2.0?

with friendly regards

Martin
 
Thanx for your immediate Reply!

For me it's rather impossible to Close and Open the Connection at any
transaction. I have to process 5 transactions per second so it seems to
decrease my performance despite connection pooling.

What I wanted to know is, how to implement a simple and secure reconnect
policy in my program code.
It doesn't matter for me that there will be a data-loss on connection abort,
important is that after reestablishing and fixing the network or database
link physically (it's not my job) my program recognizes as soon as possible
the availability and restarts with his work.
That means removing the broken connection object (Close, Dispose I don't
know exactly) and recreating a new Connection for further transactions.
During the Connection isn't available for me my program should stand in
waiting position, ignoring all user events and testing the connection
periodically.

So the topics of relevance:

1.) detection of connection loss in my code (avoiding thread or program shut
down)
2.) cycling test of db availability
3.) reopen connections on availability

This points should work for several threads which are owning an connection
object for each of them.

with friendly regards

Martin
 
MS bugs: nowhere you are told how to work with connections. I guess the best is to
1. open connection
2. transaction
3. close connection

over and over again.

Regards Marcus

PS I have the same problem, open connections will be autoclosed and cannot be reopened (again MS bug).
 
Back
Top