Hade du nytta av den här sidan?
Din feedback om det här innehållet är viktig. Berätta vad du tycker.
Ytterligare feedback?
1500 tecken kvar
Managing Database Federations
Det här innehållet finns inte tillgängligt på ditt språk men här finns den engelska versionen,

Managing Database Federations (Azure SQL Database)

Updated: July 15, 2015

Federations will be retired with Web and Business service tiers in September 2015. (See Web and Business Edition Sunset FAQ.) If you don’t plan to migrate your application off Federations by then, your application will stop working after September 12, 2015. Consider using the Elastic Database tools for Azure SQL Database to build a sharded, scale-out solution for your data tier. To try it, see Get Started with Azure SQL Database Elastic Database tools.

Transact-SQL statements, functions and views can be used to create, alter, and monitor federations. This topic provides information on administering 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.

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.

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.

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.

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.

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.

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


Returns information about federation level operations


Returns information about federation members involved in operations


Returns information about errors that have occurred during federation operations


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


Returns the federations within a database.


Returns the federation members within a federation.


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


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


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


Returns historical information on federations


Returns historical information on federation members


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


Returns historical information on the range covered by a federation member

See Also

© 2015 Microsoft