Building Scalable Database Solution with SQL Azure - Introducing Federation in SQL Azure

Building Scalable Database Solution with SQL Azure - Introducing Federation in SQL Azure

Rate This
  • Comments 1

In the previous post, I described the rationale for scale-out vs scale-up. Explained why scale-out was the best thing after sliced bread and also why specifically sharding or horizontal partitioned wins as the best pattern in the world of scale-out. I also talked about the advantages of using SQL Azure as the platform for building sharded apps.

Last week, we introduced the Federation concept in SQL Azure in this talk. In the next few posts, I’ll detail the upcoming concept and how to build applications using federations in SQL Azure. This will help you be ready when this technology ships in future versions of SQL Azure.

As the first post on the topic, lets first talk about how federations improve the life of the admin and the developer. Specifically we’ll attack the following common challenges that emerge from this pattern;

#1 Repartitioning and Redistributing Data: For the DBA managing applications with sharding pattern, repartition operation requires quite a lot of acrobatics. Federation make it easier to redistribute and repartition your data.

#2 Robust Connection Routing: For the devs building applications with sharding pattern, challenge is to put a routing layer that can handle repartitioning operations without requiring application downtime. Federation have built in routing support that provides full safety even when repartitioning operations are moving data around in the system.

First thing first; Introducing Federation in SQL Azure

To address some of these challenges above and more, we will be introducing federations in SQL Azure. Federation are key to understanding how scale-out will work. Here are the basic concepts;

  • Federations represent all data being partitioned. It defined the distribution method as well as the domain of valid values for the federation key. In the picture below, you can see that the customer_federation is part of sales_db. There can be multiple federations within a database. Each federation represent a unique distribution scheme. For example, customer-orders and products in a single db may have unique distribution requirements thus may be partitioned over to a orders_federation and a products_federation within a single sales_db.
  • Federation Key is the key used for partitioning the data. The key represent a data domain (such as a bigint or guid) and distribution style (such as range).
  • Atomic Unit (AU) represent a single instance value of the federation key. Atomic units cannot be separated thus all rows that contain the same instance value of the federation key always stay together. In the customer_federations, customer_id may be the label for the federation key and customer_id=55 may be the atomic unit.
  • Federation Member (aka Shard) is the physical container for a range of atomic units. Federation members are system managed databases.
  • Federation Root is the database that houses federations and federation directory. Any database that contain federations is a federation root for all the members of the federation.
  • Federated Tables refer to tables in federation members that contain partitioned data, as opposed to Reference Tables refer to table that contain data that is repeated in federation members for lookup purposes.

Following figure shows how it all look when you put these concepts together. Sales_DB is the database that contains a federation (a.k.a the federation root). Federation called customer_federation is blown up to show you the details. Federation members contain ranges of atomic units (AU) such as [min to 100). AUs contain the collection of all data for the given federation key instance such as 5 or 25.


Repartitioning Operations with Federations

Federation provide operation for online repartitioning. SPLIT operation allows spreading of a federation members data (collection of atomic units) to many federation members. MERGE allows gluing back of federation members data together. This is exactly how federation address the challenge #1 above. With the SPLIT and MERGE operations administrators can simply trigger repartitioning operations on the server side and most importantly they can do this without downtime!

Connecting to Federations

Federation also allow connection to the federation’s data using a special statement; “USE FEDERATION federation_name(federation_key_value)”. All connections are established to the database containing the federation (a.k.a root database). However to reach a specific AU or a federation member, instead of requiring a database name, applications only need to provide the federation key value. This eliminates the need for apps to cache any routing or directory information when working with federations. Regardless of any repartitioning operation in the environment, apps can reliably connect to the AU or to the federation member that contain the given federation key value, such as tenant_id 55, simply by issuing USE FEDERATION orders_federation(55). Again the important part is, SQL Azure guarantees that you will always be connected to the correct federation member regardless of any repartitioning operation that may complete right as you are establishing a connection. This is how federations address the challenge #2 above.

Creating and Operating Federations

By the time we release the feature some of the syntax details may change but here is conceptually how you can set up a federation;

-- in master database 


-- connect to the new sales_db, create the federation. this creates the first federation member. 

CREATE FEDERATION orders_federation(c_id BIGINT RANGE)

-- connect to the federation member and deploy schema to the federation member

USE FEDERATION orders_federation(0) …


CREATE TABLE orders (…, customer_id BIGINT NOT NULL) FEDERATED ON (c_id = customer_id)

CREATE TABLE orders_details (…, customer_id BIGINT NOT NULL) FEDERATED ON (c_id = customer_id)

CREATE TABLE zipcodes (…)

-- When you want to scale out customer’s orders, connect to sales_db and SPLIT the federation data

ALTER FEDERATION orders_federation SPLIT AT(c_id=100)

-- When you want to scale in., connect to sales_db and MERGE the federation data

-- this may not be available in versionn1.

ALTER FEDERATION orders_federation MERGE AT (c_id=100)

Here is how the app connects and works with federation;

SqlConnection cn = new SqlConnection(“;Db=sales_db;UserID=uname;Password=…;”);


SqlCommand cm = new SqlCommand(“USE FEDERATION fed1(c_id=58) …”)


I’d love to hear feedback from everyone on federations. Please make sure to leave a note here or raise issues in the SQL Azure forums here.

Many Thanks


  • Just looking at building Cloud BI solutions and this federation looks very interesting. I am really looking forwards to using it

Page 1 of 1 (1 items)