Architectural Considerations for SQL Azure

Introduction

SQL Azure developers need to make certain considerations to achieve the best performance from their applications. In this article, we will walk you through the architecture of an SQL Azure application with a focus on:

• Optimizing for Security, performance and cost
• Partitioning/sharding
• Indexing
• Reducing Latency
• Benefits of SQL Azure

There is reduced risk of management issues and more economical maintenance fees if a database is hosted on the SQL Azure platform. A database hosted on SQL Azure is highly available, reliable and scalable. It can be accessed from anywhere on the internet. The payment model is also very flexible since it offers both pay-as-you-go and bulk pricing.

How to Optimize SQL Azure Applications

Most standard database optimizing techniques apply to SQL Azure just as they would for a traditional on-premise installation.
All challenges associated with managing hardware are eliminated, making the life of the DBA easier since they do not have to worry about scalability and uptime.

Sharding

Sharding is the term used to describe an application pattern that is used to improve the performance of a database application. It is achieved by breaking the database into smaller logical chunks. For example, if the logical database contained sales forecast for the whole world, we can shard it into smaller data chunks by dividing based on continents.

These chunks can be distributed into multiple physical databases (called a shard) instead of one central database.

Typical candidates for sharding include

• Application status updates
• Content generation
• Report and statement generation

The criteria for sharding can include:

• Geographic location
• Users
• Time/Calendar

Indexing

The best way to improve the performance of a database is through the proper use of indexing. Indexing may cause slower writes and an increase in the use of storage space, but the performance improvements for random read operations are worth it. The general recommendations for indexes are as follows:

• Keep the indexes unique
• Keep the indexes small using as few columns as possible
• Avoid placing a clustered index on columns which are of the type “unique identifier”
• SQL Azure requires use on clustered indexes only

How to Create a Clustered Index

One can create a clustered index on an SQL Azure database by using the following SQL Script:

CREATE CLUSTERED INDEX TableName_indexname ON TableName (ColumnName);

Network Latency

SQL Azure Applications will host the database in the cloud, hence it is important to architect the application for reduced network latency.

One of the most time consuming operations is opening a database connection.  When an application makes multiple database operations, it is recommended to use a single connection and perform all the operations using that connection, rather than opening and closing a database connection for each operation.

Recommendations in this category include:

• Make batch data calls (operations in an order) and send a single response
• Use stored procedures for performance and security reasons
• When you have multiple datacenters that an application can connect to, use the Client Statistics option when running queries in SQL Server Management Studio to see which datacenter has a better response time; use the one with the lowest latency for improved performance

Summary

In this article, we learned how we can architect our SQL Azure application for improved performance. I hope you have found this article useful.

About the Author

Vipul Patel is a Software Engineer currently working at Microsoft Corporation. He is currently working in the Microsoft Lync team and has worked in the .NET team earlier in the Base Class libraries and the Debugging and Profiling team. He can be reached at vipul_d_patel@hotmail.com