3

is there any limit on the number of concurrent connections that can be established on SQL azure database. this is the situation that i am facing: i have a website which is hosted on a Windows server 2008 R2 virtual machine to which many users will connect and the database is created in SQL azure. we have observed that when there are too many concurrent connections, there is a connection loss meaning any further connections with the correct usernames and passwords to the website fail to login. no user will be able to login to the website for atleast 40 minutes after which the same usernames and passwords successfully log the users into the website.

is this throttling issue or what actually happens when multiple users try to login and perform multiple time consuming activities. what is the step that i can take to resolve this issue. does the session the multiple users open causing this error.can you please tell me the exact thing and the steps i need to take to avoid such a situation.

kavitha
  • 41
  • 1
  • 2
  • 4

1 Answers1

6

There is a hard limit of 180 connections on a database. If you for example have got a retry framework also in place when you start to get to this limit you will see the situation get worse as it try's to retry creating more connections to SQL Azure, increasing your issue.

You will start to see errors like this :

Exception Type: System.Data.SqlClient.SqlException Resource ID : 1. The request limit for the database is 180 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance.

http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management.aspx#Throttling_Limits

The quickest fixes for this is to try and cache lots of stuff and to try using a max pool limit on your connection string if you had lots of compute in front. (Although it sounds like you don't)

Caching in Azure

The other way to fix this issue is to shard your databases if you cannot lower your connection limits through cache, and I would recommend doing this a custom process rather than use federation for more control.

To get an idea also that you don't have a connection leak and to see what is going on I would look at this resource.

hths,

James

Jordan.J.D
  • 7,999
  • 11
  • 48
  • 78
JamesKn
  • 1,035
  • 9
  • 16
  • we also observed that if one virtual machine causes this connection loss to azure, other virtual machine connecting to the SQL azure database will also face the connection loss.but it gets rectified by itself after about 30-40 minutes. how does this happen. – kavitha May 21 '13 at 04:45
  • we have hosted the website on 2 VM's with one having 1GB RAM and the other having 4 GB RAM.As a test we ran samples for stress tesing using JMeter and when we encountered this error we tried to recycle application pool the website is connected to and everything got fixed immediately.what has application pool got to do with this. have i missed any setting on the IIS 7. – kavitha May 21 '13 at 05:55
  • @kavitha worth having a looking at the monitor connections when you place load into the system and see if this is point of problems then work your way backwards from there. Maybe there is something else causing this process to be throttled. I would assume a reboot of the app pool would clear all connections including retries dropping the load on the database considerably. – JamesKn May 21 '13 at 23:07
  • now i was able to figure out where the error is coming from. we have a layer between Business and data access layers called BCL where we make use of databasefactory classes to form queries. here the SQL connections are opened within transactions. is this causing any problems is what i want to know.can we have concurrent transactions where database is opened like this.there might be cases when user just exits and transactions dont rollback immediately.is this why im getting connection errors? – kavitha Jun 05 '13 at 06:27
  • 1
    Does that process leave your connections opens and hence you are getting build up of connections? – JamesKn Jun 05 '13 at 19:19