SALES: 1-800-867-1380

Federation Guidelines and Limitations

Updated: April 24, 2014

This topic describes guidelines and limitations for federations in Microsoft Azure SQL Database. The general guidelines and limitations are covered in the following sections.

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.

Tables

Federations can contain both federated tables and reference tables. Federated tables are tables created using the FEDERATED ON clause, and contain a column that is associated with the distribution key for the containing federation. Reference tables are tables within a federation that were not created using the FEDERATED ON clause, and have no special association with the federation distribution key. For more information on creating tables, see CREATE TABLE.

Federated Tables

Federated tables have the following limitations:

  • The federation column of the federated table can only contain data that conforms to the federation member range_low inclusive and range_high exclusive.

  • The data type of the federation column must exactly match the data type that is defined in the federation definition.

  • All unique and clustered indexes on the federated table must contain the federation column. The order in which the federation column appears in the index can be different from the key ordinal in the federation.

  • Federation column values cannot be updated to values outside the federation member range.

  • The federation column cannot be a persisted or non-persisted computed column.

  • Indexed Views are not supported in federation members.

  • Federation columns cannot be NULLable.

  • All foreign key constraints on federated tables need to include the federation column on both the referrer and the referenced tables at the same ordinal in the foreign key. Reference tables cannot have foreign key relationships with federated tables. Federated tables can have foreign key relationships with reference tables without restrictions.

  • You can drop tables created with the FEDERATED ON clause normally. You can also use ALTER TABLE to change all properties of a federated table except federation attributes such as the federation key. To change a reference table into a federated table or a federated table into a reference table, you must create new tables with the desired properties and drop the existing table.

  • When a table is marked with STATISTICS_NORECOMPUTE, federation operations like SPLIT do not invalidate or recalculate statistics. This could cause execution plan issues after repartitioning operations such as SPLIT.

  • Federated members do not support the identity property

  • Federated members do not support the timestamp and rowversion data type.

All general table metadata for federated tables is available through standard system views. Federation specific properties are available through sys.federated_table_columns.

Reference Tables

Reference tables are not automatically distributed across all federation members. While reference tables can be replicated across members of a federation manually, there is no automatic replication process.

Reference tables often contain supporting information for queries against federated tables so that you do not have to perform queries against multiple databases. For example, while customer information may be federated and spread across multiple member databases, there is nothing to be gained from federating state and zip code reference information. However you may want to store a copy of the state and zip code information in each federation member so that your queries do not have to span multiple databases.

Spatial Support

Geography and geometry types cannot be used as the data type of the column that a table is federated on; however they can be part of the federated table. There are no other limitations on using spatial data with federations.

After a SPLIT or DROP operation, spatial indexes stay consistent and intact in the destination federation members.

Hierarchical Data

The hierarchyid type cannot be used as the data type of the column that a table is federated on; however it can be part of the federated table. There are no other limitations on using hierarchyid with federations.

After a SPLIT or DROP operation, hierarchyid indexes stay consistent and intact in the destination federation members.

Connecting to Federation Members

Connections to a federation are performed through the USE FEDERATION statement. This statement automatically routes the connection to the correct member within a federation, eliminating the need to know the physical database name when accessing the data. Specifying the federation distribution key and a value will establish a connection to the corresponding member database within the federation.

Security Principals

Authentication and authorization to a database containing a federation is accomplished as normal. Logins and users determine connectivity; roles manage the grouping of principals. However, principals within a database that contains a federation are scoped only to the federation root database and are not automatically applied to federation members. For more information on users and roles, see Managing Database Federations (Azure SQL Database)

Copy Operations

A database copy cannot be performed on a database that contains federations. Creating a federation fails if a database copy operation is active in the database. Database copy cannot be performed on federation members either.

Database Size Quota

Federation operations are not applied to the federation root quota. If the root database exceeds quota, you will still be able to perform SPLIT and DROP operations. Likewise, if the size quota is exceeded for a federation member, SPLIT and DROP operations can still be performed as long as the destination database quota will not be exceeded by the operation.

Connection and Transact-SQL Properties

At the completion of repartitioning operations such as SPLIT and DROP, connections are dropped. This means that connection properties such as SET options, transaction isolation level settings, or variables are also reset. The SPLIT operation creates a new physical database, which means that the following Transact-SQL properties cannot be maintained across SPLIT operations.

 

Transact-SQL SQL Database Support Limitations Support in databases with Federations

Timestamp and rowversion data type

SQL Database may not preserve the uncommitted timestamp and rowversion values of the current database (DBTS) across failovers.

Timestamp and rowversion data type is not supported in federation members.

Timestamp and rowversion related functions such as @@dbts returns values when no timestamp and rowversion exists in a given database.

SYSUTCDATETIME(),SYSDATETIMEOFFSET(),SYSDATETIME(),getdate(), getutcdate()current_timestamp

SQL Database may report timestamp and rowversion from the local computer and may report times that are further in future or in the past across failovers.

Supported with the same constraints across repartitioning operations.

DATABASE_PRINCIPAL_ID()

Principal SIDs are guaranteed to be the same across federation members and federation root for a given principal name. However principal ids may change after a repartitioning operations such as DROP.

IDENTITY property on columns

IDENTITY property is not supported in federation members. Identity related functions such as IDENT_CURRENT, IDENT_SEED, IDENT_INCR, SCOPE_IDENTITY always return null since no identity columns can exist in federation members.

OBJECT_ID and related functions that work with user defined objects; object_id(…), object_name(object_id), type_id(…), type_name(type_id)

Object_id of any object may change for user defined objects after a repartitioning operation such as DROP.

Altering Databases with Federations

All Azure SQL Database database settings are supported; however changes to the value of an option on the federation root database will not change the option of any federation members.

See Also

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

Community Additions

ADD
Show:
© 2014 Microsoft