This content is part of the Essential Guide: Evolution of Windows Azure SQL Database
Problem solve Get help with specific problems with your technologies, process and projects.

Split root table with T-SQL in SQL Azure Federations

SQL Azure Federations can help manage massive amounts of data, says Roger Jennings. In Part 2 of this two-part series, read on for instructions on splitting the federation root table.

EDITOR’S NOTE: The following is the second article in a two-part series on managing Microsoft’s sharding technology SQL Azure Federations with Transact-SQL (T-SQL). Part 1 examined the use of T-SQL to create federated tables. This article discusses how to split the federation root member table and querying federated tables.

For more on SQL Azure

Why does SQL Azure terrify IT? Read this article to find out

SQL Azure is now “virtually” out; find out what that means for you

Why you shouldn’t sweat a migration from Sybase or Oracle to SQL Azure

The final step in the Transact-SQL (T-SQL) management sharding process in SQL Azure Federations is to split the federation root member table into five tables that have ID values from 6 to 2. To do that, we’ll use queries based on templates generated by menu choices. To enable generating templates for SQL Azure Federations, choose Tool and then Options to open the Options dialog box, expand the SQL Server Object Explorer node, select Scripting and change the value of the Script for Database Engine Type to SQL Azure Database (see Figure 1).

Figure 1. Specify scripting modifications for SQL Azure’s T-SQL dialect, which lacks support for several T-SQL reserved words.

Right-click the Federation Root node (AzureDiagnostics::WADFederation, for this example) and choose Split Federation Member to insert an Alter Federation … Split at template. Operations on the Federation Root node must be prefaced by a USE FEDERATION ROOT WITH RESET statement. Replace the second template command with the following statement (see Figure 2).


Figure 2. Start the federation member splitting process at the maximum Id value, 6 for this example.

Repeat this process four times, replacing 6 with 5, 4, 3 and 2 to create the five new federated tables. Although the “Command(s) completed successfully” message appears almost immediately after executing the query, you must wait a few minutes, depending on the size of the new table, for the split to complete and then reconnect to the Federation Root with ID = 1 before starting the next split. When you reconnect, the Federation Root’s argument will change to Id = LOW..n, where n = the ID value of the spit. Note that LOW ID values are inclusive and HIGH ID values are exclusive of the ID value.

Querying federated tables
Executing SELECT queries against federation members requires specifying the federation name, Distribution Key value, and FILTERING option in a USE FEDERATION statement before executing the SELECT statement, as in the following:


SELECT COUNT(*) FROM dbo.WADPerfCounters

The FILTERING=OFF option makes all rows in the database accessible; FILTERING=ON restricts the visibility of rows to the specified Id value for members that have a range of ID values.

Data uploading for this example was terminated at 490,000 rows, so each federated table contains 81,666 or 81,667 rows when you complete the split (see Figure 3).

Figure 3. Processing SELECT queries requires the USE FEDERATION statement with the Federation Id value as a parameter, followed by a WITH FILTERING={ON|OFF}, RESET modifier.

SQLAzureFedMW recognizes the federation tables and automatically uploads new rows to the appropriate table as designated by the CounterID value.

Running fan-out Queries 
Data-consuming applications need the capability to query across many or all federation members, as well as single federated tables. Querying across multiple federated tables requires fan-out queries, which SQL Azure Federations doesn’t yet implement natively. To fill this gap, Cihan Biyikoglu of the SQL Azure Federations team has provided a live Windows Azure Basic and Full Fan-out Query Utility application, which can be accessed from three Microsoft data center regions: North and South America, Europe and Asia.

Fan-out queries resemble MapReduce operations on Hadoop data sets because they can perform operations on two parts:

  1. A basic Member query, which is sent over to all members involved in the query
  2. An optional full Summary query, which performs post-processing operations to condense the results from the member query into the desired final result set

The following basic fan-out query produces the result shown partially in Figure 4 for the last three days of February and the first two days of March:

SELECT CounterID, CounterName, DATEPART(m, Timestamp) AS Month, DATEPART(d, Timestamp) AS Day, AVG(CounterValue) AS [Average Value]
FROM WADPerfCounters
GROUP BY CounterID, CounterName, DATEPART(m, Timestamp), DATEPART(d, Timestamp)
ORDER BY DATEPART(m, Timestamp), DATEPART(d, Timestamp)

Figure 4. Use the Federations Utility for SQL Azure to execute fan-out queries again SQL Azure Federations.

A second query can apply a HAVING clause to values produced by the first query or apply additional aggregate operations, as shown in Figure 5.

Figure 5. Full fan-out queries against SQL Azure Federations can apply HAVING clauses or execute aggregate functions in summary queries against the initial T-SQL member queries.

Support for SQL Azure Federations by SQL Server Management Studio 2012 and availability of the Federations Utility enable potential users of cloud-based SQL Azure to verify its newly gained horizontal scalability for big data management. Start tests with a single free 1 GB SQL Azure database in the Windows Azure three-month free trial and then take advantage of the SQL Azure Federation Data Migration Wizard and recently reduced SQL Azure pricing to move your big data to a pay-as-you go SQL Azure Federation in a Microsoft data center.


Roger Jennings is a.NET developer and writer, the principal consultant of OakLeaf Systems and curator of the OakLeaf Systems blog. He's the author of more than 30 books on the Windows Azure Platform, Microsoft operating systems, databases, .NET data access, Web services and InfoPath 2003.

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.