SQL Azure uses best practices for database Deployment (2)

Next: SQL Azure uses best practices for database Deployment (2)

SQL Azure Disconnects an existing connection for a longer transaction, or if the transaction is idle for a long time while it is connected. If you want to avoid being cut off by SQL Azure, you must resolve the above two issues within 5 minutes. How quickly do you deal with those 5 minutes? This requires you to keep the connection open in the shortest possible time to avoid idle transactions. When you are done with a command, quickly close the connection in a limited time and return the link to the pool.

The demo code is as follows:

using (SqlConnection cn = new SqlConnection (...))
{
cn. Open ();
using (SqlCommand cmd = CN. CreateCommand ())
{
Cmd.commandtext = ...;
...
}
}

When a link is placed in a pool, the probability of a connection failure is minimal. However, if your command executes for more than five minutes and is difficult to handle, your connection will be turned off by SQL Azure. You can divide the updates into small tasks for a number of batches. In addition, you need to ensure that the database is indexed well.

Because the system is overloaded, your connection is disconnected when SQL Azure restricts database activity.

To prevent a disconnect in any case, you should develop SQL Azure applications that enable them to handle connection errors and transparently reconnect without user involvement.

Additionally, you may need to add code to handle updates to the database that were retried because of a time-out or disconnect. This will be a lot of extra code, but luckily Microsoft has done some work. "Best Practices for handling Transient Conditions in SQL Azure client Applications" was retrieved on the Microsoft AppFabric Customer Advisory Group website. This article describes the connection errors that you may encounter and gives sample code for how to handle them. There is a link at the end of this article that allows you to download the sample code.

The final tip is how to get customer support for SQL Azure. If your application encounters problems and needs help, make sure you get the ID of the application session. SQL Azure logs errors and activities on their web site. If you give them a session ID, the support team can make it easier to figure out what's going on.

The following C # code shows how to retrieve the session ID of a connection:

Cmd.commandtext = "Select CONVERT (nvarchar), context_info ())";
SessionId = new Guid (cmd. ExecuteScalar (). ToString ());

This article discusses several issues that developers and database administrators may encounter when dealing with SQL Azure and provides guidance and best practices on how to handle these issues.