SQL Database Federations Tutorial -- DBA

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

• Get a Windows Azure subscription. For more information, see Getting Started with Windows Azure SQL Database.
  
  
• Create a SQL Database server. For the instructions, see How to Create a Windows Azure SQL Database Server.
  
  
• Configure the SQL Database server firewall. For the instructions, see How to: Configure the Server-Level Firewall Settings (Windows Azure SQL Database). You must also open the 1433 outgoing port from your local firewall.
  
  
• Install SQL Server Management Studio (SQL Server 2008 R2), or SQL Server Management Studio Express.
  
  

1. Connect to a SQL Database Server
   
   
2. Create a Federation Root Database
   
   
3. Create the Federation Scheme
   
   
4. Create and Populate a Table in the Federation
   
   
5. Perform the Federation Split Operation
   
   
6. Drop the Federation
   
   

This tutorial provides you the instructions using SSMS. The other option is to use the Management Portal for Windows Azure SQL Database .

To connect to a SQL Database server

1. Click Start, point to All Programs, point to SQL Server 2008 R2, and then click SQL Server Management Studio.
   
   
2. In the Connect to Database Engine dialog, type or select the following values:
   
   

    

   Name	Value
   Server name	<ProvideServerName>
   Authentication	SQL Server Authentication
   Login	<ProvideUserID>
   Password	<ProvidePassword>

3. Click Connect.
   
   

To create a federation, you first create a database serving as the federation root database. The federation root is the initial database that houses federation directories.

To create a federation root database

1. In Object Explorer, expand Databases, expand System Databases, and then click master.
   
   
2. Click New Query.
   
   
3. Verify Available databases on the SQL Editor tool bar shows master.
   
   
4. In the Query Editor, copy and paste the following T-SQL statements:
   
   
   Copy

   -- Create federation root database
   CREATE DATABASE [AdventureWorks1] 
      (EDITION='Web', MAXSIZE=1GB) 
   GO
   
   

   Note: To create a database larger than 1GB, you must explicitly define the MAXSIZE.
   
   
5. From the SQL Editor command bar, click Execute. Ensure the Messages pane shows Command(s) completed successfully.
   
   
6. From Object Explorer, right-click Databases, and then click Refresh. You shall see the new database listed there.
   
   

A federation is a collection of database partitions that are defined by a federation scheme. You can create multiple federations within one federation root, however each federation can have only one scheme. To create the federation scheme you must first connect to the federation root database, and then execute the CREATE FEDERATION command. In this tutorial you create a federation named CustomerFederation with a distribution name of cid followed by a RANGE partition type of BIGINT. Only RANGE partitions are supported for this release. Range types can be INT, BIGINT, UNIQUEIDENTIFIER OR VARBINARY(n) where n can be up to 900.

To create the federation scheme

1. From SSMS, click anywhere inside the Query Editor (the middle pane). You can see the buttons on the SQL Editor toolbar become enabled.
   
   
2. In Available Databases, select the federation root you created in the last procedure. The default name is AdventureWorks1.
   
   
3. Clear all of the text in the Query Editor.
   
   
4. Copy and paste the following statements into the Query Editor:
   
   
   Copy

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

5. From the SQL Editor command bar, click Execute. And make sure the Messages pane shows "Command(s) completed successfully".
   
   
6. From Object Explorer, right-click Databases, and then click Refresh. You will see a new database with a name that begins with system-. When you create a federation, it creates the federation objects, including an initial federation member database.
   
   

To better understand the scheme, you can connect to the federation root and the federation member to take a look at the metadata.

To view the federation root metadata

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

   -- Route connection to the 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
   
   

4. From the SQL Editor command bar, click Execute.
   
   
   
   The USE statement for federations changes the connection to either the federation root or one of federation members. The USE statement above routes the connection to the federation root.
   
   The first SELECT statement shows the federation root database name. There is one federation, and one federation member. The first federation member shares the same ID as the federation. federation members created by a SPLIT operation will have new unique federation IDs.
   
   Notice the range of the federation member is from -9223372036854775808 (range_low) to NULL (range_high). When you perform a SPLIT operation, the range will be split into two parts. There is one federation distribution, named cid. Later, you will match the federation key to the federation distribution.
   
   

To view the federation member metadata

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

   -- Route connection to the one federation member (aka shard)
   USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
   GO
   -- View the federation member metadata
   SELECT db_name() [db_name]
   SELECT * FROM sys.federations
   SELECT * FROM sys.federation_distributions
   SELECT * FROM sys.federation_member_distributions
   -- Federatation ranges
   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;
   GO
   
   

   Note: The FILTERING=ON clause allows connecting to a specific atomic unit. The FILTERING=OFF clause allows connection to a federation member that contains the specific key value in its range.
   
   
4. From the SQL Editor command bar, click Execute.
   
   
   
   The USE statement routes the connection to the only federation member. The cid=100 is a value within the range. You can replace it with any value in the range of valid BIGINTs.
   
   Notice the database name starts with "system_" and a GUID number. Every time you perform a SPLIT operation, two new federation members are created to replace the existing federation member. For more information of the SPLIT operation, see Federation Repartitioning Operations: Database SPLIT in Action.
   
   Note: The current build does not drop the old federation member after the SPLIT operation but does remove it from the federation.
   
   

So far, you have created a federation. The next step is to create a Federated table, insert data into the table and perform a SPLIT operation.

Before you create a Federated table, you must route the connection to a federation member. The CREATE FEDERATION statement you executed earlier created one federation member.

To create a table in the federation

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root database.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

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

4. From the SQL Editor command bar, click Execute. And make sure the Messages pane shows Command(s) completed successfully.
   
   

In the USE statement, the cid=100 is a value within the range of BIGINTs. In the CREATE TABLE statement, you map the CustomerID to the distribution key.

