Export (0) Print
Expand All

Managing Database Federations (Azure SQL Database)

Updated: April 24, 2014

ImportantImportant
The current implementation of Federations will be retired with Web and Business service tiers. Consider deploying custom sharding solutions to maximize scalability, flexibility, and performance. For more information about custom sharding, see Scaling Out Azure SQL Databases.

An Overview of Federation Administration

Transact-SQL statements, functions and views can be used to create, alter, and monitor federations. This topic provides information on administering federations.

Creating and Altering Federations

Each federation begins with a root and initial federation member. These are created by using the CREATE FEDERATION statement. After creating the federation, a federated table structure can be created by connecting to the federation root using the USE FEDERATION statement, and then using the CREATE TABLE with the FEDERATED ON clause.

To scale out a federation, the ALTER FEDERATION statement can be used to split an existing federation member into new members. The SPLIT operation identifies the member to be split through the federation key value specified as part of the operation. The federation member is split at the specified value, resulting in two new federation members containing the sum total of rows previously contained in the source member.

ALTER FEDERATION can also be used to reduce the number of federation members through the DROP AT operation. The range of federation key values formerly covered by the dropped member are transitioned to an adjacent member; however the data formerly associated with this range is not preserved by this operation.

noteNote
Federation members cannot be created or dropped using the CREATE DATABASE or DROP DATABASE commands.

The DROP FEDERATION statement can be used to drop a federation, including all federation members and other associated metadata and logical objects.

Additionally, ALTER DATABASE is fully supported on federation members.

Connecting to a Federation

While it is possible directly connect to the physical database(s) that contain federation data, this is not the optimal method of connection as it relies on knowledge of the individual member database name(s). The USE FEDERATION statement provides easier connectivity by routing all connections through the federation root, and does not require you to know the names of the individual member databases, only the federation name and federation key.

Connections can be filtered using the WITH FILTERING=ON clause, so that operations on the connection are scoped to only the federation atomic unit that matches the federation key value. With filtering on, the query processor adds a predicate containing the federation key value on the federation column to every query that refers to a federated table.

WarningWarning
A federation atomic unit is not a security boundary, and the query processor does not guarantee complete isolation of information between units within the same federation member. Do not rely on filtering as a security isolation mechanism.

To determine whether a connection is filtered or not, sys.dm_exec_sessions returns an is_filtered column. The data type of this column is bit; a 1 is returned if the connection is filtered, otherwise 0. If the connection is filtered, you can determine the filtering value by using FEDERATION_FILTERING_VALUE.

Users and Roles

Authentication to databases containing federations is the same as authentication to databases without federations. However the setup of security principals such as users and roles is scoped to the federation root database and are not automatically replicated to any of the federation members.

Federation members are not allowed to have users with logins. Instead, user accounts created in a federation member are linked to the account of the same name in the federation root. Roles within federation members are managed as in a non-federated database.

When the first federation member is created, the account executing the statement becomes the owner of the federation member. To add an existing principal from the federation root to the federation member, the database owner must create a user in the federation member with the CREATE USER statement and link the user to an existing principal in the federation root database using the ALTER USER statement. The user name must exactly match the principal name in sys.database_principals in the federation root. Linking users to roles or other types of principals other than users is not supported.

noteNote
If the username does not exactly match an existing principal name in the federation root, the statement will fail with the following error message:

‘user_name’ is not a valid user_name or you do not have permission

Users and roles within the federation root database are created as for a non-federated database.

Linked users can be used as regular user accounts within a federation member. They can participate in EXECUTE AS clauses and can be added to roles. With EXECUTE AS, the WITH NO REVERT clause is not supported because linked users do not have trust and dbchaining enabled.

Monitoring and Metadata

The following views provide information about federation operations. Information is reported by these views as long as the operation is executing, and is cleaned up immediately after the operation completes.

 

Federation operation view Description

sys.dm_federation_operations

Returns information about federation level operations

sys.dm_federation_operation_members

Returns information about federation members involved in operations

sys.dm_federation_operation_errors

Returns information about errors that have occurred during federation operations

sys.dm_federation_operation_error_members

Returns information about federation members involved in operations that have encountered an error

Additional metadata describing federations, members, and the distribution scheme used within the federation, can be obtained from the following views. Also, sys.databases now returns a column named is_federation_member, which contains a bit value that indicates whether a database is a federation member.

 

Federation Metadata Table Description

sys.federations

Returns the federations within a database.

sys.federation_members

Returns the federation members within a federation.

sys.federation_distributions

Returns the distribution type and data types used by a federation.

sys.federation_member_distributions

Returns the distribution name and range covered by a federation member.

sys.federated_table_columns

Returns federation specific information on federated tables.

Historical information on federation operations can be obtained through the federation history views. The information allows you to reconstruct the federation root and member information, including the range covered by a member, for any given point in time. The federation history views are as follows:

 

Federation History Table Description

sys.federation_history

Returns historical information on federations

sys.federation_member_history

Returns historical information on federation members

sys.federation_distribution_history

Returns historical information on the distribution type and data type for a federation

sys.federation_member_distribution_history

Returns historical information on the range covered by a federation member

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft