SQL Azure Federations (Because you have a lot of data)

SQL-Azure_rgbLast week I explained SQL Azure Essentials for the Database Developer where I covered some of the differences between working in SQL Azure instead of an on-premise database from the perspective of a SQL Server database developer. Today I will continue in that vein and explain one of the upcoming features for SQL Azure: Federations.

When you first create a SQL Azure database, you are asked if you want to create a Web or a Business Database. A web database can have a maximum size of 1GB or 5 GB.  A Business database can be up to 50 GB. But in this day and age 50 GB is not that big. There must be a way to use SQL Azure for databases larger than 50 GB! One of the great benefits of cloud computing is the ability to handle peak loads and peak volumes by increasing your usage as needed. We can do that with Windows Azure using the pay as you go model, but how do we get that same sort of flexibility for our database in SQL Azure? The answer is federations! With federations you can expand and contract the number of nodes that service the database. Your application can scale to have 10 to 100s of SQL Azure databases and provide you with data storage beyond the limits of a single SQL Azure database.

The concept is very similar to partitioned tables. When you set up a partitioned table in SQL Server, you choose one of the columns on the table to use to partition the data into different data files. For example you could partition an order table based on country id. All orders with country ids of 1 or 2 go into partition A, all orders with country ids between 3 and 9 go into partition B, all orders with country ids from 10 to 20 go into partition C and so on.

You would end up with something like this

OrderId CustomerId OrderTotal OrderDate CountryId Country Partition
100 402 500.00 1-Jan-2010 1 Canada A
101 251 300.25 1-Jan-2010 1 Canada A
102 406 199.99 1-Jan-2010 5 England B
103 406 50.00 1-Jan-2010 5 England B
104 397 75.00 1-Jan-2010 6 France B
105 216 50.00 1-Jan-2010 2 USA A
106 411 19.99 1-Jan-2010 10 China C
107 203 315.00 2-Jan-2010 2 USA B
108 87 100.00 2-Jan-2010 10 China C

We usually partition a table in a on-premise SQL Server database to speed up queries (since they may only need to search through one partition and data file depending on the query), or to provide more backup options since you can back up one partition at a time. We can also merge two partitions together or split a single partition into two partitions using the SPLIT and MERGE statements.

In SQL Azure, you create one or more Federations in your database. A federation represents all the data being partitioned and contains one or more federation members or shards (The federation member is like a partition in a partitioned table.) The federation members are stored across different nodes.

So if we had a table called Orders in SQL Azure and we wanted to federate that table we would do the following

1. Create a federation in our SQL Azure database and specify we want to be able to divide up our records based on a range of integer values

CREATE FEDERATION order_federation (c_id RANGE BIGINT)

2. Create the tables we want to partition in our federation. For each table we must specify which column contains the integer values we should use to divide up the records across federation members

USE FEDERATION order_federation

CREATE TABLE orders (orderid INT NOT NULL, country_id BIGINT NOT NULL, …) FEDERATED ON (c_id = country_id)

3. Split our federation into federation members

ALTER FEDERATION order_federation SPLIT AT (c_id=3)

ALTER FEDERATION order_federation SPLIT AT (c_id=10)

This will split all records with a country id of 1 or 2 into one federation member, and all records with a country id of 3 to 9 into a second federation member and all records with a country id of 10 or higher into a third federation. You can continue to use split commands to create as many federation members as you want.

All the data is still available as you split and merge federation members!

Now what’s truly powerful about this concept on SQL Azure is that the federation scales out across multiple nodes which gives your virtually unlimited scalability in your application. You can build applications that scale from 10s to 100s of SQL Azure databases. You can also change this on demand as needed because you can repartition without downtime.

Federation Technology will be available in the final quarter of calendar year 2011 in all SQL Azure geographies.

Todays My 5

5 Places to learn more about SQL Azure federations

  1. SQL Azure Federations: Building Scalable, Elastic, and Multi-tenant Database Solutions
  2. Cihan Biyikoglu’s Blog post Building Scalable Database Solution with SQL Azure – Introducing Federation in SQL Azure
  3. Recording from PDC Building Scale-Out Database Solutions on SQL Azure
  4. Recording from TechEd 2011 Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations
  5. Building Scalable Database Solutions Using SQL Azure – Scale-out techniques such as Sharding or Horizontal Partitioning
    This post is also on the Canadian Solution Developer blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

<span>%d</span> bloggers like this: