SQL Azure 180 concurrent connections

Question

SQL Azure (Web and Business) allows 180 concurrent connections at most as indicated here:

http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/23/premium-preview-for-windows-azure-sql-database-now-live.aspx

I'm trying to understand what's meant by concurrent connections.

Assume there is an aspx page Test.aspx that uses a SqlConnection object to Select Top 1 * from TestTable. After SqlReader returns, the connection is closed. (I believe ASP.NET will still be using connection pooling). Assume this page is simultaneously hit by 500 users.

Does this mean 500 concurrent connections or 1 connection because I'll be using a pooled connection?

P.S. I'm also looking at SQL Federations to scale out the database layer, but if each DB allows up to 180 concurrent connections and I query each DB and fan out results for every simultaneous user on the application layer, then I don't see how I'm scaling out.

Answer 1

Regarding your Federations note, It is now deprecated. You would need to use new Elastic Scale Utility to Create Shards, there are plenty of post about it on SO.

Assume this page is simultaneously hit by 500 users. Does this mean 500 concurrent connections or 1 connection because I'll be using a pooled connection?

That depends on what your Page is doing. Lets say a First request hits the page and page is doing a long running query say which runs for 20Sec (Hypothetically), in that 20Sec if another 100 Request hits and in turn each of those 100 request is doing long running query, then you are indeed using 101 (including the first one) actual connections to the DB and not re-using anything from the Connection Pool.

Practically it is very unlikely that a web page would be doing something like above.

Answer 2

A concurrent connection is from the standpoint of the database server. In .NET, when your data table is returned, the connection is logically closed, meaning that the connection is marked as available in the connection pool, but the actual database connection is not closed; it remains open for a period of time. When a connection is reused from the pool, .NET issues an sp_reset_connetion command to the available connection so that it can be reused.

A new connection to the database is created when there are not enough connections available in the pool to satisfy the demand. So to reach 180 concurrent connections you would need to issue 180 simultaneous requests to the database, because the pool would need to create that many connections.