SQL Azure notes & queries as at August 2011

One of the best things about cloud services is that every few months a new version comes out and users can instantly use all its new features.

One of the worst things about cloud services is that every few months a new version comes out and users are able to instantly use it and all its new features.

I repeat because in each release of a cloud service constraints and even pricing are changing, so while an offering might not be attractive or even initially possible, a year later it might not only be viable but attractive, too. A case in point is SQL Azure. When it was initially launched it could only handle 10Gb databases, now that’s 50Gb.

I thought some notes and queries around the state of SQL Azure might be in order, and I’ll add update posts to it as the Azure landscape (cloudscape?) changes.

Current betas

One common thing across any type of product is that there are betas and community technical previews (ctp), though these are released more frequently. In SQL Azure there are a couple of ctps to note:

Reporting Services. This is like reporting services in SQL Server on-premises except that it’s limited to just reporting against SQL Azure i.e. you can’t change the data source. Just like with a local copy of reporting services you can use either BI Development Studio or Report Builder 3 to design reports, you just deploy them to Azure instead of your local copy of reporting services.

SQL Azure Data Sync Service. This allows you to replicate data between different SQL Azure databases, or between SQL Azure and a local copy of SQL Server. A typical use case might be the synching of local reference data (a list of products, for example) up to SQL Azure as part of an e-commerce application.

NoSQL and Azure

Just because SQL Azure is part of Azure does not mean you have to use it to store your data. There’s also Windows Azure Storage where you can store blobs and tables which you can use in an Azure application in much the same way as you can with a NoSQL. At the moment Azure Storage is also much cheaper than SQL Azure per Mb so my advice would be to develop a hybrid application where any data for which you don’t need particularly detailed queries e.g. blobs, xml etc. goes in Azure Storage and is linked to a corresponding record in SQL Azure.

Databases bigger than 50Gb

To break the 50Gb limit you’ll need to shard the database and even then you cannot write a cross-database query in Azure T-SQL as it doesn’t understand the three part names myserver.mydatabase.mytable. The only way to union results is via a local copy of SQL which could return a large number of rows and incur significant cost on your Azure account for the data transfers involved. This also means that each of the databases has to have its own lookup data within it. All this might sound very negative but there are large organisations for whom this is still an attractive proposition e.g. retailers with a database per store or an ecommerce application in different regions.

Design to fail

With an armour-plated super fault tolerant datacentre reliably connected to client applications, developers can afford to be lazy. With any cloud service, however, not just SQL Azure, you no longer have this luxury. The connection could easily time out or take longer to connect over the internet, so this needs to be factored into the design, even if it’s an Azure application you’re writing.

Getting Started

To help get you started there are a number of useful tools for SQL Azure on Codeplex. This is another part of SQL Azure that is constantly evolving as the capabilities of SQL Azure change. You’ll currently find:

  • A migration wizard to move an on-premises database from SQL Server to Azure
  • A backup tool based on bcp (bulk copy program) to do periodic backups of SQL Azure to another Azure or local database, and protect against logical errors.
  • Example code and tutorials
  • A SQL Azure connector for Ruby

An interesting use case of these tools is MVP Andrew Couch migrating his customers’ Access application to SQL Azure. SQL Azure is a lot more powerful than Access, though, most significantly around scalability. You can find some good examples of what’s possible in these case studies from start-up JustProud, and my old company IMGroup who use it to develop solutions for their customers.

If this sounds interesting you can sign up for a free trial with your liveID and a credit card even if you don’t incur any charges.

 Find more SQL Azure info over on TechNet.

Comments (0)

Skip to main content