SQL Azure Federations – First Look

At Microsoft teched 2011 the SQL Azure Database Federations feature was announced and that the product evaluation program was now open for nominations. You can read more about the program here.

In this blog I wanted to touch on a couple of the enhancements in Transact SQL to support SQL Azure federations. There is no better way than an example with short excerpts for each step.

Create the Federation

A federation is a collection of database partitions that are defined by a federation scheme. To create the federation scheme you must execute the ‘CREATE FEDERATION’ command. In this example we have a federation named Visitor_Fed with a distribution name of range_id followed by a RANGE partition type of BIGINT. Only RANGE partitions are supported for this release. Range types can be BIGINT, UNIQUEIDENTIFIER OR VARBINARY(n) where n can be up to 900.

CREATE FEDERATION Visitor_Fed (range_id BIGINT RANGE)
GO

Connect to a Federation

Connection to a federation member is performed with the ‘USE FEDERATION’ statement. In the statement below we connect to the Visitor_Fed federation member with range_id distribution name equal to 0. This connects us to the first for now only federation in this example. The FILTERING=OFF option denotes that the connection is scoped to the federation member’s full range. Setting FILTERING=ON scopes the connection to a particular federation key instance within a federation member. The RESET keyword is required and is used explicitly reset the connection after use.

USE FEDERATION Visitor_Fed (range_id = 0)
WITH FILTERING = OFF, RESET
GO

Create a Table

The syntax to create a table has a new enhancement, the ‘FEDERATED ON’ clause which allows the table to be included in multiple federation members. Only one FEDERATE ON column is supported on any given table and it must refer the federation key. Another stipulation, all unique indexes on federated tables must contain the federation column, in this case the visitor_id is defined as the federated column and has a primary key associated with it.

CREATE TABLE visitor
(
    visitor_id BIGINT PRIMARY KEY,
    col2 varchar(10)
)
FEDERATED ON (range_id = visitor_id)
GO

Split a Federation

A federation member is split using the ‘ALTER FEDERATION name SPLIT AT’ command. In the example below, we connect to the root member and perform a SPLIT of the Visitor_Fed federation at the value 100, thus creating two federation members. We can see by querying the sys.federation_member_distributions DMV and from table 1 that we have a two members, member_id=65537 and member_id=65538. The ranges are aligned at a split value of 100 and thus we have a member from the min to 100 and another from 100 to max. A visitor_id value of 100 is included in the second member.

USE FEDERATION ROOT WITH RESET
GO

ALTER FEDERATION Visitor_Fed SPLIT AT (range_id = 100)
GO

federation_id	member_id	distribution_name	range_low	range_high
65536	65537	range_id	-9.2E+18	100
65536	65538	range_id	100	NULL

Table 1 Federation Member Distributions

Inserting/Querying

Connect to the appropriate federation member. Insert/delete/update/select as one would typically do if not utilizing federations.

USE FEDERATION Visitor_Fed (range_id = 0)
WITH FILTERING = OFF, RESET
GO

INSERT INTO visitor VALUES (1, 'visitor 1')
INSERT INTO visitor VALUES (2, 'visitor 2')
GO

USE FEDERATION Visitor_Fed (range_id = 100)
WITH FILTERING = OFF, RESET
GO

INSERT INTO visitor VALUES (100, 'visitor 100')
INSERT INTO visitor VALUES (101, 'visitor 101')
GO