To populate the table

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root database.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

   -- 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'),
   (101, 'Mr.', 'William', 'J.', 'Conner', '', 'Urban Sports Emporium', 'adventure-works\jos‚1', 'william1@adventure-works.com', '383-555-0111'),
   (102, 'Ms.', 'Linda', '', 'Leste', '', 'National Manufacturing', 'adventure-works\garrett1', 'linda7@adventure-works.com', '493-555-0134'),
   (106, 'Ms.', 'Andrea', 'A.', 'Thomsen', '', 'West Side Mart', 'adventure-works\jae0', 'andrea1@adventure-works.com', '1 (11) 500 555-0120'),
   (109, 'Mr.', 'Daniel', 'P.', 'Thompson', '', 'Travel Sports', 'adventure-works\pamela0', 'daniel2@adventure-works.com', '247-555-0197'),
   (110, 'Ms.', 'Kendra', 'N.', 'Thompson', '', 'Vintage Sport Boutique', 'adventure-works\david8', 'kendra0@adventure-works.com', '464-555-0188'),
   (111, 'Mr.', 'Scott', 'A.', 'Colvin', '', 'Westside Cycle Store', 'adventure-works\jillian0', 'scott1@adventure-works.com', '119-555-0144'),
   (168, 'Sr.', 'Luis', '', 'Bonifaz', '', 'Economy Bikes Company', 'adventure-works\linda3', 'luis0@adventure-works.com', '688-555-0113'),
   (169, 'Ms.', 'Brenda', '', 'Diaz', '', 'Downtown Hotel', 'adventure-works\shu0', 'brenda2@adventure-works.com', '147-555-0192'),
   (172, 'Ms.', 'Gabriele', '', 'Dickmann', '', 'Certified Sports Supply', 'adventure-works\jos‚1', 'gabriele0@adventure-works.com', '835-555-0116'),
   (173, 'Mr.', 'Rudolph', 'J.', 'Dillon', 'Sr.', 'Sundry Sporting Goods', 'adventure-works\jos‚1', 'rudolph0@adventure-works.com', '722-555-0169'),
   (174, 'Mr.', 'Michael', 'L.', 'Bohling', '', 'Toy Manufacturing Inc', 'adventure-works\garrett1', 'michael12@adventure-works.com', '838-555-0147'),
   (178, 'Mr.', 'Dick', '', 'Dievendorff', '', 'Rampart Amusement Company', 'adventure-works\jae0', 'dick1@adventure-works.com', '1 (11) 500 555-0193'),
   (180, 'Ms.', 'Nicky', 'E.', 'Chesnut', '', 'Full-Service Bike Store', 'adventure-works\michael9', 'nicky0@adventure-works.com', '264-555-0164'),
   (181, 'Mr.', 'Michael', 'J.', 'Lee', '', 'Family Entertainment Center', 'adventure-works\pamela0', 'michael18@adventure-works.com', '396-555-0139'),
   (182, 'Mr.', 'Stanley', 'A.', 'Alan', 'Jr.', 'Another Bicycle Company', 'adventure-works\david8', 'stanley0@adventure-works.com', '156-555-0126'),
   (183, 'Mr.', 'Yao-Qiang', '', 'Cheng', '', 'This Area Sporting Goods', 'adventure-works\jillian0', 'yao-qiang0@adventure-works.com', '344-555-0181'),
   (184, 'Ms.', 'Marjorie', 'M.', 'Lee', '', 'Go-cart and Bike Specialists', 'adventure-works\jillian0', 'marjorie0@adventure-works.com', '306-555-0166'),
   (185, 'Mr.', 'Sandeep', '', 'Kaliyath', '', 'Weekend Bike Tours', 'adventure-works\shu0', 'sandeep1@adventure-works.com', '495-555-0113'),
   (186, 'Mr.', 'Pei', '', 'Chow', '', 'Thrilling Bike Tours', 'adventure-works\linda3', 'pei0@adventure-works.com', '789-555-0184'),
   (187, 'Mr.', 'Frank', '', 'Campbell', '', 'Trailblazing Sports', 'adventure-works\shu0', 'frank4@adventure-works.com', '491-555-0132'),
   (190, 'Mr.', 'Mark', '', 'Lee', '', 'Racing Partners', 'adventure-works\jos‚1', 'mark5@adventure-works.com', '371-555-0112'),
   (191, 'Ms.', 'Sandra', 'T.', 'Kitt', '', 'Non-Slip Pedal Company', 'adventure-works\jos‚1', 'sandra2@adventure-works.com', '303-555-0117'),
   (192, 'Mr.', 'Hao', '', 'Chen', '', 'Nuts and Bolts Mfg.', 'adventure-works\garrett1', 'hao1@adventure-works.com', '117-555-0173'),
   (196, 'Ms.', 'Jolie', '', 'Lenehan', '', 'Exhibition Showroom', 'adventure-works\jae0', 'jolie0@adventure-works.com', '1 (11) 500 555-0126'),
   (197, 'Ms.', 'Diane', 'F.', 'Krane', '', 'Fabrikam Inc., West', 'adventure-works\michael9', 'diane4@adventure-works.com', '224-555-0126'),
   (198, 'Mr.', 'Payton', 'P.', 'Benson', '', 'Field Trip Inc', 'adventure-works\michael9', 'payton0@adventure-works.com', '528-555-0183'),
   (199, 'Mr.', 'Roger', '', 'Lengel', '', 'Authorized Bike Sales and Rental', 'adventure-works\pamela0', 'roger1@adventure-works.com', '947-555-0143'),
   (200, 'Ms.', 'Peggy', 'J.', 'Justice', '', 'Basic Bike Company', 'adventure-works\david8', 'peggy0@adventure-works.com', '170-555-0189'),
   (201, 'Ms.', 'Edna', 'J.', 'Benson', '', 'Unsurpassed Bikes', 'adventure-works\jillian0', 'edna0@adventure-works.com', '789-555-0189'),
   (202, '', 'A.', 'Francesca', 'Leonetti', '', 'Two-Seater Bikes', 'adventure-works\jillian0', 'a0@adventure-works.com', '645-555-0193'),
   (203, 'Ms.', 'Jean', '', 'Jordan', '', 'Separate Parts Corporation', 'adventure-works\shu0', 'jean3@adventure-works.com', '207-555-0129'),
   (204, 'Mr.', 'Ido', '', 'Ben-Sachar', '', 'Traction Tire Company', 'adventure-works\linda3', 'ido0@adventure-works.com', '973-555-0112'),
   (205, 'Mr.', 'Frank', '', 'Martnez', '', 'Rally Master Company Inc', 'adventure-works\shu0', 'frank5@adventure-works.com', '171-555-0147'),
   (208, 'Mr.', 'Steve', '', 'Masters', '', 'Professional Cyclists', 'adventure-works\jos‚1', 'steve2@adventure-works.com', '154-555-0115'),
   (209, 'Mr.', 'Robert', 'E.', 'Jones', '', 'Purple Bicycle Company', 'adventure-works\jos‚1', 'robert6@adventure-works.com', '595-555-0131'),
   (210, 'Mr.', 'Josh', '', 'Barnhill', '', 'Gasless Cycle Shop', 'adventure-works\garrett1', 'josh0@adventure-works.com', '584-555-0192');
   GO
   
   

