Steve,

I tend to open one connection at the beginning of my application and hold it open throughout. In SQL Server, there is overhead on the server for each open connection. There is also a limit on the number of connections that can be open at one time by all users. You can raise this limit, but raising the limit uses additional RAM, even when the connections aren't in use. The only times I would vary from this would be:

1. You are doing asynch. queries and want to be able to do a second query while one is in progress. If you try to do a query on a connection that's already processing a query, you'll get an error.

2. You only need to access the back-end occasionally. In this case I would open the connection as needed and close it when done. This would reduce overhead on the server.

Hope this helps,

Josh

>Can anyone advise me on the best way to manage connections. I have only one connection to my remote data source. Should I get a connection handle at the start of my app and keep it to use that one handle all the way through? Should a get a new connection handle every time I want to perform an operation and disconnect it as soon as I have finished? Should I have several connections on the go at once so that I have a few to choose from.
>
>I can't find any documentation or any articles to advise on this. Is there an overhead associated with a connection handle? Should I limit SQLConnects and SQLDisconnects or should I treat them like variables and keep them only as long as I need them?
>
>Thanks in advance for any advice offered.