Federation Metadata in SQL Azure Part 1 – Federations and Federation Members

One of the great value propositions of federations is the ability to represent all the data distribution on the server side for both your schema (for example which parts of your schema you want to scale out and using what keys etc) and your runtime layout (for example whether you need 30 or 300 federation members at any one time to handle your workload).

The annotations you put in to express your design and runtime decisions are represented in a number of system views. In part 1, we will take a look at the basic views that represent federations itself and members. In future parts of the series, we’ll take a look at other metadata that allow you to monitor ongoing federation operations, federation error reporting views as well as view a history of all the federation events, and connection metadata for federations and how to use that.

Alright lets get started…

Federations and Federation Members

Definition of a federation is represented by 2 system views which capture the federation distribution scheme;

• sys.federations represent the federations in a database.
• sys.federation_distributions represent the definition of the federation distribution scheme for federations. Yes, in v1 a federation only allow a single federation distribution key but the normalization is there to allow for multiple federation distribution keys in future.

Federation member metadata is quite symmetric except it represents additional runtime information the distribution information.

• sys,federation_members represent members of the federations
• sys.federation_member_distribution provides the distribution details per federation distribution key per member.

These views are available in all databases, root and members. Root contains the full set of rows for all federations and members. They only display the information related to the member you are connected to. For example, sys.federations would return all federation in ‘salesdb’, the root database, lets say we have federations f1 and f2 with 20 members each. However if you switch over to a federation member 2 of f1, sys.federation* system views will only contain federation f1 and member information for only member 2 that you are connected to. This is much like how sys.databases work in SQL Azure master database and user databases.

Besides the federation membership information, sys.databases contain the is_federation_member column to signify if a database is a federation member or a user database.

Lets look at a few examples that can help work out some common queries; 

 -- am I a member database? select is_federation_member  from sys.databases where name=db_name() -- am I a root database? select (~ is_federation_member) & (select cast(count(*) as bit)  from sys.federations)  from sys.databases where name=db_name() -- am I a db but not a root? select (~ is_federation_member) & ( select ~ cast(count(*) as bit)  from sys.federations)  from sys.databases where name=db_name()  go

Here are a few more about federation information for root dbs;

 -- count of members each federation contain? use federation root with reset GO select f.name, count(fm.member_id)  from sys.federations f join sys.federation_members fm  on f.federation_id=fm.federation_id group by f.name GO 

and a few for members dbs;

 -- which federation is this member a part of? select * from sys.federations  where (select is_federation_member    from sys.databases    where name=db_name())=1 GO -- what are my low and high ranges? select range_low, range_high from sys.federation_member_distributions GO -- what is member db name and root db name? use federation federation1(key=100) with reset go select db_name() go use federation root with reset go select db_name() go

 -- how many member are there between a range of federation keys declare @low sql_variant declare @high sql_variant set @low=50000 set @high=390000 select member_id, range_low, range_high from sys.federation_member_distributions  where (range_high between @low and @high    OR range_low between @low and @high) order by range_low

If there are other examples you’d like to suggest, you can always leave a comment. Thanks and enjoy!