SQL in Cloud

Connecting to a database is a time consuming process as there are various steps (establishing physical connection, authentication etc.) involved in the connection establishment process.
In practice, most application used only few connection configuration and throughout the run time of the application several identical connections will be repeatedly opened and closed by the application.

To minimize this process, AdO.NET uses an optimization technique called connection pooling. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls “Close” on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call. Connection pooling is by default enabled in the ADO.NET unless application explicitly disables it.
A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default). Connections are released back into the pool when they are closed or disposed.

Transaction Support

Connections are drawn from the pool and assigned based on transaction context. Unless Enlist=false is specified in the connection string, the connection pool makes sure that the connection is enlisted in the Current context. When a connection is closed and returned to the pool with an enlisted System.Transactions transaction, it is set aside in such a way that the next request for that connection pool with the same System.Transactions transaction will return the same connection if it is available. If such a request is issued, and there are no pooled connections available, a connection is drawn from the non-transacted part of the pool and enlisted. If no connections are available in either area of the pool, a new connection is created and enlisted. When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. This allows you to commit or abort the distributed transaction later.

Default Isolation level:

The default transaction isolation level for SQL server is Read Committed. However for .Net TransactionScope it is serializable.

Isolation level leaking:

Except SQL server 2014, in all the versions of SQL Server, the isolation level of the connection depends on what it was when it was returned to the pool. For example below code throws the error because readpast locking hint is not supported in the serializable isolation level. However this behavior is different in SQL Azure, and the transaction isolation level is reset when the connection returned to the pool.
using (var scope = new TransactionScope())
{
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
sqlConn.Open();
SqlCommand updateCommand = new SqlCommand(“select * from sys.databases”, sqlConn);

var returnValue = updateCommand.ExecuteReader();
}
scope.Complete();
}

// => It should be using (var s = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted }))
using (var s = new TransactionScope())
{
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();

// => this fails with the error that READPAST locking hint is not supported in serializable isolation level.
Var cmd = new SqlCommand(“SELECT * FROM sys.databases WITH(READPAST)”, sqlConnection);

var readerWithKeys = cmd.ExecuteReader();

while (readerWithKeys.Read())
{
Console.WriteLine(readerWithKeys[0].ToString());
}
}
s.Complete();
}