Working with Windows Azure Federations

Damir Dobric Posts

Next talks:

 

    

Follow me on Twitter: #ddobric



 

 

Archives

Windows Azure SQL Server provide powerful “shard” - database architecture SQL Azure provides horizontal partitioning by using federations feature which has been briefly introduced by Cihan in this post. My intension in this post is to show how to make usage of this feature within a .NET application.

For this demo I created one federation:
Federation Name: Federation2
Distribution Name:FED2DIST1

image

Click on federation to open federation members.
Previously I have created the table in the originally only existing member (LOW)

USE FEDERATION [Federation2] ([FED2DIST1] = -9223372036854775808) WITH FILTERING = OFF, RESET
GO

CREATE TABLE dbo.Table1(
        ID bigint NOT NULL,
        Column1 nvarchar(50) NULL,
        Column2 nvarchar(15) NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
     ID ASC
)
) FEDERATED ON ([FED2DIST1] = ID)
GO

This default member will hold all records with  -9223372036854775808 < ID < 5,
because is is currently spitted to maximum with ID = 5. Fore demo purposes I spitted the federation  to 4 members as shown at the picture below:

image

Now, the crucial question is “how to write an application which is able to deal with all this artifacts”?
Following code shows how to read partition information:

using (SqlCommand cmd = connection.CreateCommand())
{

// First set the connection to Federation Root

cmd.CommandText = "USE FEDERATION ROOT WITH RESET";
cmd.ExecuteNonQuery();


// Now we will retrieve federation specific data

cmd.CommandText = @"SELECT f.name, fmc.federation_id, fmc.member_id, fmc.range_low, fmc.range_high " +
"FROM sys.federations f " +                                                           
"JOIN sys.federation_member_distributions fmc " +                                                            "ON f.federation_id=fmc.federation_id " +           
"ORDER BY fmc.federation_id, fmc.range_low, fmc.range_high";

using (SqlDataReader reader = cmd.ExecuteReader())
{
     Console.WriteLine("Federation Name\t\tFederation ID\tMEMBER ID\tRANGE_LOW\tRANGE_HIGH");

     while
(reader.Read())
     {

         Console.WriteLine(reader["name"] + "\t" +

                reader["federation_id"] + "\t" +

                reader["member_id"] + "\t" +

                reader["range_low"] + "\t" +

                reader["range_high"] + "\t");

             }

                          
}

Following picture shows the result:

image

In fact the only new thing how have to know is statement: “USE FEDERATION”. This setups your connection to specific federation inside of database.
Similarly you can use following code to read data from specific table inside of federation. Note that different federations can have different schemas!

private string federationName = "Federation2";

private string distributionName = "FED2DIST1";

private long federationKey = 5; // Start value of one distribution. This would be a member 5-7 in th epicture above

// Before we start to work with federation, we have to execute ‘USE FEDERATION’ statement

cmd.CommandText = "USE FEDERATION " + federationName +
"(" + distributionName + "=" + federationKey + ") WITH RESET, FILTERING = OFF";
cmd.ExecuteNonQuery();

// Now, all statemens are routed to selected federation.

cmd.CommandText = @"SELECT * FROM table1";

 

using (SqlDataReader reader = cmd.ExecuteReader())

{

    while (reader.Read())

    {

        //...

    }

}

 

Federation Pricing: http://blogs.msdn.com/b/cbiyikoglu/archive/2011/12/12/billing-model-for-federations-in-sql-azure-explained.aspx


Posted Jan 14 2012, 12:31 PM by Damir Dobric
Filed under: , ,
developers.de is a .Net Community Blog powered by daenet GmbH.