23 out of 41 rated this helpful - Rate this topic

How to: Create a Federation in Windows Azure SQL Database

SQL Database with federation is a way to achieve greater scalability and performance from the database tier of your application through horizontal partition. One or more tables within a database with federation are split by row and portioned across multiple databases known as federation members. A federation is defined by a federation distribution scheme, or federation scheme. The federation scheme defines a federation distribution key, which determines the distribution of data to partitions within the federation. For more information on federation, see Federations in Windows Azure SQL Database (formerly SQL Azure).

Administrators can use Windows Azure Platform Management Portal or SQL Server Management Studio to create federation and perform related operations.

Create Federation Root Database

Federation root refers to the logical name of the database that houses federation object and is the central repository for information about distribution of scaled-out data. To create a federation root database, you first connect to the master database of a SQL Database Server, and then execute the CREATE DATABASE (Windows Azure SQL Database) T-SQL statement:

-- Create federation root database
CREATE DATABASE [database_name]

Create Federation and Federation Schema

Federation can be created in any database. A database can house many federations. To create federation schema, you first connect to the federation root database, and then execute the CREATE FEDERATION (Windows Azure SQL Database) T-SQL statement:

-- Create federation scheme
CREATE FEDERATION federation_name (distribution_name <data_type> RANGE)

The federation distribution key must be an INT, BIGINT, UNIQUEIDENTIFIER, or VARBINARY (up to 900 bytes) and specifies a range value. There can only be one federation scheme and one federation distribution key for a federation.

Connect to a Federation

Before you can create a table in a federation or perform the read and write operations, you must first connect to a federation member. The T-SQL statement to connect to a federation is USE FEDERATION (Windows Azure SQL Database).

To connect to the federation root, you first connect to the federation root database, and then use the following statement:

USE FEDERATION ROOT WITH RESET

To connect to a federation member, you also need to connect to the federation root database first, and then use the following statement:

USE FEDERATION federation_name (distribution_name = value)
    WITH FILTERING={ON|OFF}, RESET

Create a Federated Table

The FEDERATED ON clause of the CREATE TABLE (Windows Azure SQL Database) statement creates the table as a federated table within a SQL Database federation, and applies the distribution constraint (distribution_name) for the federation to the specified federation column within the table (column_name):

-- Route connection to federation member
USE FEDERATION federation_name(distribution_name = value) WITH RESET, FILTERING={ON|OFF}
GO
CREATE TABLE 
    [ schema_name . ] table_ame 
        ( { <column_definition> | <computed_column_definition> }
        [ < table_constraint> ] [ ,...n ] ) 
FEDERATED ON (distribution_name = column_name)
GO

View Federation Metadata

You can use the following statements to retrieve the federation root metadata after you have connected to the federation root database.

-- Route connection to federation root
USE FEDERATION ROOT WITH RESET
GO
-- View the Federation Root metadata
SELECT db_name() [db_name]
SELECT * FROM sys.federations
SELECT * FROM sys.federation_distributions
SELECT * FROM sys.federation_member_distributions ORDER BY federation_id, range_low;
GO

Perform Federation Split Operation

When a database has reached its size limit or you want to improve performance, you can split a federation using the ALTER FEDERATION (Windows Azure SQL Database) statement:

--Perform the federation SPLIT operation
USE FEDERATION ROOT WITH RESET
GO
ALTER FEDERATION federation_name SPLIT AT (boundery_value)
GO

Example

The following code example demonstrates the process of creating a database with federation.

First connect to the master database, and then use the following T-SQL to create the federation root database:


-- Create Federation Root
CREATE DATABASE [AdventureWorks] 
GO

Next, connect to the federation root database, and then use the following T-SQL statements to create the federation scheme, and insert some test data:

-- Create Federation scheme
CREATE FEDERATION CustomerFederation(cid BIGINT RANGE)
GO

-- Route connection to the one Federation Member
USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
GO
-- Table [dbo].[Customer]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [bigint] NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NOT NULL,
[Suffix] [nvarchar](10) NULL,
[CompanyName] [nvarchar](128) NULL,
[SalesPerson] [nvarchar](256) NULL,
[EmailAddress] [nvarchar](50) NULL,
[Phone] [nvarchar](25) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[CustomerID] ASC
) 
)FEDERATED ON (cid=CustomerID)
GO

-- Route connection to the Federation Member
USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
GO
-- Insert data
INSERT INTO [dbo].[Customer]
           ([CustomerID]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[CompanyName]
           ,[SalesPerson]
           ,[EmailAddress]
           ,[Phone])
     VALUES
