In previous posts I talked about scaling options and techniques and introduced the concepts for SQL Azure federations. Let’s solidify the concepts with a few sample scenarios that showcase federations. Don’t expect an exhaustive list... I am sure you all can come up with many other examples of how to use SQL Azure federations.

Sharding pattern has been popularized by internet scale applications so scenarios in this category turn out to be perfect fits for SQL Azure federations. I spent the last few years building one of these types of apps, working on electronic health record store on the web called HealthVault. In a sentence, HealthVault stores electronic health records for consumers in a given country. It is available in the US and a number of other countries. Users and applications can put and get small data such as body weight or blood pressure all the way to huge blobs like MRIs into their health records. Much like many other application on the web, the traffic changes on the site as news articles, blogs and TV appearances attract users to the site. Capacity requirement shift rapidly and even though it is possible to predict capacity few days or weeks in advance, predicting next months is much more difficult. How do you handle these shifts with the best economics and still avoid maintaining large excess capacity?

Web Scale Applications:

Pick your favorite free site on the web and it is easy to generate examples like HealthVault… Imagine a news website or a blog site. On a given day, there isn’t always a way to predict which articles will go viral. As people render and comment on the popular viral articles, the database traffic goes up. Best way to handle such rapid shift in resource requirements is to repartition data to evenly distribute the load and take advantage of the full capacity of all machines in your cluster. Such unpredictability makes capacity planning a tough challenge day to day. Add on top, the unpredictable capacity requirements of software updates to these apps with new and improved functionality and one thing is crystal clear. Scale-up does not work for these environments. Scale-up has a ceiling capacity and you have to invest in that capacity up front as there is not immediate elasticity. Well… this is where federations shine! Federations provide the ability to repartition data online. With no loss of availability, you can initiate an “ALTER FEDERATION … SPLIT AT” for example, to spread the workload on the federation member that contains 100s or 1000s of news articles to 2 federations members covering half the new articles and get twice the throughput.

Multi-Tenant Solutions

Another great scenario for SQL Azure federation is multi-tenant ISV apps. Many solutions on premise have been built with the classic model of one-database-per-tenant, meaning as a customer you get your own server and database. That model certainly translates to the cloud as well. However databases come with some level of built-in elasticity – today that elasticity allows the smallest database to be 1GB and largest to be 50GB in data size for example. However for the small footprint customer – a.k.a the long tail of customers creating a full blown database may not be feasible. The upper limits of a single database may also not be able to support the larger footprint customers. You may need to engage multiple nodes. It is likely that customer may start small and grow or shrink over time or may have varying computational needs. Thus single-database-per-tenant simply may not work in the cloud.

Imagine a web-front for solo doctors’ offices or small bed & breakfast operators that manage scheduling and reservations. OR a human resources app that is tracking many franchise stores… Given the hurried pace of business, you don’t have days to provision new servers and environments for newly on-boarded customers. You need customer’s to self-service themselves. Provision just in time then use the app right away… Some these self-provisioned tenants grow in load and may need to graduate to a less dense tenancy model. As your customers load shift, you can adjust the tenant count per database through online SPLITs and MERGEs or your databases.

There are many other examples, for example, federations can also help with sliding window scenarios where you manage time based data such as inventory over months or entity based federation where a ticker symbol could be used to manage asks and bids in a stock trading platform. In all these partitioned cases, if you’d like to repartition data without losing availability and with robust connection routing as market focus changes to different stocks, federation will help.

I could go on for pages and I am certain there are many more examples out there you guys have. I just threw in a few of the cases I like to talk about to explain the SQL Azure federation concept.  If you have scenarios you’d like to share, just leave a note on the blog…