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!

 

Figure 1: 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.

• Unlimited Scalability:

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.

• Elasticity for the Best Economics:

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.

• Simplified Development and Administration of Scale-out Database Systems

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!

• Simplified Multi-Tenancy:

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.

 

 

Who are Federations for?

 

Federations can help many types of database application to scale. Here are a few examples;

 

• Software Vendors Implementing Multi-tenant SaaS Solutions:

 

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!

 

• Web Scale Database Solutions

 

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.

 

• NoSQL Applications

 

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 local storage.

 

Federations provide all the power of the SQL Database for storing unstructured data or semi structured data through data types such as XML or binary types.

 

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 topic here.

 

 

Federation Architecture

 

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 Members: 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:

 

1. A distribution key label that is used for referring to the key,
2. A data type to specify the valid data domain for the distribution such as uniqueidentifier or bigint, and
3. A distribution type to specify the method for distributing the data such as ‘range’.

 

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

 

Federated Tables: 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 light blue.

 

Reference Tables: 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 marked orange.

Figure 5: Types of Tables in Federations. Federated tables are represented in blue. Reference tables are represented in green and central tables are represented in orange.

 

Online Repartitioning: 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.

 

 

How to Create a Federation?

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 https://sql_azure_server_name.database.windows.net/.

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 anotation. 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
GO
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 here.

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.

 

Further Information on Federations

For ongoing information on federations you can search on #sqlfederations tag or Follow @cihangirb on twitter.

Getting Started with Federations:
You can find the official product documentation on federation here.
Windows Azure Training Kit contains a hands-on-lab on federations. December update is available here.  
You can find a 10 min summary of the concept in federations overview video, or a detailed video training on the Federation concepts here; Large Scale Database Solutions on Windows Azure SQL Database with Federations

Deep Dives:
Find detailed deep dives on my blog; http://blogs.msdn.com/b/cbiyikoglu/. Here are a few blog posts that detail federations;

• Sample: The Quintessential SalesDB with Customer and Orders.

• Apps that will love SQL Database Federations? Here are a few…

• SQL Database Federations: Robust Connectivity Model for Federated Data

• The “NoSQL” Gene in SQL Database Federations

• Moving to Multi-Tenant Database Model Made Easy with SQL Database Federations

• Federation Metadata Series: Part-1, Part-2, Part-3

• Pricing and Billing with Federations in Windows Azure SQL Database:

• Introducing Fan-out Queries: Querying Multiple Federation Memebers.