Designing an application to work with SQL Azure

Using SQL Azure for your applications is relatively straightforward. All you need is... a connection string... and since SQL Azure uses TDS as its underlying communication protocol, just like SQL Server, it may seem natural that you don't need to change much in your application design approach when connecting a SQL Azure database. However, this may not necessarily be true. SQL Azure was designed for ease of deployment and scalability; and as such you may need to take certain things into consideration when creating a new application against SQL Azure.

The following is not meant to be an extensive list of things to consider, but a starting to that should help you figure out which next steps you need to take.

Performance and Application Responsiveness for Windows Applications
Web development may not be too different from a performance standpoint since all data communication takes place within the data center. However for Windows Applications, where the user interface runs local to a user's workstation, having to connect to the cloud to retrieve data may pose some challenges. First, the communication takes place over SSL; no choice here. So it's always encrypted (good from a security standpoint, not so good for performance). Second, your data can be far, very far away. So retrieving larger recordsets may actually take some time (more than what your users may be expecting). As a result your application design should most likely include careful row selection, smart indexing, multi-threading (for faster UI response time) and even caching for somewhat static data, so you don't have to do database roundtrips every time you need to display the list of States for example. Most of those design choices apply whether you are desiging a web application or a Windows application.

SQL Azure Limitations
SQL Azure also imposes certain limitations you need to be aware of that may impact your design. For example, depending on your service plan your database may be limited to 10GB. As a result, if you plan in storing that kind of data, you will need to plan for data partitioning, or use a technique called Sharding. Also certain capabilities are not yet available, such as using the CLR for more advanced stored procedures. So some of your business logic may need to remain in your client-side code, or in business objects in Windows Azure through the use of REST services. Finally, consider performance of your queries as extremely important; SQL Azure will protect itself against poorly written SQL code and may disconnect your session if it feels you are consuming too much bandwidth, or if you left a connection left open for too long... so automatic reconnection may also be necessary in your design.

Last but not least, security should be strongly considered as part of your design. Storing data in the cloud presents new risks for organizations. As a result, plan for encryption more than usual, and plan to have to implement your routines in your .NET code since Encryption is not yet supported in SQL Azure.

These should be considered high-level thoughts for designing applications that use SQL Azure as their backend storage. Make sure to read about SQL Azure on MSDN for more information.


Print | posted @ Sunday, January 10, 2010 8:36 PM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.