Skip to main content

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.

Federation Architecture

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:
Diagram of the logical model for federationsDiagram of the physical model for federations

Design Considerations

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.

Comments

Post a Comment

Popular posts from this blog

Python and Parquet Performance

In Pandas, PyArrow, fastparquet, AWS Data Wrangler, PySpark and Dask. This post outlines how to use all common Python libraries to read and write Parquet format while taking advantage of  columnar storage ,  columnar compression  and  data partitioning . Used together, these three optimizations can dramatically accelerate I/O for your Python applications compared to CSV, JSON, HDF or other row-based formats. Parquet makes applications possible that are simply impossible using a text format like JSON or CSV. Introduction I have recently gotten more familiar with how to work with  Parquet  datasets across the six major tools used to read and write from Parquet in the Python ecosystem:  Pandas ,  PyArrow ,  fastparquet ,  AWS Data Wrangler ,  PySpark  and  Dask . My work of late in algorithmic trading involves switching between these tools a lot and as I said I often mix up the APIs. I use Pandas and PyArrow for in-RAM comput...
Post a Comment
Read more

Ingesting IoT Sensor Data Into S3 With an RPI3

StreamSets Data Collector Edge is a lightweight agent used to create end-to-end data flow pipelines. We'll use it help stream data collected from a sensor. Due to the increasing amount of data produced from outside source systems, enterprises are facing difficulties in reading, collecting, and ingesting data into a desired, central database system. An edge pipeline runs on an edge device with limited resources, receives data from another pipeline or reads the data from the device, and controls the device based on the data. StreamSets Data Collector (SDC) Edge, an ultra-lightweight agent, is used to create end-to-end data flow pipelines in StreamSets Data Collector and to run the pipelines to read and export data in and out of systems. In this blog, StreamSets Data Collector Edge is used to read data from an air pressure sensor (BMP180) from an IoT device (Raspberry Pi3). Meanwhile, StreamSets Data Collector is used to load the data into Amazon's Simple Storage Service ...
Post a Comment
Read more

Real-Time Talk: Windows 10 IoT Core Background Tasks and ASP.NET Core Web Apps

Display useful information from your Windows 10 IoT Core application in an ASP.NET Core web app, essential for integrating IoT data into a solution. Windows 10 IoT background task talk with a web application using WebSockets. Problems As my path to this solution has been troublesome, I am listing here the main problems I faced so my dear readers have a better idea of dead-end streets along the way: I was not able to make the ASP.NET Core web application run under a Windows 10 IoT background service. I found no information about when or if it will be supported in the near future. ASP.NET MVC and ASP.NET Core have different SignalR implementations. I was not able to make a SignalR client for .NET Core work with SignalR hosted on a web application. I was able to make things work by directly using a WebSocket. It’s not as nice a solution as I had in my mind, but it works until things get better. Making the Background Task and Web Application Talk I worked out sim...
Post a Comment
Read more