Managing Database Federations (Windows Azure SQL Database)

While physically implemented as SQL Database, federations provide new Transact-SQL statements, functions and views that can be used to create, alter, and monitor federations. Additionally, existing items have been altered to return federation information. The following table summarizes changes made to SQL Database to support federations.

Federations are created by using the CREATE FEDERATION (Windows Azure SQL Database) to create a federation root and initial federation member. After creating the federation, a federated table structure can be created by connecting to the federation root using the USE FEDERATION (Windows Azure SQL Database) statement, and then using the CREATE TABLE (Windows Azure SQL Database) with the FEDERATED ON clause.

To scale out a federation, the ALTER FEDERATION (Windows Azure SQL Database) 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 operation. As with other Transact-SQL DROP operations, dropping a federation member results in all data contained within the member being lost. 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.

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

The DROP FEDERATION (Windows Azure SQL Database) statement can be used to drop a federation, including all federation members and other associated metadata and logical objects.

Additionally, ALTER DATABASE (Windows Azure SQL 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 (Windows Azure SQL Database) 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.

Warning
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 (Windows Azure SQL Database) now returns a federation_filtering_state 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 (Windows Azure SQL Database).

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 mapping in the federation member using a limited version of the CREATE USER syntax. The syntax for CREATE USER within a federation member is as follows:

CREATE USER user_name

[;]

DROP USER username

[;]

ALTER USER userName

     WITH <set_item> [ ,...n ]

[;]

<set_item> ::=

NAME = newUserName

To link an account to an existing principal (account or a user defined role) in the federation root database, the user name must exactly match the account’s sys.database_principals.name 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 username 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 level operations. Information is reported by these views as long as the operation is executing, and is cleaned up immediately after the operation completes.

Additional metadata describing federations, members, and the distribution scheme used within the federation, can be obtained from the following tables. Also, sys.databases (Windows Azure SQL Database) now returns a column named Is_federation_member, which contains a bit value that indicates whether a database is a federation member.

Historical information on federation operations can be obtained through the federation history tables. Federation history tables follow the same structure as the sys.federation* tables, with two additional columns that specify the create date and drop date of the federation and federation member. The federation history tables are as follows:

Federation history information maintains a log of all federation operations (CREATE, ALTER, and DROP FEDERATION). The information allows you to fully reconstruct the federation root and member information, including the range covered by a member, for any given point in time.

Reference

Concepts