SQL Azure Federation Exercise

1) Create a Federation  Database

2) Working with Federated Tables from ADO.NET

note:Folks that are familiar with patitioning can think of federations as similar to partitioning, but with federations in SQL Azure you can't do SQL statements that cross federation members yet 

1)

/***********************************************************************************************Prerequisite:CREATE DATABASE Orders_FederationCreate a new Federation over the existing Database named Orders_Federation***********************************************************************************************/CREATE FEDERATION Orders_Federation (CustId UNIQUEIDENTIFIER RANGE)GO/***********************************************************************************************use the USE FEDERATION statement with a Federation Key value to establish a connection to a specific Federation Member, and set the FILTERING value in OFF to set the scope of the connection to the full-range of the Federation Member***********************************************************************************************/USE FEDERATION Orders_Federation (CustId = '00000000-0000-0000-0000-000000000000') WITH RESET, FILTERING = OFFGO/***********************************************************************************************Tables created with the FEDERATED ON property are Federated Tables.***********************************************************************************************/-- Create Products table (reference tables)CREATE TABLE Products(  ProductID int NOT NULL,  SupplierID int NOT NULL,  ProductName nvarchar(50) NOT NULL,  Price decimal(12,2) NOT NULL,  PRIMARY KEY(ProductId) )GO-- Create Customers tableCREATE TABLE Customers(  CustomerID uniqueidentifier NOT NULL,  CompanyName nvarchar(50) NOT NULL,  FirstName nvarchar(50),  LastName nvarchar(50),  PRIMARY KEY (CustomerId) )FEDERATED ON (CustId = CustomerID)GO-- Create Orders tablecreate table Orders (       CustomerId uniqueidentifier NOT NULL,       OrderId uniqueidentifier NOT NULL,       OrderDate datetime,       PRIMARY KEY (OrderId, CustomerId))FEDERATED ON (CustId = CustomerId)GO-- Create OrderItems tableCREATE TABLE OrderItems(  CustomerID uniqueidentifier NOT NULL,  OrderID uniqueidentifier NOT NULL,  ProductID int NOT NULL,  Quantity int NOT NULL,  PRIMARY KEY (OrderId, CustomerId, ProductId) )FEDERATED ON (CustId = CustomerId)GO /***********************************************************************************************INSERT DATA TO TABLES***********************************************************************************************/-- Insert sample dataINSERT INTO Products (ProductID, SupplierID, ProductName, Price)VALUES ( 386, 1001,    'Titanium Extension Bracket Left Hand',    5.25 )GOINSERT INTO Products (ProductID, SupplierID, ProductName, Price)VALUES ( 387, 1001,    'Titanium Extension Bracket Right Hand', 5.25 )GOINSERT INTO Products (ProductID, SupplierID, ProductName, Price)VALUES ( 388, 1001,    'Fusion Generator Module 5 kV',    10.50 )GOINSERT INTO Products (ProductID, SupplierID, ProductName, Price)VALUES ( 389, 1001,    'Bypass Filter 400 MHz Low Pass', 10.50 )GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'Van Nuys', 'Catherine', 'Abel')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'Abercrombie', 'Kim', 'Branch')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'Contoso', 'Frances', 'Adams')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'A. Datum Corporation', 'Mark', 'Harrington')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'Adventure Works', 'Keith', 'Harris')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'Alpine Ski House', 'Wilson', 'Pais')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'Baldwin Museum of Science', 'Roger', 'Harui')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'Blue Yonder Airlines', 'Pilar', 'Pinilla')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'City Power & Light', 'Kari', 'Hensien')GOINSERT INTO Customers (CustomerID, CompanyName, FirstName, LastName)VALUES (newid(), 'Coho Winery', 'Peter', 'Brehm')GODECLARE @orderId UNIQUEIDENTIFIERDECLARE @customerId UNIQUEIDENTIFIERSET @orderId = NewId()SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Hensien' and FirstName = 'Kari'INSERT INTO Orders (CustomerId, OrderId, OrderDate)VALUES (@customerId, @orderId, GetDate())INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)VALUES (@customerId, @orderId, 388, 4)SET @orderId = NewId()SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Harui' and FirstName = 'Roger'INSERT INTO Orders (CustomerId, OrderId, OrderDate)VALUES (@customerId, @orderId, GetDate())INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)VALUES (@customerId, @orderId, 389, 2)SET @orderId = NewId()SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Brehm' and FirstName = 'Peter'INSERT INTO Orders (CustomerId, OrderId, OrderDate)VALUES (@customerId, @orderId, GetDate())INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)VALUES (@customerId, @orderId, 387, 3)SET @orderId = NewId()SELECT @customerId = CustomerId FROM Customers WHERE LastName = 'Pais' and FirstName = 'Wilson'INSERT INTO Orders (CustomerId, OrderId, OrderDate)VALUES (@customerId, @orderId, GetDate())INSERT INTO OrderItems (CustomerID, OrderID, ProductID, Quantity)VALUES (@customerId, @orderId, 388, 1)GO/***********************************************************************************************QUERY TABLES***********************************************************************************************/-- Connect to the first FederationUSE FEDERATION Orders_Federation (CustId = '00000000-0000-0000-0000-000000000000') WITH RESET, FILTERING = OFFGOSELECT * FROM CustomersGOSELECT * FROM OrdersGOSELECT * FROM OrderItemsGOSELECT * FROM ProductsGO

