A TOUGH ONE - audit Login, Audit Logout connection

From: Phil Smith (phil.smith_at_informatics.co.uk)
Date: 04/28/04 

Date: 28 Apr 2004 05:25:45 -0700

Hi all:

Hope you can shed some light on this. we have a Vb6 app connecting to
a slq server database using ado 2.6. when each client is started they
initialize a connection object which is kept open for the life cycle
of the client (i know this not recommended but it is an inherited
problem).

Our site was reporting errors which were attributed to loss of client
connection to the server. As we still haven't found a clear way of
alerting the client when it has loosed its connection to the server
((1)if anyone has a suggestion let me know)- the Recordset.state
property and Recordset events are useless for this.

Our solution for this was - when ever we pass the connection object
to a recorded, internal to the connection object we test the
connection to the server by executing a "Select @@Version" on the
server using a prepared command object. if this test fails then we try
re-establish the connection , which is looped on the
connection.timeout. so the user has the choice to reconnect or exit
the app.

now running a trace using profiler with audit
login/logout,ClentProcessID, spid and the other main event, when the
app starts up it gets its unique ClentProcessID & spid from sql, so we
can identify the app and its connection (audit login) at intensive
parts of the code for some unknown reason an additional spid connects
for the same ClentProcessID and this can carry on for up to 5 layers
so what the trace looks like is....

ClientProccessID|SPID|EventClass|
---------------------------------
444 | 51 |SQL:Batchcomplete
444 | 51 |SQL:Batchcomplete
444 | 51 |SQL:Batchcomplete
444 | 52 |Audit Login
444 | 52 |SQL:Batchcomplete
444 | 52 |Audit Out
444 | 52 |Audit Login
444 | 52 |SQL:Batchcomplete
444 | 53 |Audit Login
444 | 53 |SQL:Batchcomplete
444 | 53 |Audit Out
444 | 52 |Audit Out
444 | 51 |SQL:Batchcomplete
444 | 51 |SQL:Batchcomplete
444 | 51 |SQL:Batchcomplete

you can see these are nested and can be upto 5 levels deep. (2) we
think what is happening is that ADO is just creating additional
connections at times of have traffic without us knowing which may be
contributing to our loss off connection, is there any way to further
track this or clarify this.

we don't think it is a connection pooling problem as this has been
switch off, and we have taken into account spid's for other apps
connected to the server.

I hope you have enough info. so if anyone has a solution to (1) or can
confirm (2) it would be much appreciated.

Gratefully – a puzzled one

Relevant Pages

  • Re: .Net Scalability problem
    ... LoadRunner will peak out a server with a few virtual users. ... To get an idea of load, ... Fire off the test client and watch the number of ... > So I think that the MTC generate concurrent connection and per ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connection lost at same time every hour (sometimes)
    ... After making the two following alterations on the server the problem seems ... After analyze your ipconfig on SBS and client, ... Then, other connection is good, ...
    (microsoft.public.windows.server.sbs)
  • Re: server disconnection - very often
    ... Reason of permanent popups is VMware server aplication on clients. ... Run CEICW to configure the network of SBS: ... Two network adapters - manual router connection to broadband ... Uninstall VMware on client. ...
    (microsoft.public.windows.server.sbs)
  • Re: Lan setup 2 nic
    ... The external nic only has TCP/IP enabled. ... Ipconfig of the server is looking good, but the client is still missing the ... > connection so we have a 2 nic with router setup now. ...
    (microsoft.public.windows.server.sbs)
  • Re: Regular disconnections from remote web workplace
    ... I can connect to office server and all office clients from home at all times ... be physically working right up until the connection is lost. ... If I enter http://companyip from a client I receive the login screen for the ... Click Services tab and select Hide All Microsoft Services and Disable ...
    (microsoft.public.windows.server.sbs)