I have deployed my application on Windows Azure and it uses a Sql Azure database. My application has it's own connection pool that maintains connections needed by the application. The pool maintains certain number of connections (configurable) and gives them out to the application, whenever requried. The pool validates every connection before giving it out by executing a 'validation sql'.
I intermitently experience an exception. - A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I know that Sql Azure closes idle connections after 30 minutes but what I do not understand is that since every connection is validated by the pool before being used by the application, it definitely is not idle for 30 minutes. The connection is being closed after the validation and before it's being used by the application, which is almost immediately.
The query run on the validated connection is also very simple and yields a small result set. So it can't qualify as a long running query/transaction.
Does Sql Azure close connections that have been open for a certain time i.e after a certain 'age'. I can't think of a better explanation.
- 已移动 Brian AurichMicrosoft Employee, Moderator 2010年9月28日 21:35 migration (From:SQL Azure)
Connections can be closed for several reasons. The recommended best practice I've seen when dealing with SQL Azure is to not leave them open. Open it right before you need it and close immediately after the operation is completed. This not only helps reduce occurances of this, but also helps you maximize the number of processes that can then access your limited supply of SQL Azure connections.
You can find a list of considerations at: http://msdn.microsoft.com/en-us/library/ee730903.aspx
Look at the "Developing SQL Azure Applications" section.
- 已标记为答案 Yi-Lun LuoModerator 2010年9月23日 9:38
2010年9月16日 19:30I wonder what your validation query is. Try using a different validation query; make sure that you SQL requires a real database connection. I wonder if you are not getting a shallow/cached response that is returned to you by one of the intermediate proxies.
Herve Roggero - SQL Server Firewall and Auditing Consultant
Your connection can be closed for several reasons. One is you have too many open, or some of your query is long running, taking up too many CPU time. One thought is your connection is being throttled. You should open only if and when you need it, and close it when you are done. On top of that, to get reliable connection, you need to implement retry.
This article desribes a bit about connection closing and retry that may help you. IMHO, you must implement retry policy if you want your application to work, period.
Thanks, Scott Ma Schlumberger IT & Software Community Leader. MS SME for Entity Framework and Windows Azure Platform.