Azure Table Service

I was recently helping a customer design a web application that would allow them to track donors and donations to their organization. They had tried going the Software As A Service route, but found them to be too expensive and difficult to get started with.

They had a small in-house Agile team that was tasked with getting this into production. One of my first meetings with them was around the data model and the requirements document they needed to give to their SQL DBA to provision the databases for them.

• At this point the project was just starting and the requirements weren't really that clear.
• Developing the relational model was taking too long.
• They wanted something that would be super fast and responsive from multiple platforms and locations.
• They didn't really want to deal with backups.

Using a SQL DB on their existing infrastructure seemed to be the obvious choice, but it seemed to be overkill for an application like this.

• The users weren't going to be accessing the data outside the application. i.e. SQL Management Studio, etc.
• The relationships between entities were going to be modelled in code anyways, the SQL relational model wasn't really going to add any value.
• Once the relational model is defined and implemented, any future changes requiring changes to the model\relationships is a lot more effort.
• Using SQL required a lot of upfront planning and forecasting to make sure that the server and databases were sized properly, backups, etc.
• SQL is great for Scale Up approach. However this particular organization had decided last year that all future development was going to be based on a Scale Out\Low cost commodity hardware approach.
• Connectivity from the internet was going to be a challenge. Need to open ports\firewalls into the private datacenter.

We started having conversations about using an alternate approach, the best option that came to mind was Azure Tables. It met most of the customers requirements, although it was a new paradigm and they weren't fully convinced. As we talked through the benefits, they quickly realized the savings in costs and effort.

• Pay as you go. Much lower licensing costs. Costs per GB much lower than they could achieve on their own.
• No need to worry about backups.
• Available on the internet directly, Can use Shared Access Control to restrict people accessing the data selectively.
• Faster development time. Don't need to define the data model upfront. Can easily add new entities as you go along.Easily re-partition data as requirements are added and evolved

https://azure.microsoft.com/en-us/documentation/articles/storage-table-design-guide/

What is the Table service? As you might expect from the name, the Table service uses a tabular format to store data. In the standard terminology, each row of the table represents an entity, and the columns store the various properties of that entity. Every entity has a pair of keys to uniquely identify it, and a timestamp column that the Table service uses to track when the entity was last updated.

The account name, table name and PartitionKey together identify the partition within the storage service where the table service stores the entity.

In the Table service, an individual node services one or more complete partitions and the service scales by dynamically load-balancing partitions across nodes. If a node is under load, the table service can split the range of partitions serviced by that node onto different nodes; when traffic subsides, the service can merge the partition ranges from quiet nodes back onto a single node.

The article above also talks helps developers understand how to optimize the key\value store for Read or Write heavy scenarios. The best thing that I like about the Azure Table service is forces the developer to think about the query they need to fulfill the application requirements...and then store data based on that design. Table storage is so cheap that it allows you to de-normalize data and store different views of the same data to help make the application go faster.

https://azure.microsoft.com/en-us/documentation/articles/storage-table-design-guide/#design-for-querying

The article above also has some great information about Design Patterns and Modelling relationships in a lightweight scalable way.

For your next application, do consider using Azure Table, or some other similar NoSQL persistent storage.

• Do you really need a full fledge SQL DB for your application?
• Are you fully going to leverage the complex modelling and relationships that SQL provides? Maybe your application code is going to implement anyways.
• Managing the SQL backend and infrastructure is going to require additional work and operational overhead.

I don't think that SQL DBs are going away by any means. There is still a place for SQL in complex business applications, where access to the data is required through multiple channels, Business Intelligence needs, etc. However, I would challenge you to really think through the need for SQL rather than using it as the default.

Some scenarios could also benefit from the Azure SQL service. You get all the functionality of SQL, but don't need to manage and tune the infrastructure. Let the service do that for you, but still have all the full SQL functionality that you are used to.

I think the future of application development is going to tilt heavily in the No\Low SQL direction. Moving towards DataLayer-as-a-Service is definitely going to help minimize developer effort while providing higher scalability and reliability at a lower cost. Who could argue with that :) .