In the previous post, I described the rationale for scale-out vs scale-up. Explained why scale-out was the best thing after sliced bread and also why specifically sharding or horizontal partitioned wins as the best pattern in the world of scale-out. I also talked about the advantages of using SQL Azure as the platform for building sharded apps.
Last week, we introduced the Federation concept in SQL Azure in this talk. In the next few posts, I’ll detail the upcoming concept and how to build applications using federations in SQL Azure. This will help you be ready when this technology ships in future versions of SQL Azure.
As the first post on the topic, lets first talk about how federations improve the life of the admin and the developer. Specifically we’ll attack the following common challenges that emerge from this pattern;
#1 Repartitioning and Redistributing Data: For the DBA managing applications with sharding pattern, repartition operation requires quite a lot of acrobatics. Federation make it easier to redistribute and repartition your data.
#2 Robust Connection Routing: For the devs building applications with sharding pattern, challenge is to put a routing layer that can handle repartitioning operations without requiring application downtime. Federation have built in routing support that provides full safety even when repartitioning operations are moving data around in the system.
First thing first; Introducing Federation in SQL Azure
To address some of these challenges above and more, we will be introducing federations in SQL Azure. Federation are key to understanding how scale-out will work. Here are the basic concepts;
Following figure shows how it all look when you put these concepts together. Sales_DB is the database that contains a federation (a.k.a the federation root). Federation called customer_federation is blown up to show you the details. Federation members contain ranges of atomic units (AU) such as [min to 100). AUs contain the collection of all data for the given federation key instance such as 5 or 25.
Repartitioning Operations with Federations
Federation provide operation for online repartitioning. SPLIT operation allows spreading of a federation members data (collection of atomic units) to many federation members. MERGE allows gluing back of federation members data together. This is exactly how federation address the challenge #1 above. With the SPLIT and MERGE operations administrators can simply trigger repartitioning operations on the server side and most importantly they can do this without downtime!
Connecting to Federations
Federation also allow connection to the federation’s data using a special statement; “USE FEDERATION federation_name(federation_key_value)”. All connections are established to the database containing the federation (a.k.a root database). However to reach a specific AU or a federation member, instead of requiring a database name, applications only need to provide the federation key value. This eliminates the need for apps to cache any routing or directory information when working with federations. Regardless of any repartitioning operation in the environment, apps can reliably connect to the AU or to the federation member that contain the given federation key value, such as tenant_id 55, simply by issuing USE FEDERATION orders_federation(55). Again the important part is, SQL Azure guarantees that you will always be connected to the correct federation member regardless of any repartitioning operation that may complete right as you are establishing a connection. This is how federations address the challenge #2 above.
Creating and Operating Federations
By the time we release the feature some of the syntax details may change but here is conceptually how you can set up a federation;
-- in master database
CREATE DATABASE sales_db
-- connect to the new sales_db, create the federation. this creates the first federation member.
CREATE FEDERATION orders_federation(c_id BIGINT RANGE)
-- connect to the federation member and deploy schema to the federation member
USE FEDERATION orders_federation(0) …
CREATE TABLE orders (…, customer_id BIGINT NOT NULL) FEDERATED ON (c_id = customer_id)
CREATE TABLE orders_details (…, customer_id BIGINT NOT NULL) FEDERATED ON (c_id = customer_id)
CREATE TABLE zipcodes (…)
-- When you want to scale out customer’s orders, connect to sales_db and SPLIT the federation data
ALTER FEDERATION orders_federation SPLIT AT(c_id=100)
-- When you want to scale in., connect to sales_db and MERGE the federation data
-- this may not be available in versionn1.
ALTER FEDERATION orders_federation MERGE AT (c_id=100)
Here is how the ado.net app connects and works with federation;
SqlConnection cn = new SqlConnection(“Server=tcp:servername.db.windows.net;Db=sales_db;UserID=uname;Password=…;”);
SqlCommand cm = new SqlCommand(“USE FEDERATION fed1(c_id=58) …”)
I’d love to hear feedback from everyone on federations. Please make sure to leave a note here or raise issues in the SQL Azure forums here.
Just looking at building Cloud BI solutions and this federation looks very interesting. I am really looking forwards to using it