How to Detect If You Are Connected To A Federation Root

If you develop applications in SQL Azure, you may end up in a situation where your code needs to know if it is operating in the root database or in a Federation Member, or if it is connected to a regular user database. Two ways can be used to make this determination: using system tables, or using the USE FEDERATION command.

Using System Tables

This approach is the easiest to implement since it relies a SQL call only, without much logic in your application code. Assuming your code is currently connected to a SQL Azure database, you could use the sys.databases table to read the is_federation_member column.  

Here is the statement you would use:

SELECT is_federation_member FROM sys.databases WHERE name = db_name()

If you are currently connected to a federation member, is_federation_member will be 1; otherwise it will be 0.

Note that as of this writing, running the above statement in SQL Azure will throw an error because the is_federation_member column is not yet available in regular SQL Azure user databases. However, if you connect to a federation, the SQL code above will run.

Using the USE FEDERATION Command

Alternatively you can also use code in .NET to make that determination. Connect to the database as usual, using the SqlConnection class for example, then perform the following tasks:

-          Execute the USE FEDERATION ROOT WITH RESET command, which forces the connection back to the root database (in case you are not current in the root database)

-          Execute SELECT db_name() which fetches the name of the database of the root database

-          Compare the original database name you connected to with the one returned by the previous call; if the names are the same, you originally connected to the root database

Here is the sample code:

string sql = "USE FEDERATION ROOT WITH RESET";

string sql2 = "SELECT db_name()";

bool isFederationRoot = false;

try

{

 SqlConnection sqlConn = new...; // Your connection string goes here

 sqlConn.Open();

 (new SqlCommand(sql, sqlConn)).ExecuteNonQuery(); // connect to the root db

 SqlDataReader dr = (new SqlCommand(sql2, sqlConn)).ExecuteReader(); // Get the db_name()

 if (dr.Read())

   isFederationRoot = (dr[0].ToString().ToLower() == dbName.ToLower());

 dr.Close();

}

catch { }