Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

How to: Connect to a Federation in Azure SQL Database

Updated: January 20, 2015

This topic describes how you can connect to a federation in Azure SQL Database. For general considerations about connecting to databases in SQL Database, see Guidelines for Connecting to Azure SQL Database Programmatically.

Establishing a connection to the federation’s data is accomplished by using the USE FEDERATION (Azure SQL Database) statement. All connections are established to the database containing the federation (Federation Root,) which then routes the connection to the appropriate federation member. The federation member that the connection is routed to is determined by the federation key value, which is specified as part of the USE FEDERATION statement.

WarningWarning
Federations will be retired with Web and Business service tiers in Azure SQL Database. (See Web and Business Edition Sunset FAQ.) Consider using Elastic Scale for Azure SQL Database to build a sharded, scale-out solution for your data tier. To try it, see Get Started with Azure SQL Database Elastic Scale Preview.

The following example is a console application that connects to a SQL Database with federation. The console application performs the following steps:

  1. First, the console application uses a SQLConnectionStringBuilder object to construct the connection string..

  2. Then the console application connects to federation root to retrieve and display the federation member data.

  3. Finally, the console application connects to a federation member and counts the rows in the federation member.

To use this example in Visual Studio with your SQL Database server, do the following steps:

  1. Open Visual Studio and create a new console application.

  2. Replace the code in the program file with the code from this example.

  3. Replace the value of the eight static data members.

  4. Run the code.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ConnectToFederationConsoleApp
{
    class Program
    {
        private static string federationName = "<ProvideFederationName>";
        private static string distributionName = "<ProvideDistributionName>";
        private static long federationKey = <ProvideAValue>;
        private static string tableName = "<ProvideTableName>";

        static void Main(string[] args)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
            csb.DataSource = dataSource;
            csb.InitialCatalog = databaseName;
            csb.Encrypt = true;
            csb.TrustServerCertificate = true;
            csb.UserID = userName;
            csb.Password = password;

            try
            {
                using (SqlConnection connection = new SqlConnection(csb.ToString()))
                {
                    connection.Open();

                    using (SqlCommand command = connection.CreateCommand())
                    {
                        // Route the connection to Federation Root
                        command.CommandText = "USE FEDERATION ROOT WITH RESET";
                        command.ExecuteNonQuery();

                        //Retrieve Federation Root metadata
                        command.CommandText = @"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, fmc.range_high";;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            Console.WriteLine("name\t\tfederation_id\tmember_id\trange_low\trange_high");
                            while (reader.Read())
                            {
                                Console.WriteLine(reader["name"] + "\t" + 
                                    reader["federation_id"] + "\t" + 
                                    reader["member_id"] + "\t" + 
                                    reader["range_low"] + "\t" + 
                                    reader["range_high"] + "\t");
                            }
                            Console.WriteLine();
                        }

                        // Route the connection to a federation member
                        command.CommandText = "USE FEDERATION " + federationName + "(" + distributionName + "=" + federationKey + ") WITH RESET, FILTERING = OFF";
                        command.ExecuteNonQuery();
                        command.CommandText = @"SELECT * FROM " + tableName;

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            int iRowCount = 0;
                            while (reader.Read())
                            {
                                iRowCount++;
                            }
                            Console.WriteLine("There are {0} rows in this federation member with the federation key value of {1}.", iRowCount, federationKey.ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine( String.Format("Error:\r\n{0}\r\n{1}\r\n{2}", ex.Source, ex.Message, ex.StackTrace));
            }

            Console.WriteLine("\nPress ENTER to continue ... ");
            Console.ReadLine();

        }
    }
}

See Also

Show:
© 2015 Microsoft