4. From the SQL Editor command bar, click Execute. And make sure the Messages pane shows "(65 row(s) affected)".
   
   

To query the federated data

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root database.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

   -- Route connection to the 1 federation member
   USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
   GO
   -- Query customer table for high/low Federated Keys
   SELECT MIN(CustomerID) [CustomerID Low], MAX(CustomerID) [CustomerID High] FROM Customer
   select count(*) from Customer
   SELECT * FROM Customer order by customerid
   GO
   
   

4. From the SQL Editor command bar, click Execute.
   
   

The range of CustomerID is from 56 to 210. There are 65 records total in the Customer table.

Federations provide an operation for online repartitioning. Using the SPLIT operation, you can partition one federation member across multiple federation members. The SPLIT operation can be done without database downtime. To split a federation member, you must first connect to the federation member, and then use the ALTER FEDERATION command. You must specify at where you want to split the federation member. In the following sample, cid=100 is used. If you want, you can replace the number with another number.

To split a federation member

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root database.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

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

4. From the SQL Editor command bar, click Execute. And make sure the Messages pane shows Command(s) completed successfully.
   
   
5. From Object Explorer, right-click Databases, and then click Refresh. You shall see the old federation member database was replaced with two new databases with the name starting with "system-". The SPLIT operation partitioned the data across the two federation members.
   
   

To view the federation root metadata

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root database.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

   -- Route connection to the federation root database
   USE FEDERATION ROOT WITH RESET
   GO
   SELECT * FROM sys.federation_member_distributions ORDER BY federation_id, range_low;
   GO
   
   

4. From the SQL Editor command bar, click Execute. You will see 2 records in the federation_member_distributions table, one for each of the federation members.
   
   

To view a federation member metadata

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root database.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

   -- Route connection to the first federation member
   USE FEDERATION CustomerFederation(cid=0) WITH RESET, FILTERING=OFF
   GO
   SELECT db_name() [db_name]
   SELECT * FROM sys.federation_member_distributions
   -- Query customer table for high/low Federated Keys
   SELECT MIN(CustomerID) [CustomerID Low], MAX(CustomerID) [CustomerID High] FROM Customer
   GO
   
   

4. From the SQL Editor command bar, click Execute. The database name is different from the database before the SPLIT operation. The CustomerID Low is 56, and the CustomerID High is 99, if you used cid=0.
   
   

To view the other federation member metadata

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root database.
   
   
3. Copy and paste the following statements into the Query Editor:
   
   
   Copy

   -- Route connection to the second federation member
   USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
   GO
   SELECT db_name() [db_name]
   SELECT * FROM sys.federation_member_distributions
   -- Query customer table for high/low Federated Keys
   SELECT MIN(CustomerID) [CustomerID Low], MAX(CustomerID) [CustomerID High] FROM Customer
   GO
   
   

4. From the SQL Editor command bar, click Execute. Make sure the Messages pane shows Query executed successfully. The CustomerID Low is 100 and the CustomerID High is 210.
   
   

To drop a federation and its associated databases, you use the DROP FEDERATION statement.

To drop the federation

1. Clear all of the text in the Query Editor.
   
   
2. Verify Available Databases shows the federation root database.
   
   
3. In the Query Editor, copy and paste the following T-SQL statements:
   
   
   Copy

   USE FEDERATION ROOT WITH RESET
   GO
   DROP FEDERATION CustomerFederation
   GO
   
   

4. From the SQL Editor command bar, click Execute.
   
   
5. From Object Explorer, right-click Databases, and then click Refresh. You shall see the federation root database, and the federation members are dropped.
   
   

You have completed this tutorial. There are two more tutorials for developers, one uses ADO.NET, and the other uses ADO.NET Entity Framework (EF).

• Windows Azure SQL Database Federations Tutorial -- ADO.NET
  
  
• Windows Azure SQL Database Federations Tutorial -- Entity Framework
  
  

Concepts