2) 

1. GetDataSet
   1. Federation members are system managed databases that provide computational capacity to a part of federation’s data. An Application that uses a database with federation must be aware of this
      
2. FanOut
   1. retrieving all items of a federated table could lead to a Fan Out query, requesting data from multiple databases
      

private string GetUseFederationStatement(string federationName, string distributionName, Guid customerId, bool filteringOn)        {            return distributionName.ToUpper().CompareTo("ROOT") == 0            ? "USE FEDERATION ROOT WITH RESET"            : string.Format("USE FEDERATION {0} ({1}='{2}') WITH RESET, FILTERING = {3}", federationName, distributionName, customerId, (filteringOn ? "ON" : "OFF"));        }private DataSet FanOut(string sqlCommand)        {            Guid? federatedKey = Guid.Empty;            DataSet dataToReturn = null;            var sqlCommandText = sqlCommand;            using (var tempData = new DataSet())            {                do                {                    using (var data = new DataSet())                    {                        using (var connection = new SqlConnection(this.connectionString))                        {                            connection.Open();                            // Connection Routing to the specified Federated Member                            using (var command = connection.CreateCommand())                            {                                command.CommandText = this.GetUseFederationStatement(FederationName, DistributionName, (Guid)federatedKey, false);                                command.ExecuteNonQuery();                                // Gets DataSet                                using (var dataAdapter = new SqlDataAdapter(sqlCommandText, connection))                                {                                    dataAdapter.Fill(data);                                }                                // Merges data in temp dataset                                tempData.Merge(data);                                // Get next range high value from metadata                                command.CommandText = "SELECT CAST(range_high as uniqueidentifier) FROM sys.federation_member_distributions";                                var nextRange = command.ExecuteScalar();                                if (nextRange == DBNull.Value)                                {                                    federatedKey = null;                                }                                else                                {                                    federatedKey = Guid.Parse(nextRange.ToString());                                }                            }                        }                    }                }                while (federatedKey != null);                dataToReturn = tempData;            }            return dataToReturn;        }        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "Already using SqlParameters")]        private DataSet GetDataSet(string federationName, string distributionName, Guid customerId, bool filteringOn, string strSql, params SqlParameter[] parameters)        {            DataSet dataToReturn = null;            using (var temp = new DataSet())            {                var useFederationCommandText = this.GetUseFederationStatement(federationName, distributionName, customerId, filteringOn);                using (var connection = new SqlConnection(this.connectionString))                {                    connection.Open();                    using (var command = connection.CreateCommand())                    {                        command.CommandText = useFederationCommandText;                        command.ExecuteNonQuery();                    }                    using (var dataAdapter = new SqlDataAdapter())                    {                        using (var command = new SqlCommand())                        {                            foreach (var param in parameters)                            {                                command.Parameters.Add(param);                            }                            command.Connection = connection;                            command.CommandText = strSql;                            dataAdapter.SelectCommand = command;                            dataAdapter.Fill(temp);                        }                    }                }                dataToReturn = temp;            }            return dataToReturn;        }public Customer GetCustomerById(Guid customerId)        {            var customer = new Customer();            var customers = this.GetDataSet(FederationName, DistributionName, customerId, true, "select * from Customers");            if (customers.Tables.Count > 0)            {                var row = customers.Tables[0].AsEnumerable().ToList().FirstOrDefault();                customer.CustomerId = (Guid)row["CustomerId"];                customer.CompanyName = row["CompanyName"].ToString();                customer.FirstName = row["FirstName"].ToString();                customer.LastName = row["LastName"].ToString();            }            return customer;        }