Federations in Windows Azure SQL Database (formerly SQL Azure)
Federations in SQL Database are a way to achieve greater scalability and performance from the database tier of your application through horizontal partitioning. One or more tables within a database are split by row and portioned across multiple databases (Federation members). This type of horizontal partitioning is often referred to as ‘sharding’. The primary scenarios in which this is useful are where you need to achieve scale, performance, or to manage capacity.
SQL Database can deliver scale, performance, and additional capacity through federation, and can do so dynamically with no downtime; client applications can continue accessing data during repartitioning operations with no interruption in service.
A federation is a collection of database partitions that are defined by a federation distribution scheme, known as the federation scheme. The federation scheme defines a federation distribution key, which determines the distribution of data to partitions within the federation. The federation distribution key must be an INT, BIGINT, UNIQUEIDENTIFIER, or VARBINARY (up to 900 bytes) and specifies a range value. There can only be one federation scheme and one federation distribution key for a federation.
The database partitions within a federation are known as federation members, and each member covers a part, or all, of the range of values covered by the data type of the federation distribution key. Federated tables are tables which are spread across federation members. Each federation member has its own schema, and contains the federated table rows that correspond to the federation member’s range. The collection of all rows in a federation member that match a specific federation key value is called a federation atomic unit. Each federation member contains many federation atomic units. A federation member may also contain reference tables, which are tables that are not federation aware. Reference tables are fully contained within a member, and often contain reference information that is retrieved in combination with federated data.
A federation member provides physical separation between the data it contains and data stored in other members. Each federation member has its own schema, which may temporarily diverge from the schema of other members due to member specific processing such as performing a rolling schema upgrade across all members.
While federation members are physically implemented as databases, they are logically referenced at the application layer as a range of federation key values. For example, a federation member database that contains rows associated with federation key values 50-100 would be logically accessed by specifying a key value within that range rather than specifying the database name.
Federations are accessed through a federation root database, which represents the application boundary of the federation. It functions as the logical endpoint for applications to connect to a federation by routing connections to the appropriate federation member based on the specified federation key value. Each root database may contain multiple federations, each with its own federation schema. It may also contain global data, such as users, passwords, roles, or other application specific data.
The following diagrams illustrate the logical and physical model for federations:
When designing a federation, one of the most important design decisions is what value to federate on. Ideally you want to select a key that allows you to federate data from multiple, related tables so related rows are stored together. For example, in the case of a multi-tenant application you might select the tenant_id. The rows within each federated table that specify the same tenant_id value would be stored in the same federation atomic unit.
You must also consider how to insert new records in such a way that all federation members are equally utilized, instead of storing all new records in one member. Determining how to distribute new data among federation members must be handled at the application layer.
Since there is a physical separation of data contained in different federation members and SQL Database doesn’t support join operations across databases, your application must implement the logic for joining data from multiple federation members or multiple federations. For example, a query that needs to join data from a two federations would need to perform separate queries against each and join the data within the application. The same holds true for aggregating data across multiple shards within a single federation, such as obtaining a count of all rows contained within the federation.
Open Specification Promise
A new specification that describes additional SQL capabilities for data sharding is now available under the Microsoft Open Specification Promise. For more information, see the Windows Azure SQL Database Federations specification.