SQL Azure Federations are here. At least on my server…

Late tonight I tried something that I didn't think would work because @@version on SQL Azure didn't report anything different from last week. (Microsoft SQL Azure (RTM) – 11.0.1814.30 Nov 21 2011 16:46:09 Copyright (c) Microsoft Corporation). It didn't work last week. But it does work now. That's the last time I listen to @@version… 😉 This thing that works is…

CREATE FEDERATION foo (id bigint range);

That's right…SQL Azure federations are up and appear to be working. At least on my server. They do a rolling upgrade in SQL Azure, so they be not yet be in all servers. If you've missed reading about these in the recent past, Cihan Biyikoglu is one of the "fathers" of the SQL Azure Federation implementation, and he's written all about it, for months, in his blog. He was a special guest at SQLskills' week 4 immersion event that I gave in August and gave a talk there, mostly on federations. The CREATE FEDERATION DDL statement isn't available on the web yet, so that's where to get it to try it out.

Note: You can also do this on the new SQL Azure Management portion of the Windows Azure portal. Select a database on your SQL Azure server, choose "Manage" off the ribbon at the top. You'll need to sign on again. Choose the "Administration" entry at the bottom right, then on administration page, it's the "New" button on the left of the top riboon (whew, that was harder to describe than it was to do).

BIG NOTE: Since I get the impressions that federations will likely be cost items, THIS WILL LIKELY COST YOU MONEY. Don't say I didn't warn you, if it does. I don't know how much. The corollary is DROP FEDERATION foo;

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 (i.e. no built-in fan-out queries or joins between federated tables and tables that live outside the federation member). Good thing we all know what map-reduce means from the Dr David Dewitt talk at SQLPASS this year. Folks that are more familiar with data-dependent routing (a.k.a. data-directed routing) like to draw parallels to this technology and its use in scale-out. There's lots of information on data-directed routing, I even wrote about it in a whitepaper called "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005", once upon a time. And some folks like to liken federations to NoSQL variant that uses the, ah, SQL language.

Whatever you'd like to compare it to, if you want federations to work with your application, you'll need to design for it. You can start by thinking in terms of three different types of tables: federated tables, reference tables, and global tables. Federated tables must have the federation key as part of the table's key in the SQL Azure implementation. So if the federation key is a customer id, orders can be a federated table if you make customerid part of its key. You can even stretch it to order_details containing the composite orders key that contains customer id. I have a harder time thinking of the products table working that way.

If you want to join order_details and products, you can make products a reference table. A reference table lives in every federation member. When you split a federation into two members, both members contain a full copy of the products table. BTW, these copies are not automatically kept in sync, so you'd have to update all of them yourself. In case you're thinking as I was, we'll need to find out if Sync services for Azure will support reference tables in federation members right away.

Finally, you can have global tables, that don't know anything about the federation. These could be in the federation root (which keeps track of the federation member metadata but doesn't know about individual federated tables), or in a different SQL Azure database. Joins between federated tables on global tables, however, happen on the client/middle-tier.

So, if you're interested in using SQL Azure Federations, the first thing to do is to think about what would make a good federation key. And what type of table each of your tables would be; federated, reference, or global. Cihan probably was a blog entry about this somewhere.

Cheers. And a big congrats to Cihan, Michael, and the rest of the team.

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.