SQL Azure Federations

SQL Azure Federation is a way of splitting a table (or tables) across a number of SQL Azure databases. The technique is also know as sharding. For more information, see this MSDN article.

There are a few use cases for federations:

  1. Your table is too big to fit in a single database (i.e. it’s greater than 150GB)
  2. Query performance is a problem, it’s a way of breaking up the table into smaller pieces, which run on different physical hardware.
  3. In a multi-tenancy scenario, it’s a way of physically partitioning tenants into separate databases.

Creating a federation

Federated databases have a ‘Federation Root’ database. This is the principal database you connect to. Once connected to this, you can create a Federation with this command:

CREATE FEDERATION federation_name (distribution_name <data_type> RANGE)

You need to specify a ‘range’, which is a value you will use to split your data into the separate federations. Typically you would use a bigint. Federations can also be created in the management portal.

When you create a table, you then specify which column to use for the value of the range (i.e. which column to federate on).

USE FEDERATION federation_name(distribution_name = value) WITH RESET, FILTERING={ON|OFF}
    [ schema_name . ] table_ame
        ( { <column_definition> | <computed_column_definition> }
        [ < table_constraint> ] [ ,...n ] )
FEDERATED ON (distribution_name = column_name)

Unfortunately you can’t ‘ALTER’ a table for federation, you can only issue the command on CREATE.

Using federation

When you connect to the database you need to include an extra command to indicate that we want to work with federations. You also need to supply a value to indicate which of the federations to work with. One of the advantages of Federations, is that we don’t need to specify the actual federation, just the value for range. SQL Azure will determine which Federation this value falls in, and routes you accordingly.

USE FEDERATION federation_name (distribution_name = value)

The disadvantage is that this command needs to be included in your  application. The command is associated with a connection, so if there is some clever connection pooling going on (i.e. entity framework) you may have some extra work to do. You cannot call this command from within a stored procedure.

With filtering on, only records which match the range value will be retured. With filtering off, all records from the federation can be returned.

Splitting a federation

Splitting federations is straight forward, you can either issue a command:

ALTER FEDERATION federation_name SPLIT AT (boundery_value)

…or use the management portal.

In either case, it’s an atomic operation, requiring no down-time.

What’s missing?

  • Identity columns are not allowed in federated tables. You can either use GUIDs (not a very nice thing to split on) or create some kind of identity issuing table in the federation root.
  • Timestamp columns are not allowed.
  • You can’t join federations back together (unless you write the code yourself!).
  • Pricing.

Other interesting features

  • Each federation is just a separate database. You can connect to them and alter the schema yourself if you want (I’m not sure I advise this!).
  • There is a tool to help you migrate your data.


This is the first release of SQL Azure Federations, and whilst this isn’t a panacea to solve all big data problems (I’m not sure anyone has a good solution for sharding) it does present some interesting features. Separating the the layout of the federations from the concern of the application is nice, and the splitting operation is good too. It’s a shame that applications need to be modified, and include the extra ‘USE’ command, but perhaps that’s something that will change?

I see Federations as a compromise between SQL Azure and Table Store. You give away a few features (e.g. identity and timestamps) but you gain scalability.

Another tool in the box.