The Pitfalls Of Porting Applications Using On Site SQL Server to Azure SQL Database

Following the decision by someone I know to use Azure SQL Database for his start-up and the general rise in interest in this platform, I thought I would write a blog post on the potential pit falls of assuming you will get the same behavior with Azure SQL Database as you would with the on site version of the product. I’ve summarized some of the key differences in the following bullet points:

• Resource throttling
  To deal with “Noisy neighbor syndrome”, Azure can throttle back resource usage, this takes place in the form of connections being terminated in extreme cases, the following links relate to this topic and are recommended reading:
  the resource limits that determine when throttling takes place
  design and coding “Best practices” for dealing with throttling
  hard and soft throttling
• Database size
  At the time of writing this post, the hard and fast limit for database size is 150 GB, to work around this you effectively partition your data across multiple database using sharding, Azure SQL Database federations facilitate this.If you talk to Microsoft about performance the standard response is that you should scale out via Azure SQL Database federations. Performance and scalability have two dimensions: throughput; the ability of a platform to handle more transactions and response time: how fast queries/transactions are executed. Azure SQL Database federations only deals with the throughput aspect of this. I liken this to making a curry and forgetting to buy any chili powder, no matter how much curry powder you add, you will not get that extra chili kick. This analogy relates to Azure SQL Database in that no matter how many databases you partition your data across, this will not bring down the execution time of individual transactions or queries. I don’t know if this is the best analogy in the world, but any home chefs will be able to associate with this. However, help might be at hand, I mentioned this to Conor Cunningham after his keynote at this years SQL Bits and he hinted that “Advanced hardware skus” might be in the pipeline. This information should ** NOT ** be taken as gospel until an official Microsoft announcement appears.
• Maximum degree of parallelism of 1
  Azure SQL Database comes with a hard coded maximum degree of parallelism of 1, this means you should not in any way expect OLAP style queries to perform in the same way they do with an on site instance of SQL Server that tuned for queries to execute with parallel execution plans.
• Inter tier latency
  The latency between the database tier and that above it for an on site instance of SQL Server should be sub millisecond, with Azure SQL Database the inverse is likely to be true. This blog post from the SQLCAT team explains how table valued parameters can be used to minimize the effects of inter-tier latency. Whilst object relational mapping tools are both popular and conducive to increased coding productivity, they can result in lots of network round trips between the database tier and that above it, and hence exacerbate this latency problem.
• Isolation level
  The default isolation level for Azure SQL Database is snapshot isolation and this cannot be changed, where as for the on site version of the product, this is read committed. The upshot of this is that if your application is coded for and expects the behavior of the read committed isolation level, you might be in for a surprise when you port your application to Azure SQL Database.
• Table storage
  Although this is not a feature of Azure SQL Database but that of the wider platform,
  I have come across people who want to leverage this as much as possible on the grounds that is cheaper than Azure SQL Database and not subject to throttling. The popular use cases for this are around the need to storage images / blobs and the use of the entity attribute model. Whilst being highly flexible the entity attribute model comes with its own baggage which warrants a blog post in its own right. The point to note here is that the use of table storage should not be perceived as being a “Get out jail free” card when Azure SQL Database performance and scalability issues come to light.

In my humble opinion people have got away with writing applications with relatively low standards of engineering by “Throwing tin” at performance and scalability issues. For me the interesting thing with Azure SQL Database is that because this is no longer an option,
it should force engineering quality standards up. The introduction of the core based licensing model will have a similar effect. Either that or there will be some horrible examples of failed migrations to Azure SQL Database that will come out of the wood work.