Optimizing Data Access and Messaging-Managing Connections – Throttling, latency and transactions

http://channel9.msdn.com/learn/courses/Azure/SQLAzure/SQLAzureTipsAndTricks/Exercise-3-Managing-Connections--Throttling-latency-and-transactions

When working against a SQL Azure database you should be aware of the effects ofThrottling, Latency and Transactions.

Throttling, where SQL Azure automatically restricts new (or drops existing) connections, can occur when your database (or server hosting your database) is under heavy load. This ensures you server and database are protected from large numbers of connections and undo load.

However, it does mean you need to write your applications to handle a connection to dropping or transactions failing.

There are not any SQL Azure specific techniques for addressing these issues. In fact, addressing these issues is no different from handling them in the applications you build against on-premise databases.

Some common techniques include,

• Hold a connection open for as short a time as possible.
• Before issuing a command against a connection check it is still open, re-open it if SQL Azure has closed it. You may decide to implement a queue or other retry mechanism.
• Ensure you wrap all database calls in transactions – all exceptions (including ones thrown due to Throttling) should result in the transaction being rolled back.