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
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:
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:
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