(56, 'Mr.', 'Brian', '', 'Groth', '', 'Latest Accessories Sales', 'adventure-works\david8', 'brian5@adventure-works.com', '461-555-0118'),
(57, 'Ms.', 'Judy', 'R.', 'Lundahl', '', 'Leading Sales & Repair', 'adventure-works\jillian0', 'judy1@adventure-works.com', '260-555-0130'),
(58, 'Mr.', 'Peter', '', 'Kurniawan', '', 'Largest Bike Store', 'adventure-works\jillian0', 'peter4@adventure-works.com', '436-555-0160'),
(59, 'Mr.', 'Douglas', '', 'Groncki', '', 'Locks Company', 'adventure-works\shu0', 'douglas2@adventure-works.com', '385-555-0140'),
(60, 'Mr.', 'Sean', 'J.', 'Lunt', '', 'Main Bicycle Services', 'adventure-works\linda3', 'sean4@adventure-works.com', '183-555-0111'),
(61, 'Mr.', 'Jeffrey', '', 'Kurtz', '', 'Many Bikes Store', 'adventure-works\shu0', 'jeffrey3@adventure-works.com', '452-555-0179'),
(64, 'Mr.', 'Vamsi', '', 'Kuppa', '', 'Metal Clearing House', 'adventure-works\jos‚1', 'vamsi1@adventure-works.com', '290-555-0196'),
(65, 'Ms.', 'Jane', 'P.', 'Greer', '', 'Metro Manufacturing', 'adventure-works\jos‚1', 'jane2@adventure-works.com', '737-555-0163'),
(66, 'Mr.', 'Alexander', 'J.', 'Deborde', '', 'Neighborhood Store', 'adventure-works\garrett1', 'alexander1@adventure-works.com', '394-555-0176'),
(70, 'Mr.', 'Deepak', '', 'Kumar', '', 'Outdoor Aerobic Systems Company', 'adventure-works\jae0', 'deepak0@adventure-works.com', '1 (11) 500 555-0122'),
(73, 'Ms.', 'Margaret', 'T.', 'Krupka', '', 'Outdoor Sports Supply', 'adventure-works\pamela0', 'margaret1@adventure-works.com', '107-555-0132'),
(74, 'Mr.', 'Christopher', 'M.', 'Bright', '', 'Parcel Express Delivery Service', 'adventure-works\david8', 'christopher2@adventure-works.com', '162-555-0166'),
(75, 'Ms.', 'Aidan', '', 'Delaney', '', 'Paint Supply', 'adventure-works\jillian0', 'aidan0@adventure-works.com', '358-555-0188'),
(76, 'Mr.', 'James', 'J.', 'Krow', '', 'Out-of-the-Way Hotels', 'adventure-works\jillian0', 'james11@adventure-works.com', '265-555-0190'),
(77, 'Mr.', 'Michael', '', 'Brundage', '', 'Mechanical Products Ltd.', 'adventure-works\shu0', 'michael13@adventure-works.com', '128-555-0148'),
(78, 'Mr.', 'Stefan', '', 'Delmarco', '', 'Preferred Bikes', 'adventure-works\linda3', 'stefan0@adventure-works.com', '819-555-0186'),
(79, 'Mr.', 'Mitch', '', 'Kennedy', '', 'Reasonable Bicycle Sales', 'adventure-works\shu0', 'mitch0@adventure-works.com', '996-555-0192'),
(82, 'Mr.', 'James', 'D.', 'Kramer', '', 'Refined Department Stores', 'adventure-works\jos‚1', 'james10@adventure-works.com', '814-555-0130'),
(83, 'Mr.', 'Eric', 'J.', 'Brumfield', '', 'Requisite Part Supply', 'adventure-works\jos‚1', 'eric3@adventure-works.com', '644-555-0114'),
(84, 'Ms.', 'Della', 'F.', 'Demott Jr', '', 'Rewarding Activities Company', 'adventure-works\garrett1', 'della0@adventure-works.com', '752-555-0185'),
(88, 'Ms.', 'Pamala', 'M.', 'Kotc', '', 'Closest Bicycle Store', 'adventure-works\jae0', 'pamala0@adventure-works.com', '1 (11) 500 555-0173'),
(91, 'Ms.', 'Joy', 'R.', 'Koski', '', 'Scooters and Bikes Store', 'adventure-works\pamela0', 'joy0@adventure-works.com', '810-555-0198'),
(92, 'Ms.', 'Jovita', 'A.', 'Carmody', '', 'Sports Commodities', 'adventure-works\david8', 'jovita0@adventure-works.com', '646-555-0137'),
(93, 'Mr.', 'Prashanth', '', 'Desai', '', 'Stationary Bikes and Stands', 'adventure-works\jillian0', 'prashanth0@adventure-works.com', '138-555-0156'),
(94, 'Mr.', 'Scott', '', 'Konersmann', '', 'Specialty Sports Store', 'adventure-works\jillian0', 'scott6@adventure-works.com', '556-555-0192'),
(96, 'Ms.', 'Jane', 'N.', 'Carmichael', '', 'Rural Department Store', 'adventure-works\shu0', 'jane0@adventure-works.com', '716-555-0167'),
(97, 'Ms.', 'Bonnie', 'B.', 'Lepro', '', 'More Bikes!', 'adventure-works\linda3', 'bonnie2@adventure-works.com', '354-555-0130'),
(99, 'Mr.', 'Eugene', '', 'Kogan', '', 'Mountain Bike Center', 'adventure-works\shu0', 'eugene2@adventure-works.com', '136-555-0134'),
(100, 'Mr.', 'Kirk', 'T', 'King', '', 'Up-To-Date Sports', 'adventure-works\jos‚1', 'kirk2@adventure-works.com', '979-555-0163');

GO

Use the following T-SQL statements to perform a split operation:


--Perform Federations SPLIT operation
USE FEDERATION ROOT WITH RESET
GO
ALTER FEDERATION CustomerFederation SPLIT AT (cid=80)
GO

Community Additions

ADD