SQL Connection Lifetime

[Adrian Bezzina]

Hi,

I am asking a generic question about SQL server (or any type server
communication for that matter) and how one would keep the connection open
for the life time of the application, or should one close the connection for
each request (or set of related requests)?

Does SharePoint open / close the connection to the database for every
request?

Does Exchange open / close the connection to the database for every request?

BTW I define a request as more like a unit of work which could be made up of
multiple sql statements (in this instance. A unit of work is to be run in
the minimal amount of time possible.

I personally think that keeping a connection to a server open when an
application is IDLE is definately not on. Connection / Disconneciton also
helps free resources, and in the event of a network failure (or dropped
packets for that matter) then recovery is much 'cleaner' (e.g. in SQL server
i know of an application that connects when the user logs on and closes when
the user logs off and if a VPN link drops packets the application come up
with communication link failure.)

I am wondering on what is the best way to go with future projects that I
might work on.

Regards,
Adrian.

 

[Miha Markic [MVP C#]]

Hi Adrian,

Best practise is to use connection pool (by default) and keep connections
open just for the time you use them.
The conneciton pool will take of leaving n connections open (you can set
that n - depends on the provider).
This is also the best scenario for multithreaded applications