Federations simply bring in the sharding pattern into Windows Azure SQL Database as a first class citizen. Sharding pattern is used for building many of your favorite sites on the web such as social networking
sites, auction sites or scalable email applications such as Facebook, eBay and Hotmail. By bringing in the sharding pattern into SQL Database, federations enable building scalable and elastic database tiers and greatly simplify developing and managing modern
multi-tenant cloud applications.
Federations scalability model is something you are already greatly familiar with: Imagine a canonical multi-tier application: these applications scale-out
their front and middle tiers for scalability. As the demand on the application varies, administrators add and remove new instances of the front end and middle tier nodes to handle the variance in the workload. With Windows Azure Platform this is easily achieved
through easy provisioning of new capacity and the pay as you go model of the cloud. However database tiers have typically not provided support for the same elastic scale-out model. However with federations, SQL Database enable database tiers to scale-out in
a similar model. With federations, database tiers can be elastically scaled-out much like the middle and front tiers of the application based on application workload. Using federations, applications can expand and contract the number of nodes that service
the database workload without requiring any downtime!
SQL Database Federations can scale the database tier much like the front and middle tiers of your application.
Federations bring a great set of benefits to applications.
Federations offer scale beyond the capacity limits of a single SQL Database. Using federations, applications can scale from 10s to 100s of SQL
Databases and exploit the full power of the SQL Database cluster.
Federations provide easy repartitioning of data
without downtime to exploit elasticity for best price-performance. Applications built with federations can adjust to variances in their workloads by repartition data. The online repartitioning combined with the pay-as-you-go model in SQL Database,
administrators of the application can easily optimize cost and performance by changing the number of databases/nodes they engage for their database workload at any given time. And no downtime is required for this change.
Developing large scale application is greatly simplified with federations; Federations come with a robust programming & 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!
Multi-tenancy provides great efficiencies by increasing density and cost per tenant characteristics. However a static decision on placement of
tenants rarely work for long tail of tenants or for large customers that may hit scale limitations of the single database. With federations application don’t have to make a static decision about tenant placement. Federation provide repartitioning operations
for efficient management of tenant placement and re-placement. ...And can deliver this without any applications downtime!
Figure 2: Federation provide a flexible tenancy model.
Federations can help many types of database application to scale. Here are a few examples;
Many on premise application are developed with a single-tenant—per-database model. However for modern cloud applications a flexible tenancy model
is needed. Static layout of tenants require overprovisioning and with cloud infrastructures, single node solutions may be limiting in scaling to spectrum of all your tenants; from the long-tail (very small customers) and to large-head (very large customers).
With federations ISVs are not stuck with a static tenant layout. Federated database tiers can easily adapt to varying tenant types and workload characteristics and continue to grow as new tenants are acquired into the system.
Lets look at an ISV providing a customer relationship management sit. Some tenants start life small with a small customer portfolio and other
tenants may have a very large portfolio of customers. Some of these tenants may purchase the silver package and others upgrade to gold or deluxe editions as their sales activity grow. With federations, silver package tenants start life in small, multi-tenant
federation member, but can graduate to a dedicated federation member (a.k.a single-tenant-per-database model) when they upgrade to gold. With the deluxe package, tenants go to scale-out tenants over multiple federation members. Best part is, with federations
upgrades from silver to gold to deluxe require no downtime or application code changes!
Applications on the web face capacity planning challenges every day to handle varying and growing traffic. Peaks, bursts, spikes or new flood
of users... With federations, applications on the web can handle these variances in their workloads by expanding and shrinking the capacity they engage in SQL Database.
For example, imagine a web site that is hosting blogs, lets call the site Blogs’R’Us. At any given day, users create many new blog entries and
other visit and comment on these blogs. Every day some of these blogs go viral. However it is hard to predict which blogs will go viral any given day. Placing these blogs on a static distribution layout across servers means that some servers will be saturated
while others server 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.
As the workload grows, applications can continue to engage more nodes to provide unlimited scalability to the application from 100s of nodes that are available worldwide through the SQL Database. With the pay-as-you-go model of SQL Database combined with federations,
applications also do not need to compromise on the economics with big upfront investment in computational resources and grow over time with online repartitioning operations just-in-time.
Applications built with NoSQL technologies can also benefit greatly from federations in SQL Database. Federations bring a great set of the NoSQL
properties to the SQL Database platform such as the sharding pattern. With federations, SQL Database adapts an eventually consistency model while preserving local consistency with full ACID transactions in federation members.
Another great benefit of federations is to scale-out tempdb which is the lightweight storage option that come with every reliable SQL Database.
Each SQL Database is a reliable, replicated, highly available database. Federation members are simply system managed SQL Databases. With each federation member, applications also get a portion of the nodes TempDB. TempDB provides lightweight, high performance
Federations provide all the power of the SQL Database for storing unstructured data or semi structured data through data types such as XML or
These are just some of the examples of the NoSQL gene embedded in SQL Database through federations. You can find a detailed discussion of this
Federation implementation is extremely easy to work with. Here are a few of the concepts that will be helpful in understanding federations;
Federations: Federations are objects
within a user database just like other objects such as views, stored procedures or triggers. However federation object is special in one way; it allows scaling out of parts of your schema and data out of the database to other system managed databases called
federation members. Federation object represents the federation scheme which contains the federation distribution key, data type and distribution style. SalesDB in figure 2 below represent a user database with federations. There can be many federations to
represent varying scale-out needs of subset of data – for example you can scale out orders in one federations and products and all its related objects in another federation under SalesDB.
Federation Root: Refers to the database that houses federation object. SalesDB is the root database in figure 3 above. Root
database is the central repository for information about distribution of scaled-out data.
Federation use system managed SQL Databases to achieve scale-out named federation members. Federation members provide the computational and storage capacity
for parts of the federations workload and data. Collection of all federation members in a federation represent the collection of all data in the federation. Federation members are managed dynamically as data is repartitioned. Administrators decide how many
federation members are used at any point in time using federation repartitioning operations.
Figure 3: SalesDB is the root database with many federations.
Federation Distribution Key: This
is the key used for data distribution in the federations. In the federation definition, the distribution key represented by 3 properties:
Federation Atomic Unit: Represent
all data that belongs to a single instance of a federation key. An AU (federation atomic unit) contains all rows in all federated tables with the same federation key value. AUs are guaranteed to stay together in a single federation member and is never SPLIT
further into multiple members. Federation members can contain many atomic units. For example, with a federation distribution key such as tenant_id, atomic unit refers to all rows across all federated tables for tenant_id=55.
Figure 4: Federation Atomic Units
Refer to tables that contain data that is distributed by the federation. Federated tables are created in federation members and contain a federation distribution
key annotated with the FEDERATED ON(federation_distribution_key = column_name) clause when creating a table. You can find more details in the online documentation for the CREATE TABLE statement in SQL Database. In figure 5 below, federated tables are marked
Refer to tables that contain reference information to optimize lookup queries in federations. Reference tables are created in federation members and do not
contain any FEDERATED ON annotation. Reference tables typically contain small lookup information useful for query processing such as zipcodes that is cloned to each federation member. In the figure below, reference tables are marked green.
Central Tables: Refer to tables that
are created in the federation root for typically low traffic or heavily cached data that does not need to be scaled-out. Good examples are metadata or configuration tables that are accessed rarely by the application. In the figure below, central tables are
Figure 5: Types of Tables in Federations. Federated tables are represented in
blue. Reference tables are represented in
and central tables are represented in
One of the fundamental advantages of federations is the online repartitioning operations that can be performed on federation objects. Federation allow online
repartitioning of data through ALTER FEDERATION T-SQL commands. By repartitioning Orders_Fed with a SPLIT operation for example, administrators can move data to new federation members without downtime and expand computational capacity from 1 to 2 federation
members. Federation members are placed in separate nodes and provide greater computational capacity to the application.
Figure 6: SPLIT operation creates 2 new federation members in the background and replicated all the data in the original source member to the new destination members.
For the walkthrough here, we'll be using the SQL Azure Management Portal. To go to the management portal you can click on the "Manage" button under SQL Database in the Windows Azure Management Portal OR simply visit your fully qualified SQL Database server
name in the browser with an HTTPS:// protocol header like
Creating Federations: To create a federation in
the SQL Azure Management Portal, you can click the New Federation icon on the database page.
Figure 7: Creating a federation.
You can use the following T-SQL to create a federation as well:
CREATE FEDERATION blogs_federation(id BIGINT RANGE)
You can find details of the CREATE FEDERATION statement in the
Windows Azure SQL Database documentation. Basically, CREATE FEDERATION creates the federation and its first federation member.
You can view the details of the layout of your federation in the federation’s details page or using the following T-SQL:
SELECT * FROM sys.federations fed JOIN sys.federation_member_distributions fedmd ON fed.federation_id=fedmd.federation_id order by range_low
Figure 8: Federation’s detail page.
Deploying Schema to Federations:
Federation object allow you to scale out parts of your schema to federation members. Federation members are regular SQL Databases and have their private schemas. To deploy schema to federation members instead of the root database, you need to first
connect to the federation member that you want to target. USE FEDERATION statement allow you to do just that. You can find details on the USE FEDERATION statement in
Windows Azure SQL Database documentation. Once you are connected to the federation member, you can deploy your schema to this federation member using the same create
object 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.
Figure 9: Actions on federation members in the federations detail page.
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. Tables are the only objects that require special annotation. None of the other object can be deployed using the regular SQL Database T-SQL syntax. Here is a sample T-SQL CREATE TABLE statement that creates a federated table;
USE FEDERATION blogs_federation(id=-1) WITH RESET, FILTERING=OFF
CREATE TABLE blogs_tbl(
blog_id bigint primary key,
blog_title nvarchar(1024) not null,
) FEDERATED ON (id=blog_id)
Figure 10: Deploying schema using the online T-SQL editor
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 in the management portal or using the following T-SQL statement:.
ALTER FEDERATION blogs_federation SPLIT AT(id=100)
Repartitioning operations like SPLIT are performed online in SQL Database so even if the operation take a while to perform, no application downtime is required while the operation is performed. You can find a detailed discussion of the online SPLIT operation
Figure 11: SPLIT action on a federation
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 or using the following T-SQL;
SELECT * FROM sys.dm_federation_operations
Figure 12: Monitoring SPLIT action in the federation’s detail page.
As the application scales, more SPLIT points are introduced. Put another way, as the application workload grow, administrators SPLIT federation into
more federation members. Federation can easily power such large scale applications and provide great tooling to help administrators orchestrate at scale.
Figure 13: Federation’s detail page with full view of all federation members
All of the above operations can be performed through T-SQL as well. For a full reference of T-SQL statements for federations, you can refer to SQL Database
online documentation. You can also visit
my blog for a detailed discussion of federation topics.
The Quintessential SalesDB with Customer and Orders.
that will love SQL Database Federations? Here are a few…
Database Federations: Robust Connectivity Model for Federated Data
“NoSQL” Gene in SQL Database Federations
to Multi-Tenant Database Model Made Easy with SQL Database Federations
Pricing and Billing with Federations in Windows Azure SQL Database:
Introducing Fan-out Queries: Querying Multiple Federation Members.
the images do not shown properly
Hi there, I enlarged the images for better visibility
Hi. The article mentions that federation members get a "portion" of the nodes Tempdb. I've also read that Tempdb is limited to 5Gb. Does this mean that the overall limit is still 5Gb, and federated members receive only a portion of this? Or does each member receive its own 5Gb portion?
Hi There mzz3lh!
The limit on tempdb is per session not per member on tempdb. You can have many sessions concurrently using more than that.
However in general, whatever limits you have for a given SQL Azure database edition (web or business), the same applies to federation members as well. So for this limit on tempdb; Per session if you exceed 5GB of usage of tempdb, your session connected to a member may experience throttling.
So are Federations available in Azure *now*? And is there any solution for doing SQL Server federations in our own data centers (short of deploying our own "old-school" federated database solution with partitioned distributed views, etc.).
Hi Chris, yes Federations are available in SQL Azure today. Unfortunately it isn't available on SQL Server so no way yet to do this with SQL Server deployments.
Thanks Cihan. But would it work with SQL Azure Private Cloud - in other words, with SQL Azure Private Cloud, could we install Azure appliances in our own data centers and scale out to a large-ish scale? By "large-ish" I am talking about potentially replacing some of our OLTP SQL Servers that currently run about 5K transactions per second each with a more distributed architecture based on Azure Federations.
If you are referring to appliances here;
the answer is yes, federations is available in all Azure clusters.
Thanks for this article about a great technology!
Current guidance on sharding from Microsoft can be found here: