Free Trial *Internet Service Required
2 out of 5 rated this helpful - Rate this topic

Windows Azure SQL Database Federations Tutorial -- Entity Framework

Windows Azure SQL Database Federations is a technology for building scale-out database solutions. Federations are objects in SQL Databases just like other objects such as tables and stored procedures but provide system managed databases to allow applications to scale out parts or all of their data. For more information on SQL Database Federations, see Federations in Windows Azure SQL Database (formerly SQL Azure). This tutorial demonstrates how you can use ADO.NET Entity Framework to access federated data and perform the split operation.

In this tutorial, you first use some scripts to create a database with federation and populate it with some sample data. You will then run a WinForm application to query the federation metadata and the federated data, and finally split federation members. The tutorial doesn't explain the code line by line. You can set breakpoints and use the debugger to step through the code yourself.

Prerequisites

Before you begin this tutorial, you must complete the steps in:

  1. Get a Windows Azure subscription. For more information, see Getting Started with Windows Azure SQL Database.

  2. Create a SQL Database server. For the instructions, see How to Create a Windows Azure SQL Database Server.

  3. Configure the server-level and database-level firewall settings. For the instructions, see How to: Configure the Server-Level Firewall Settings (Windows Azure SQL Database) and How to: Configure the Database-Level Firewall Settings (Windows Azure SQL Database). You must also open the 1433 outgoing port from your local firewall.

  4. Install Visual Studio 2010.

  5. Download and extract the tutorial sample from http://go.microsoft.com/fwlink/?LinkId=238246.

In this Tutorial

Create a Federation

SQL Database Federations Tutorial -- DBA has covered the details for creating a federation. In this tutorial, you will use scripts to create a federation.

  1. Open SetupFederation\CreateFederation.cmd from the folder where you extracted the sample in Notepad.

  2. Enter your server information and your account information (line 2 ~ 5). The default port is 1433, and the default federation root database name is AdventureWorks3.

  3. Save the file and close Notepad.

  4. Run SetupFederation\CreateFederation.cmd.

    The script performs the following operations:

    1. Create a federation root database, called AdventureWorks3.

    2. Create a federation, called CustomerFederation.

    3. Create a federated table, called Customer.

    4. Create a federated table, called CustomerAddress.

    5. Insert 800 records into the Customer table.

    6. Insert 400 records into the CustomerAddress table

  5. Make sure the last message is "Processed 400 total records". If you run into a problem, try repeating step 5 again.

Open the Visual Studio Solution

The tutorial provides an application to demonstrate using federation.

To open the Visual Studio solution

  1. Open Visual Studio 2010 as an administrator.

  2. From Visual Studio, browse to the folder where you extracted the sample, and then open CustomerFederation.sln.

To configure the connection string

  1. From Solution Explorer, expand CustomerFederation, right-click App.config, and then click Open.

  2. Modify the connectionString. You must replace [SQLAzureServerName] with the SQL Database server’s fully qualified domain name or IP address, [UserName] with your user name, [LogicalServerName] with your logical server name, and [Password] with your password.

To run the application in debugger

Press F5 to run the application in the debugger. This is a screenshot of the GUI:

SQL-Azure-Federation-Tutorial-Entity-Framework

The Metadata tab shows the federation metadata. The Customers tab shows the data in the database, the Customer and CustomerAddress tables. The Clean-up tab lists the databases of the federation.

View the Federation Metadata before the Split

Earlier in the tutorial, you ran a script to create a federation. In this section, you take a look of the federation metadata and the federated data within the federation members.

To view the Federation metadata

  1. Click the Clean-up tab.

  2. Click List Federated DBs. You should see two databases. One is the federation root. The default name is AdventureWorks3. And the other one with the name that begins with system- is the federation member created when the federation is created.

  3. Click the Metadata tab.

  4. Click Root. The Root button displays the federation root metadata.

    In the trace box, it shows:

    Copy 
    ------ 12/27/2011 10:57:01 PM : ----- Federation root metadata -----
-- Open Data Source=▪▪▪▪▪▪▪▪▪,1433;Initial Catalog=▪▪▪▪AdventureWorks3;User ID=▪▪▪▪@▪▪▪▪;Password=▪▪▪▪▪▪▪▪▪▪▪
USE FEDERATION ROOT WITH RESET
GO
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
GO
    From the trace log, you can see that it first connects to the federation root database. It then uses the USE statement to route the connection to the federation root. At the end, it executes a T-SQL statement to retrieve the metadata.

    In the DataGridView control, it lists one federation member with the range from long.MinValue to long.MaxValue (empty). When a federation is created, a federation member is also created. After a federation split operation, there will be two new federation members replacing the old federation member.

    noteNote
    You can test the T-SQL statements in the trace box in SQL Server Management Studio (SSMS). Make sure you specify AdventureWorks3 in the Available Database combo box.

  5. In Federated Key, select a value between long.MinValue and long.MaxValue. The default value is 100. Currently, there is only one federation member with the range from long.MinValue to long.MaxValue. You can choose any value within the range, and it will NOT affect the query results.

  6. Click Member. The Member button shows the metadata for the federation member with the federated key value chosen.

    In the trace box, it shows:

    Copy 
    ----- 12/27/2011 10:58:30 PM : ----- Federation member metadata -----
-- Open Data Source=▪▪▪▪▪▪▪▪▪;Initial Catalog=AdventureWorks3;User ID=▪▪▪▪@▪▪▪▪;Password=▪▪▪▪▪▪▪▪▪

USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
GO

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
GO
    From the trace log, you can see that it first connects to the federation root database. It then uses the USE statement to route the connection to the federation member. Notice "cid=100". It is the value specified in the Federated Key textbox. At the end, it executes a T-SQL statement to retrieve the metadata.

    In the DataGridView control, it lists the federation member with the range from long.MinValue to long.MaxValue. The information shown should be identical to the federation root metadata.

  7. In Federated Key, select a value between long.MinValue and long.MaxValue. The default value is 100.

  8. Click Get Row Counts. The Get Row Counts button displays the row counts for tables in one or more federation members. It performs a fan-out query. A fan-out query can traverse multiple federation members. It starts with the federation member with the federated key specified, and then checks the federation members with greater range values.

    In the trace box, it shows:

    Copy 
    ----- 12/27/2011 10:59:28 PM : ----- Get row counts -----
-- Open Data Source=▪▪▪▪▪▪▪▪▪;Initial Catalog=AdventureWorks3;User ID=▪▪▪▪@▪▪▪▪;Password=▪▪▪▪▪▪▪▪▪

USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
GO

SELECT '[' + s.name + '].[' + t.name + ']' [name],fmc.range_low,fmc.range_high,p.row_count FROM sys.federation_member_distributions fmc JOIN sys.federated_table_columns ftc ON fmc.distribution_name=ftc.distribution_name JOIN sys.tables t ON t.object_id=ftc.object_id  JOIN sys.schemas s ON t.schema_id=s.schema_id  JOIN sys.dm_db_partition_stats p ON t.object_id=p.object_id  WHERE p.index_id=1 ORDER BY s.name, t.name
GO

SELECT CAST(range_high as bigint) FROM sys.federation_member_distributions
GO

-- Done with Fan-Out

-- The number of Fan-Out federated Members is 1.
    From the trace log, you can see that it first connects to the federation root database. It then uses the USE statement to route the connection to the federation member. Notice "cid=100". This is used to determine the federation member that the fan out query begins with. The process is repeated for each federation member with a higher cid value until it reaches one that has a range high value of NULL. Since we currently have only one federation member, a count of all rows should be returned.

    In the DataGridView control, it lists the row count for the Customer table is 847, and the row count for the CustomerAddress table is 417.

View the Federated Data before the Split

In this section, you will take a look at the federated data.

  1. Click the Customers tab.

  2. In Federated Key, select a value in between long.MinValue and long.MaxValue. The default value is 100.

  3. Click Query. The Query button displays the data in the federation member with the federated key equals to the value you chose.

    In the trace box, it shows:

    Copy 
    ----- 12/27/2011 10:16:55 PM : ----- Query Federation Member data ------
-- EF Open name=AWEFEntities
-- EF ExecuteStoreCommand USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
-- EF Query [AWEFEntities].[Customers]
-- EF Query [AWEFEntities].[CustomerAddresses]
    From the trace log, you can see that it first connects to the federation root database. It then uses the USE statement to route the connection to the federation member. Notice "cid=100". It executes T-SQL statements to retrieve the federated data.

    In the DataGridView control, it lists the data from the Customer table, and the CustomerAddress table. There are 847 rows in the Customer table, and 417 rows in the CustomerAddress table, which match to the numbers you retrieved from the federation member metadata.

  4. From the top list, click CustomerID column head to sort the data. Notice the value is from 1 to 30118.

  5. From the bottom list, click CustomerID to sort the data. Notice the value is from 29485 to 30118. You will examine these numbers after you split the federation member.

  6. In Federated Key, select a value between long.MinValue and long.MaxValue. The default value is 100.

  7. Click Fan Out. The Fan Out button displays the federated data in one or more federation members. It performs a fan-out query. A fan-out query can traverse multiple federation members. It starts with the federation member with the federated key specified, and then check for the federation members with greater range values.

    In the trace box, it shows:

    Copy 
    ----- 12/27/2011 11:02:04 PM : ----- Fan-out federation members data ----
-- Open Data Source=▪▪▪▪▪▪▪▪▪;Initial Catalog=AdventureWorks3;User ID=▪▪▪▪@▪▪▪▪;Password=▪▪▪▪▪▪▪▪▪

-- EF Open name=AWEFEntities
-- EF ExecuteStoreCommand USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
-- EF Query [AWEFEntities].[Customers]
-- EF Query [AWEFEntities].[CustomerAddresses]
SELECT CAST(range_high as bigint) FROM sys.federation_member_distributions
GO

-- Done with fan-out.
-- The number of fan-out federated members is 1.
    From the trace log, you can see that it first connects to the federation root database. It then uses the USE statement to route the connection to the federation member. Notice "cid=100". It executes a T-SQL statement to retrieve the data. If the range high is not NULL, it repeats the process.

    In the DataGridView control, the data is the same as you clicked the Query button. It is because there is only one federation member.

Perform Federation Split

Federations provide an operation for online repartitioning. The split operation allows partitioning of a federation member data (collection of atomic units) to multiple federation members.

To split a Federation

  1. Click the Metadata tab.

  2. In Federated Key, choose 100. You will split the federation member into two federation members: one federation member will contain the records with the federated key value less than 100, and the other will contain the records with the federation value greater than or equal to 100.

  3. Click Split.

  4. Click OK to acknowledge the operation.

    In the trace box, it shows:

    Copy 
    ----- 12/27/2011 11:03:37 PM : ----- Split -----
-- Open Data Source=▪▪▪▪▪▪▪▪▪,1433;Initial Catalog=▪▪▪▪AdventureWorks3;User ID=▪▪▪▪@▪▪▪▪;Password=▪▪▪▪▪▪▪▪▪▪▪

USE FEDERATION ROOT WITH RESET
GO

ALTER FEDERATION CustomerFederation SPLIT AT (cid=100)
GO
    From the trace log, you can see that it first connects to the federation root database. It then uses the USE statement to route the connection to the federation root. Notice "cid=100". It executes the ALTER FEDERATION statement to split the federation member.

In the next section, you will take a look the federation metadata and the federated data after the split operation.

View the Federation Metadata after the Split

In this section, you re-examine the federation metadata and the federated data after the split operation.

To view the Federation metadata

  1. Click the Clean-up tab.

  2. Click List Federated DBs. You should see three databases. One is the federation root. The default name is AdventureWorks3. The other two have the names that begin with system-. These are the new federation member databases created by the SPLIT operation.

  3. Click the Metadata tab.

  4. Click Root. Before the split operation, there was one federation member with the range from long.MinValue to long.MaxValue. After the split operation, there are two federation members, one ranged from long.MinValue to 100, and the other ranged from 100 to NULL.

  5. In Federated Key, type 99 or any long type number less than 100, so you can get the metadata for the federation member with the lower range.

  6. Click Member. It lists the metadata for the one federation member. The range is from long.MinValue to 100.

  7. In the Federated Key, type 100 or any long type number greater or equal to 100, so you can get the metadata for the federation member with the higher range.

  8. Click Member. It lists the metadata for the one federation member. The range is from 100 to NULL.

  9. In Federated Key, type 100.

  10. Click Get Row Counts. The Get Row Counts does a fan-out query that traverses the federation members with ranges greater than the value specified. There is only one federation member that meets the criteria, the one with the higher range. You should see two rows, one for each of the tables.

    In the DataGridView control, it lists the row count for the Customer table is 784, comparing to 847 before the split operation, and the row count for the CustomerAddress table is 417, same as before the split operation because the CustomerAddress table does not contain any records with the CustomerID lower than 100.

  11. In Federated Key, change the value to 1.

  12. Click Get Row Counts. You should see 4 rows instead of 2 rows this time. The data was retrieved from two federation members. Before the split operation, there were 847 rows in the Customer table. After the split, the rows are repartitioned into two federation members.

View the Federated Data after the Split

In this section, you check the federated data after the split operation.

  1. Click Customers to switch to the Customers tab.

  2. In Federated Key, select 1 to query the data in the federation member with the lower range.

  3. Click Query. When you split the federation, you specified cid=100. The CustomerAddress table does not have records with cid lower than 100. So the CustomerAddress in this federation member does not contain any records. In the Customer table, the CustomerID is from 1 to 99.

  4. In Federated Key, select 100 to query the data in the federation member with the higher range.

  5. Click Query.

  6. From the top list, click CustomerID column head to sort the data. Notice the value is from 100 to 30118. Comparing data with before the split operation, the records with CustomerID from 1 to 99 are spread to another federation member.

  7. From the bottom list, click CustomerID to sort the data. Notice the value is from 29485 to 30118. It is the same as before the split operation.

  8. Select Filtering On.

  9. Click Query. When Filtering is turn on, you only get the record with cid=100 in the Customer table.

  10. Clear Filtering On.

  11. In Federated Key, select 100 to query the data in the federation member with cid=100.

  12. Click Fan Out. You only get the data from the federation member with the range from 100 to long.MaxValue.

  13. In Federated Key, select 99 to query the data in the federation member with cid=99.

  14. Click Fan Out. This time, you get the data from both federation members.

Drop the Federation

In this section, you delete the federation.

To drop the Federation

  1. Click the Metadata tab.

  2. Click Drop Federation.

  3. Click OK to acknowledge the activity.

    In the trace box, it shows:

    Copy 
    ----- 12/27/2011 11:05:40 PM : ----- Drop the federation -----
-- Open Data Source=▪▪▪▪▪▪▪▪▪,1433;Initial Catalog=AdventureWorks3;User ID=▪▪▪▪@▪▪▪▪;Password=▪▪▪▪▪▪▪▪▪▪▪

DROP FEDERATION [CustomerFederation]
GO
    The T-SQL statement for dropping a federation is DROP Federation.

To list databases

  1. Click the Clean-up tab.

  2. Click List Federated DBs. You should see the federation root database left. All of the federation members are dropped.

You have completed this tutorial. There is another tutorial for developers, using ADO.NET.

See Also

Did you find this helpful?
(1500 characters remaining)

Community Additions

ADD
© 2013 Microsoft. All rights reserved.
facebook page visit twitter rss feed newsletter