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

Kubernetes Configuration Provider to load data from Secrets and Config Maps

Using Kubernetes Configuration Provider to load data from Secrets and Config Maps When running Apache Kafka on Kubernetes, you will sooner or later probably need to use Config Maps or Secrets. Either to store something in them, or load them into your Kafka configuration. That is true regardless of whether you use Strimzi to manage your Apache Kafka cluster or something else. Kubernetes has its own way of using Secrets and Config Maps from Pods. But they might not be always sufficient. That is why in Strimzi, we created Kubernetes Configuration Provider for Apache Kafka which we will introduce in this blog post. Usually, when you need to use data from a Config Map or Secret in your Pod, you will either mount it as volume or map it to an environment variable. Both methods are configured in the spec section or the Pod resource or in the spec.template.spec section when using higher level resources such as Deployments or StatefulSets. When mounted as a volume, the contents of the Secr...
Post a Comment
Read more

Andriod Bug

A bug that steals cash by racking up charges from sending premium rate text messages has been found in Google Play.  Security researchers have identified 32 apps on Google Play that harbour the bug called BadNews. A security firm Lookout, which uncovered BadNews, said that the malicious program lays dormant on handsets for weeks to escape detection.  The malware targeted Android owners in Russia, Ukraine, Belarus and other countries in eastern Europe. 32 apps were available through four separate developer accounts on Google Play. Google has now suspended those accounts and it has pulled all the affected apps from Google Play, it added. Half of the 32 apps seeded with BadNews are Russian and the version of AlphaSMS it installed is tuned to use premium rate numbers in Russia, Ukraine, Belarus, Armenia and Kazakhstan.
Post a Comment
Read more