• 3 min read

Building Large Scale Elastic Database Tiers with SQL Azure: Introducing Federations

One of the exciting new features in the just-released SQL Azure Q4 2011 Service Release is SQL Azure Federation. In a sentence, SQL Azure Federation enables building elastic and scalable database…

One of the exciting new features in the just-released SQL Azure Q4 2011 Service Release is SQL Azure Federation. In a sentence, SQL Azure Federation enables building elastic and scalable database tiers.

Video Presentation of SQL Azure Federation

To accompany this blog post, you can also view my short video recording with a demonstration here.

Imagine the canonical 3-tier application: these applications handle growing user workloads by adding and removing nodes to their front and middle tiers. Federations extend the same model of scalability to the database tier. With federations applications can scale beyond a single SQL Azure database and harness the capacity of 10s or 100s of nodes. At any point, administrators can play with federation dials to expand and shrink the capacity of their database tier without requiring any application downtime.

Federations provide four key benefits:

  • Massive Scale: Federations bring in the sharding pattern in SQL Azure and allow harnessing of massive capacity at the database tier. Combined with the power of SQL Azure, administrators can choose to engage 10s or 100s of nodes within the SQL Azure cluster.
  • Best Economics: With federations, database tiers become truly elastic. Administrators can repartition applications based on workload to engage or disengage SQL Azure nodes. With federations, no downtime is required for these repartitioning operations.
  • Simplified Development and Administration of Scale-out Database Systems: Federations come with a robust programming and connectivity model for creating dynamic applications. With native tooling support for managing federations and with online repartitioning operations for orchestrating federation at runtime, federations greatly ease management of databases at scale.
  • Simplified Multi-tenant Database Tiers: Multi-tenancy provides great efficiencies by increasing the density of tenants per database. However, a static decision on placement of tenants rarely works for the long tail of tenants or for large customers that may hit scale limitations of a single database. With federations applications don’t have to make a static decision about tenant placement. Federations provide repartitioning operations for efficient management of tenant placement and re-placement and can deliver this without any application downtime.

Let’s take a look at a sample application of federation to ground these notions. Imagine a web site called Blogs’R’Us that is hosting blogs. At any given day, users create many new blog entries and some of these go viral. However, it is hard to predict which blogs will go viral; so placing these blogs on a static distribution layout means that some servers will be saturated while other servers sit idle. With federations, Blogs’R’Us does not have to be stuck with static partition layout. They can handle the shifts in traffic with federation repartitioning operations and they don’t need to take downtime to redistribute the data.

Let’s look at how one would stand up a federation for Blogs’R’Us: federations are objects within a database, much like tables, views or stored procedures. However they are in one way special; they scale out parts of your schema and data out of the database to other member databases.

Creating a Federation

To create a federation with the news SQL Azure Management Portal, click the New Federation icon on the database page.

CREATE FEDERATION creates the federation and its first federation member. You can view the details of the layout of your federation in the Federations Details page:

Deploying Schema to Federations

Federation members are regular SQL Azure databases and have their private schemas. You can deploy your schema to this federation member using various create statements to create your objects like tables, stored procedures, triggers and views. To connect and deploy your schema, you can click the Query > New Query action on the federation member.

With federations you provide additional annotations to signify federated tables and the federation key. The FEDERATED ON clause is included in CREATE TABLE statements for this purpose. All other objects require no additional annotations.


Scaling-out with Federations

Now that you have deployed your schema, you can scale out your federation to more members to handle larger traffic. You can do that in the Federations Detail page using the SPLIT action.

The federation page also provides detailed information on the progress of the SPLIT operation. If you refresh after submitting the SPLIT operation, you can monitor the federation operation through the federation page:

As the application scales, more SPLIT points are introduced. As the application workload grows, administrators SPLIT federation into more federation members. Federation easily powers such large scale applications and provides great tooling to help administrators orchestrate at scale.

If you’d like to get further information on how to work with federations, you can refer to SQL Azure online documentation as well as my blog. My short video demonstration of the new SQL Azure Federation feature is also available here. You can also follow updates to federations on twitter via #sqlfederations.