Manage Learn to apply best practices and optimize your operations.

SQL Azure database recommendations and best practices

SQL Azure database is modeled on SQL Server technology, but it doesn’t always act like it. SQL Azure is distinct in how it works, and you should handle it accordingly. Read on for guidelines to keep your cloud deployment in fine working order.

Although SQL Azure is based on SQL Server technology, and most of the best practices for SQL Server apply in SQL Azure, some of the differences in the architecture and features require additional consideration and forethought when building this new type of application. 

For example, the differences in connectivity behavior or that SQL Azure resources get throttled when you overload the database require you to take such things into account and code your application to handle issues you may not have using traditional a SQL Server application. This article will provide you with several recommendations and best practices to follow in your SQL Azure database deployments.

If your application front end is hosted in Windows Azure, place both the application and the SQL Azure database in the same GEO location. This will optimize connectivity by reducing the latency between the application and database servers. Traditionally, your web servers and database servers would be in the same facility, but SQL Azure is hosted in several data centers around the world. So, in terms of system setup, the Windows Azure app role should be in the same location as the SQL Azure database.

As a traditional database gets larger and more heavily used, it is easier to handle the load by scaling out as opposed to up. The same rule applies to the SQL Azure database, and even more so because of resource throttling. When SQL Azure sees that a database is using too much CPU and disk I/O, it throttles the throughput to make sure other databases on the server are not affected. Since throttling is implemented on the database level, you are better off spreading large data into multiple databases, either horizontally or vertically. This way, the chance of getting throttled is reduced, and any potential throttling will affect only part of your data set.

Moreover, if SQL Azure needs to initiate failover for one of your databases and switch to another server, it can do it faster if the database is smaller. Similarly, if you have a read-only database that gets refreshed from your SQL Server, you can create more than one database in SQL Azure and have the application select which one to talk to; you can do a round-robin or a random selection. This way, you can distribute the load across SQL Azure and reduce the chance of being throttled.

Another thing to keep in mind is that connectivity to SQL Azure servers is much more susceptible to interruptions, and your applications should be able to handle them gracefully and provide a transparent ability to reconnect. But compared with traditional SQL Server environments, there are several scenarios in which you might get disconnected. For example, if you step outside of your local network, there are more points of failure along the way, including routers, switches, regional Internet outages, etc. Then there’s the way SQL Azure database works. If your database server fails, SQL Azure will bring a new server online and recover your database. It’s a quick process, taking only a few seconds, but any existing connections will be severed.

If you have a long running transaction, or if your connection is idle for too long SQL Azure can also cut off existing connections. In both cases, the threshold is five minutes. So how do you deal with either? You can avoid idle transactions by keeping the connection open for the shortest time possible. Once you execute a command, close the connection and return it to the pool. The following code shows you how:

using (SqlConnection cn = new SqlConnection(…))
    using (SqlCommand cmd = cn.CreateCommand())
        cmd.CommandText = …;

If you keep the connection in the common pool, the chance of the connection going stale is minimized. But having your connection killed by SQL Azure if the command is executing for more than five minutes is harder to deal with. You may have to break your updates into smaller batches. Aside from that, make sure the database is well indexed.

You also might get disconnected when SQL Azure throttles your database activity because the load on the system is too high.

To guard against getting disconnected under any circumstance, code your SQL Azure applications so that they can handle connection errors and transparently reconnect without the users noticing any issues.  

Also, you may want to add code to retry database updates if they fail because they timed out or were disconnected. That’s a lot of extra code, but luckily, Microsoft has done some of that work for you. Check out"Best Practices for Handling Transient Conditions in SQL Azure Client Applications on the Microsoft Server AppFabric Customer Advisory Team website. The article describes the types of connectivity errors you might encounter and gives sample code demonstrating how to handle them. There is a link at the end of the article that will let you download sample code from the article.

One final recommendation deals with calling SQL Azure customer support. If you are having issues with your application and require assistance, make sure you capture the session ID of your application. SQL Azure logs errors and activities on their side. The support team can more easily figure out what’s going on if you give them the session ID. The following C# code shows how you can retrieve the session ID for your connection:

    cmd.CommandText = "Select CONVERT(nvarchar(36), CONTEXT_INFO())";
    SessionId = new Guid(cmd.ExecuteScalar().ToString());

This article discussed several issues developers and database administrators may run into when working with SQL Azure database, and provided guidelines and best practices for dealing with them.

About the author:
Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada.

Next Steps

How to handle the SQL Server post-installation blues

Learn how to best navigate SQL Server on Azure

Dig Deeper on Microsoft SQL Server Management Issues