Your Data in the Cloud

Database Scaling Done Right!

Cihan Biyikoglu - SQL Azure — Thu, 24 May 2012 21:10:44 GMT

FullScale 180 folks have a great post on windrows azure blog today. Trent’s post provides great insight into what it takes to building large scale database solutions and how sharding gets simplified with SQL Federations.

Want to read more… Here is the link.

http://blogs.msdn.com/b/windowsazure/archive/2012/05/24/windows-azure-isv-blog-series-full-scale-180-tackles-database-scaling-with-windows-azure.aspx

Enjoy.

-cihan

ID Generation in Federations in Azure SQL Database: Identity, Sequences, Timestamp and GUIDs (Uniqueidentifier)

Cihan Biyikoglu - SQL Azure — Thu, 17 May 2012 16:11:39 GMT

Identity and timestamp are important pieces of functionality for many existing apps for generating IDs. Federation impose some restrictions on identity and timestamp and clearly we need alternatives for federations that can scale to the targets of scale federations hits. So I’ll dive into alternatives and options in this post.

Lets take a look at identity and timestamp first in detail to understand why they are not good fits in federations: Identity is an unique id generation scheme and timestamp is typically used in optimistic concurrency implementation. Digging a little deeper, identity promises to generate a linearly increasing value without gaps scoped to a table in a given database. It provides the ability to reseed and provides an easy way to ensure uniqueness for many apps. Timestamp similarly provides a uniquely increasing value for all updates scoped to the database. Federations use databases as its building block and in an elastic fashion thus change the scope of databases as federation is repartitioned. This means you may end up with duplicate values generated in 2 separate members that got merged together when identity or timestamp is used on columns. We could generate the values in the root and make them globally unique but we end up with a single choke point that literally will limit your throughput. so what to do?

There are a number of options for distributed systems.

- GUID/Uniqueidentifier as a unique id generation method: I strongly recommend using uniqueidentifier as a identifier. It is globally unique by definition and does not require funneling generation through some centralized logic. Unlike identity and timestamp, uniqueidentifiers can be generated at any tier of the app. With unique identifiers you give up on one property; ID generation is no longer sequential. So what If you’d like to understand the order in which a set of rows were inserted? Well that is easy to do in an atomic unit. You can use a date+time data type with high enough resolution to give you ordering: ex: datetime2 or datetimeoffset data types both have resolution to 1/1000000 fraction of a second. So these types have great precision for ordering events.

This is more of an academic topic and don’t expect many folks to try this but, I’ll still mention that I strongly trying ordering across atomic units. Here is the core of the issue; If you need to sort across AUs, datetimeoffset still may work. However it is easy to forget that there isn’t a centralized clock in a distributed system. Due to the random number of repartitioning operations that may have happened over time, the date+time value may be coming from many nodes and nodes are not guaranteed to have sync clocks (they can be a few mins apart). Given no centralized clock, across atomic units datetime value may not reflect the exact order in which things happened.

Well, how about the difficulty of partitioning over ranges of uniqueidentifiers? GUIDs are sortable so this is nothing new but their sort order may get confusing. However it is fairly easy to understand once you see the explanation. This one explains the issue well; How are GUIDs sorted by SQL Server. It is a mind stretching exercise but I expect we’ll have tooling to help out with some of this in future.

Last but not the least, many people have experiences that suggest GUIDs (uniqueidentifiers) are bad candidates for clustering keys given they will not be ordered and cause page splits, causing higher latencies and fragmentation? No so on SQL Azure. at least not to the degree you experience in on premise SQL Server. SQL Azure dbs give you 3 replicas and that means the characteristics of writes are very different compared to a single SQL DB without HA. In SQL Azure the write have to be confirmed by 2 out of the 3 copies thus are always a network level writes… A network write is much slower in latency compared to what a page split would cause. Page split makes a tiny amount of this latency and is not visible to naked eye. You do end up with some fragmentation with uniqueidentifier and that is true. However fragmentation is hard to completely get rid of and unordered inserts compared to deletes or expanding updates don’t cause as much fragmentation so my experience simply says clustering on uniqueidentifiers is no reason for worry. This is easy to try; simply run the following script and watch your latencies. Here is a quick test you can try: See if you can make an unordered insert like the case for GUIDs take longer over many inserts:

use federation root with reset
go
drop table t1
drop table t2
go
create table t1(c1 int identity primary key, 
  c2 uniqueidentifier default newid(), 
  c3 char(200) default 'a')
create table t2(c1 int identity, 
  c2 uniqueidentifier default newid() primary key, 
  c3 char(200) default 'a')
go
-- MEASURE T1 
set nocount on
declare @s datetime2
set @s=getdate()
declare @i int
set @i=0
begin tran
while (@i<1000000)
begin
     if (@i%1000=0)
          begin 
               commit tran
               begin tran
          end
     insert into t2(C2, C3) values(default,default)
     set @i=@i+1
end
commit tran
select datediff(ss,@s, getdate()) 'total seconds for t1'
go
-- MEASURE T2
set nocount on
declare @s datetime2
set @s=getdate()
declare @i int
set @i=0
begin tran
while (@i<1000000)
begin
     if (@i%1000=0)
          begin 
               commit tran
               begin tran
          end
     insert into t2(C2, C3) values(default,default)
     set @i=@i+1
end
commit tran
select datediff(ss,@s, getdate()) 'total seconds for t2'
go

- Datetime2 for optimistic concurrency: Timestamp replacement is much easier to talk about; I recommend using datetime2 with its 1/1000000 of a second resolution instead of timestamp. Simply ensure that you generate ever time you update, you also update the modified_date and ensure to use that just like timestamp to compare before updates to detect update conflicts.

That said, we plan to enhance these functions to be meaningful for federations in future and will also remove some restrictions in members especially on reference tables. if you need any more details on these, you can always reach me through the blog.

Thanks!

-cihan

Scale-First Approach to Database Design with Federations: Part 2 – Annotating and Deploying Schema for Federations

Cihan Biyikoglu - SQL Azure — Fri, 13 Apr 2012 01:32:00 GMT

In part 1, I focused on modeling your data for scale. That was the first step in the scale-first design process. Scale-first data modeling focuses on table groups that needs scale and federations and federation keys that help surround these table groups for configuring their scale characteristics. In this post, we focus on the next step: the schema deployment. We’ll talk about how to annotate the schema definition and deploy it for matching the scalable data model you want to achieve.

Lets start with the basics: an overview of the schema model in federations.

Schema Model in Federations:

Different from a single database schema, with federations schema is distributed. Some parts of the schema live in the root database while other parts of the schema are scaled out to federations. This distribution is important to note because in the absence of distributed queries, only the local schema is visible when you connect to root or a member. you can discover other members or the root from a member but you cannot yet access anything other than the local schema.

Here is a quick examples: with schema with federations is that each db in the system, lets imagine Root db has table#1 and table#2 and member#1 in federation#1 has table#3 and federation#2 has a member#1 with table#4 in it. In this setup, table#3 is not visible in the root. table# 1, table#2 or table#4 are not visible when you connect to member#1 in federation#1. So no globally visible single schema with federations or a single namespace to address all schema today. All of this is fairly similar to what you would have, if you were building your own sharding.

It is important to note that within a federation, it is possible to setup member#2 and member#1 with different schemas (different tables, indexes, triggers sprocs, permissions etc). However typically most apps eventually deploy the same schema to all members in a federation. That is many apps do no have a permanent drift of schemas but a transient drift during upgrades etc. I have worked on sharded models where I index member differently for examples based on their size but very few instances.

The ability to independently manipulate schema in each part (root or members) of federations is critical because of a few reasons;

1- Availability and Partition Loss Tolerance: Independent schema per part (root or member) allows for better partition loss tolerance and overall availability. With independent schemas, each part of the system have its own private copy of the schema and have little or no dependency on other parts of the system. If some parts of the system suffer from transient or permanent loss of availability, the rest of the system stays accessible.

2- It promotes a flexible upgrade pattern for 24x7 apps: you can upgrade a piece at a time in your federated database (root+all members) and can rollback with minimized impact. A single monolithic database does not give you options to upgrade half of a table or try a fix to a stored proc for some of your users. I that this is the experience of many DBAs; it is hard to figure out what schema change operations and updates can be done online in databases and with the separation of schemas to many mini private parts, you can upgrade your schema partially for some of your data and test. During the upgrade even it the DDL exclusively locks the data, it is isolated to only 1 member. If the upgrade works out, roll the new schema to all other members. If it does not work out, you can roll back faster because you only rollback parts you updated. All of this is similar to how we do our rollout in SQL Azure as well.

3- It allows for parallel high performance upgrades: given the ability to scale out all your schema updates on all independent nodes with their independent transaction logs and temp dbs, memory, cpu and IO capacities, you can deploy your schema much faster than you can on a single monolithic database. Because schema update is massively parallelized to all members.

Annotating and Deploying Schema to Federations:

So you get it: All objects are local to where they are created. However if you start looking at the collection of all parts together across the root database, the federations and their members, you can see a set of tables used for 3 different purposes: central tables, federated tables and reference tables.

Types of Tables

Tables in the root are called central tables , tables scaled out in federations are called federated tables. Within members you can also create a second type of table called reference tables.

Central tables are the tables in your data model that you choose not to scale out. They are low traffic any happy with the resource constraints of a single node. For these tables life is easy. There are no changes to the shape of these tables or how you deploy them. Just connect to the root and deploy.

We talked about reference tables in part 1 as part of the logical data model but lets remember:

Reference tables are the tables we choose to distribute to all members because we want to optimize query-ability at the members. You can choose to leave many lookup tables as central tables in the root database but if the tables are regularly used for lookups by queries hitting your federated tables, it may make sense to move them to members as reference tables.

With reference tables given they are going to be part of the members, there are a few restrictions to ensure correctness;

Reference tables does not have support for IDENTITY property or the Timestamps/Rowversion data type. We hope to remove the restriction in future.
Reference tables cannot have a foreign key relationship back to a federated table. Other type of foreign key relationships work fine.

We touched on federated tables in part 1 as well: Federated tables have federation keys and are constrained to store values that comply with the range the member serves, whereas reference tables does not contain a federation key and are not constrained by the range of the member.

With federated tables, there are a few restrictions to ensure correctness as well;

Federated tables need to contain the federation distribution column as part of the table and today that can only be a single column and computed columns are not supported.
Federated tables does not have support for IDENTITY property or the Timestamps/Rowversion data type.
All unique indexes and clustered indexes must contain the federation distribution column.
Foreign key relationships between federated tables must include the federation distribution column.

Figure#1 : Gray – Central Tables, Blue & Green & Orange – Federated Tables

Objects Other Than Tables

All other types of objects besides tables, such as views, triggers, stored procedures and functions simply get deployed without requiring any changes to their definition as well as long as they can maintain local references. You may choose to deploy these objects to root or to members or to both depending on their function. For objects with a clear single table dependency you can follow the table placement (root or one of the members) and place the objects with table dependency in the same location as the table. However If you have objects that reference distributed pieces of your schema, like a stored procedure that reference tables in root and in member, this logic has to move to client side for now since SQL Azure does not support any cross database communication.

The only restriction within federations for non-table objects is on indexed views. Indexed views cannot be created on federation members. We hope to remove that restriction in future.

Deploying Central Tables

Lets start with tables you will leave in the root. These are central tables. Schema definition for central tables have no new consideration in federations. That is no change required to how you would design and deploy these tables.

-- connect to the adventureworks. this is the root db

CREATE TABLE HumanResources.Employee(EmployeeID int primary key, NationalIDNumber nvarchar(15)…)

Deploying Reference Tables

Pieces of the schema you scale-out in federation out of the root database like federated tables and reference tables need to be deployed to the federation members. Reference tables require no changes to their single-database schemas as well. However they are subject to the limitation on federation members listed above.

The deployment of reference tables require you to deploy the schema to every member. You can do this in a script to loop through them. You can use the following tool or code sample to achieve this;

Americas Deployment: http://federationsutility-scus.cloudapp.net/
European Deployment: http://federationsutility-weu.cloudapp.net/
Asian Deployment: http://federationsutility-seasia.cloudapp.net/

To deploy a central table you connect to root and iterate over each member using USE FEDERATION and repeat the CREATE TABLE statement such as the one below.

USE FEDERATION CustomerFederation(customerID=0x0) WITH RESET, FILTERING=OFF

GO

CREATE TABLE Zipcodes(id uniqueidentifier primary key, code nvarchar(16) not null, …)

GO

Deploying Federated Tables

Just like reference tables, federated tables need to be deployed to the federation members. Federated tables do go through a transformation for their original form in a single database model. Federated tables are required to have the federation key as a column in the database. Some tables naturally contain the federation key but for others like orderdetails table, you denormalize the table to include the federation key (customerID). Federated tables are also anotated with a FEDERATED ON clause that point to the distribution column used in the table. Here is the deployment of the orderdetails table;

USE FEDERATION CustomerFederation(customerID=0x0) WITH RESET, FILTERING=OFF

GO

CREATE TABLE Ordersdetails(orderdetail_id uniqueidentifier primary key,

order_id uniqueidentifier not null,

customer_id uniqueidentifier not null,

…)

FEDERATED ON (customerID=customer_id)

GO

One useful piece of information to note here is that even though a denormalization is required, maintenance of the new federation in each table like customer_id columns the oderdetails table above, can be simplified with the following default using the federation_filtering_value() function. The function grabs the federation key value you used to connect into the member in the USE FEDERATION statement and will automatically add the value to new rows. Great way to shortcut through this change.

USE FEDERATION CustomerFederation(customerID=0x0) WITH RESET, FILTERING=OFF

GO

CREATE TABLE Ordersdetails(orderdetail_id uniqueidentifier primary key,

order_id uniqueidentifier not null,

customer_id uniqueidentifier not null DEFAULT federation_filtering_value('customerId'),

…)

FEDERATED ON (customerID=customer_id)

GO

Deploying Objects Other Than Tables

For objects other than tables there is no refactoring to do but you need to ensure connection is switched over to the right part of the federation for the deployment. For objects you need to deploy to members the fanout utility above can help or the code sample can help you code your own scripts. Here is a sample script that deploys a stored procedure to a member.

USE FEDERATION CustomerFederation(customerID=0x0) WITH RESET, FILTERING=OFF

GO

CREATE PROCEDURE proc1(p1 int) AS

BEGIN

….

END

To recap, once the scale-first data model is defined, there are a few incremental changes to the tsql script that expresses the data model. There are the annotations with FEDERATED ON for tables, also changes to do to your foreign key relationships like including the federation distribution key and there are changes to how you deploy some parts of your schema to members like using USE FEDERATION to switch the connection to the members and fanning out the statements to all members. with those changes you get a scale-first data model deployed to SQL Azure. The last step along the way is to make some changes to the app. We’ll talk about that next.

Follow @cihangirb

AzureWatch for Monitoring Federated Databases in SQL Azure

Cihan Biyikoglu - SQL Azure — Mon, 09 Apr 2012 17:59:04 GMT

AzureWatch has a great new experience now for watching Federations.

With AzureWatch it gets easier to monitor the whole deployment in an aggregate view for the following resource dimensions;

Database Size
Number of open transactions
Number of open connections
Number of blocking queries
Number of federated members (root database only)

Igor writes about the support in-depth here;

http://www.paraleap.com/blog/post/SQL-Azure-Federations-active-monitoring-setup-and-instructions.aspx

-cihan

Inside SQL Azure – Self-healing, Self-governing, Massively Scalable Database service in the Cloud.

Cihan Biyikoglu - SQL Azure — Sat, 24 Mar 2012 16:31:55 GMT

This is a talk I did a few weeks ago that will take you under the hood of SQL Azure. I cover the gateway to SQL Azure that manager billing, connectivity and some parts of T-SQL as well as Engine and surrounding services like load balancing and node governance and the fabric of SQL Azure that manages node health, replication and failover. Why is this all important to know you may ask... Well understanding all this allows you to build apps that are better citizens in the new cloud world. At the end I tie it all together with what you need to think about in your own apps when working with the service and talk about differences in app characteristics between SQL Server and SQL Azure.Even if you are not building an app on SQL Azure, today, If you want to get a deep understanding of services we built around SQL Server that make SQL Azure so unique, this is the talk to watch:

http://technet.microsoft.com/en-us/edge/inside-sql-azure-self-governing-self-healing-massively-scalable-database-service-in-the-cloud

Follow @cihangirb

Countdown is over for SQL Server 2012… That means Scaling on-demand with Federations gets easier!

Cihan Biyikoglu - SQL Azure — Wed, 07 Mar 2012 23:08:29 GMT

Today SQL Server 2012 is released to web and now we have a new set of bits that provide great enterprise class data management capabilities that stretches the boundaries of SQL Server to the public and private cloud. With SQL Server 2012, moving back and forth between cloud and on premise existence is much easier.

SQL Server 2012 tooling is one of the stories that is easy to miss within the 100s of additions and improvements we have in this release. SQL Azure obviously has a set of online tools you can use – SQL Azure Management Portal simplifies life if you are purely in the cloud but for most of us the story is a hybrid deployment. Some parts on premise and others in the cloud in those scenarios, SQL Server Management Studio is the key tool! This post can help you get up to speed on how scale-out gets easier with Management Studio capabilities around Federations.

http://blogs.msdn.com/b/windowsazure/archive/2012/02/15/managing-federations-in-sql-azure-with-the-sql-server-management-studio-of-sql-server-2012-rc0-new.aspx

If you’d like to get a fresh overview of federations; the why and the how, here is a quick link to the video that is part of the SQL Server 2012 Launch.

http://social27.http.internapcdn.net/social27/Denali/Sessions/1500/ST128_Denali_CT-02_Cihan_Biyikoglu.wmv

Enjoy the new set of bits and make sure to play with federations using management studio/.

Follow @cihangirb

Scale-First Approach to Database Design with Federations: Part 1 – Picking Federations and Picking the Federation Key

Cihan Biyikoglu - SQL Azure — Wed, 29 Feb 2012 22:04:00 GMT

Scale-first approach to database design refer to the approach used when designing data models that has scalability as their primary concern. To recap the purpose of federations I talk about 2 properties: unlimited scale and the best economics. These are some pretty bold claims… To get you you there, federations promote a set of concepts to model your data with scale as your first and foremost goal.

Scale-first approach does not require you to relearn database design from scratch. It is just an additional set of steps you apply while generating your data model. These additional steps help you configure your model for best scale characteristics. By picking your federations and federations key, you work in the scale-first principals into your data and database design. In other words, by picking federations and federation keys, you get to annotate the data model and your database objects with scale-out intent.

In a series of posts, I’ll walk through the process of designing, coding and deploying applications with federations. If you’d like to design a database with scalability in mind using sharding the technique, this post will also help you get there as well...

In case you missed earlier posts here is a quick definition of federation and federation key;

Federation is an object defined to scale out parts of your schema. Every database can have many federations. Federations use federation members which are regular sql azure databases to scale out your data in one or many tables and all associated rich programming properties like views, indexes, triggers, stored procs. Each federation has a name and a federation key which is also called a distribution scheme. Federation key or federation distribution scheme defines 3 properties;

A federation key label, help self document the meaning of the federation key like tenant_id or product_id etc. ,

A data domain to define the distribution surface for your data. In v1, data domains supported are INT, BIGINT, UNIQUEIDENTIFIER (guid) and VarBinary – up to 900 bytes.

A distribution style, to define how the data is distributed to the data domain. At this point distribution style can only be RANGE.

The scale First approach is an add-on set of steps to how you model your data so lets pick up from the point where you modeled your basic entities and objects in your database. Assume you are designed the AdventureWorks (AW) database. You have the entities at play like Stores, Customers, Orders, Products, SalesPeople etc and now you are thinking about how to scale it out…

Picking your Federations:

Picking your federations and federations keys is much like other steps of data modeling and database design processes like normalization. With the scale-first approach and federation concepts, you can come up with multiple alternatives that compromise to optimize for various parts of your workload. Here are these steps to help you come up with those alternatives and help you choose between these alternatives:

Step #1: Identify entities you want to scale out: You first identify the entities (collection of tables) in your database that is going to be the busiest parts of your traffic, that will be popular targets that will be hit by largest portion of your workload… That will be under pressure due to excessive resource requirements and needs scale out. These entities are your candidate federations in your design.

Step #2: Identify table-group that make up these entities (identify federated tables): After identifying these popular entities you want to scale out, you identify main tables table-groups by identifying main tables that make up the entity by traversing the relationships, access patterns and properties to these set of entities. I’ll refer to these groups as table-groups.

Some may be lucky to have one table group but in typical cases you find multiple table-groups in your database model such as customer, order and product. These table-groups make up your entire data model and may have relationships such as one-to-many, one-to-one or many-to-many. For example: orders and customers are entities in SalesDB that consist of table-groups. Order table-group are made up of orders and orderdetails tables. customer table-group contain customers and customeraddress tables. Custom has many Orders, thus these 2 entities are said to have a one-to-many relationships.

Picking your Federation Key:

Step #3: Identify the Federation Key: Federation key is the key used for distributing the data in the table-groups. Federation key is used in routing the query to the right partition thus is critical to have in all latency and scale sensitive parts of your workload. Federation key also define the boundary of atomic units. Atomic units are rows across all tables in the table-group. Atomic Units are rows across all the scaled out tables that share the same federation key value. For example across the order table-group, if the federation key happen to be customerID, all rows that have customerID=55 in orders and orderdetails tables make up the atomic unit.

Ideal federation keys have the following properties;

Atomic Units is the target of most query traffic & transaction boundaries. This is desired because business logic within a atomic unit boundary is coded with all the richness of the SQL Language – stored procedures, triggers, CTEs, etc.
Distributes workload equally to all members equally – decentralize load to many atomic units as opposed to concentrating the load. This is important because it promotes the application take advantage of the full computational capacity available to the application at all members or partitions of data.
Atomic units cannot be split, so largest atomic unit does not exceed the boundaries of a single federation member. Picking small-footprint atomic units are important because it avoids hitting the walls of the system.

Step #4: Picking Reference Tables: Now that you have picked your federations, table groups in them and federations keys for distributing the data in these federations, you have a bunch of your objects you scaled out with federations and other set of objects you don’t scale out and simply leave in the root database. Federations provide a 3rd type of table called a reference table that optimize your query-ability. Reference tables typically contain popular lookup info in the transactions and workload that hit the federated tables but are tables that do not align with the federation key. One example can be the Zipcodes table in CustomerOrders federation with CustomerID federation key. Reference tables like Zipcodes is an optimization to take advantage of local joins to improve latency and query-ability. Reference tables are created in every member and may or may not contain the same data across all member. Having some tables local and repeated in every member however changes how to converse with a reference table and what guarantees and behavior you expect from it. Simple put: reference tables operate in eventual consistency as opposed to strict consistency.

Local updates to the reference table within a member are allowed. However these local updates are not automatically replicated by the rest of the copies in other members at this point. In future this may change. However in future the replication of reference table data, even if it is done by the system need to happen asynchronously for the best scale characteristics. Distributed transactions simply does not scale to large number of transaction managers. With eventual consistency your app interact with this data can no longer assume to find the latest updates in all copies meaning; updating the zipcode table in one member in a transaction may mean that you may may still find its missing if you switch to another member.

This is it! 4 Steps!

Lets apply these rules on a example to see how they help pick federations and fed keys.

Walking through AdventureWorks

The database design for AW is something I hope you are already familiar with. You can fid details here on the schema for SQL Azure; http://msftdbprodsamples.codeplex.com/releases/view/37304. For this app we want to be able to handle Mil customers, 100 Mil orders, and Mil products. These are the largest entities in our database. I’ll add a few more details on the workload to help guide our design; here are our most popular transactions and queries;

‘place an order’, ‘track/update orders’, ‘register/update a customer’, ‘get customer orders’,’get top ordering customers’, ‘register/update products’, ’get top selling products’

and here are some key transactions and consistent query requirements;

‘place an order’, ‘import/export orders for a customer and stores’, ‘monthly bill for customers’

Step #1: We have the classic sales database setup with customer, order and product in AW. In this example, we expect orders to be the parts that will be most active, the tables will be the target of most of our workload. We expect many customers and also handle cases where there are large product catalogs.

Step #2: In AW, Store and customer tables are used to identify the customer entity. SalesTerritory, SalesOrdersHeader and SalesOrderDetail tables contain properties of orders. Customer and Order entities have one-to-many relationship. On the other hand, Product entity has a many-to-many back to Order and to Customer entity. When scaling out, you can align one-to-one and one-to-many relationships together but not many-to-many relationships. Thus we can only group Customers and Orders together but not products.

Step #3: Given the Customer (store and customer tables) and Order (SalesOrderHeader, SalesrderDetail, SalesTerritory) we can think of a few setups here for the federation key.

- StoreID as the Federation Key:

Well that would work for all transactions so #1 of ideal federation key principal taken care of! That is great.
However stores may have varying size and may not distribute the load well if there the customer and order traffic variance between stores is too wide. Not so great on #2 principal of ideal federation keys.
StoreID as a federation key will mean all customers and all their orders in that store will be a single atomic unit (AU). If you have stores that could get large enough to challenge the computational capacity of a single federation member, you will hit the ‘split the atom’ case and get stuck because you cannot.

So StoreID may be too coarse a granule to equally distribute load and may be too large an AU if a store gets ‘big’. By the way, TerritoryID is a similar alternative and has a very similar set of issues so same argument applies to that as well.

- OrderID as the Federation Key:

OrderID certainly satisfy #2 and #3 of the ideal federation key principals but has an issue with #1 so lets focus on that.
That could work as well but is too fine a granule for queries that are orders per customer in the system. It also won’t align with transactional requirements of import/export of customer and store orders. Another important note; with this setup, we will need a separate federation for the Customer entity. It means that queries that may be common like ‘get all orders of a customer’ or ‘get order of customer dated X/Y/Z’ will need to hit all or at least multiple members. Also with this setup we lose ability to transact multiple order from a customer. We may do that when we are importing or exporting a customers orders.

Fan-out is not necessarily bad. It promotes parallel executions and can provide great efficiencies. However efficiencies are lost when we hit all members and can’t fast-eliminate members that don’t have any data to return and when cost of parallelization overwhelms the processing of the query. With OrderID as the federation key, queries like ‘get orders of a customer’ or ‘get top products per customer’ will have to hit all members.

- OrderDetailID as the Federation Key:

The case of OrderDetailID has the same issues as OrderID case above but amplified on principal #1. With this setup, we will lose transactional boundary to place a single order in a transaction. In this case, there will be more queries that will need to be fanned-out like ‘get all customer orders’ or ‘get an order’… Makes assembling order a full fan-out query which can get quite expensive.

- CustomerID as the Federation Key:

With CustomerID #2 and #3 will not likely be an issue. The only case is where a customer gets so large that it overwhelms a member and computational capacity of AU. For most cases, CustomerID can be a great way to decentralize the load getting around issues StoreID or TerritoryID would create.
This setup also satisfy almost all of #1 as well except 2 cases; one is ‘get top selling products across all customers’. However that case isn’t satisfied in any of the other alternatives either. This setup does focus the DML (INSERT/UPDATE/DELETE) transactions and satisfy both multiple order and single order placement transaction to work seamlessly. So looks like a good choice for #1 standpoint. Second is import/export at store boundary; for example import of stores all customers and orders will not be possible with this setup in a transaction. Some people may be able to live with consistency at the customer level and be ok relaxing consistency at the store level. you need to ask yourself; Can you work with eventual consistency without transactions at the store level by depending on things like datetime stamps or some other store level sequence generator. if you can this is the right choice.

How about Product Entity?

We have not touched on how to place products in this case. To remind you the issue; there is a many-to-many relationship in customers vs orders thus federation aligned with orders and/or customers Well, you have 3 choices when it comes to products.

- Products as a Central Table: In this case, you’d leave Product entity in the root. That would risk making root and products a bottleneck. Especially if you have a fast updating catalog of products and you don’t build caching facilities to minimize hitting the root for product information for popular queries and transaction in the system. The advantage to this setup is that product entity can be maintained in a single place with full consistency.

- Product as a Reference Table: In this case, you would place a copy of the products entity in each federation member. This would mean you need to pay more for storing this redundant information. This would mean updating the product catalog will have to be done across many copies of the data and will mean you need to live with eventual consistency on product entity across members. That is, at any moment in time, copies of products in member 1 & 2 may not be identical. Upside is this would give you good performance like local joins.

- Product as a separate Federation: In this case, you have a separate federation with a key like productID that holds product entity = all the tables associated with that. You would set up products in a fully consistent setup so there would not be redundancy and you would be setting up products with great scale characteristics. You can independently decide how many nodes to engage and choose to expand if you run out of computational capacity for processing product queries. Downside compared to the reference table option is that you no longer enjoy local joins.

To tie all this together, given the constraints most people will choose customerID as the fed key and place customers and orders in the same federation and given the requirement to handle large catalogs, most people will choose a separate federation for products.

Here is what the actual statements would look like;

CREATE FEDERATION CustomerFederation (customerID uniqueidentifier RANGE)

CREATE FEDERATION ProductsFederation (productID uniqueidentifier RANGE)

Just like this case, for sophisticated schemas with variety of relationships, there pros and cons to evaluate much like other data and database design exercises. Designing with federations puts ‘scale’ as the top concern and that pushes you to evaluate various compromises on transactions and query processing.

In part 2, I’ll cover the schema annotations that you need to make with federations.

Later!

Follow @cihangirb

What would you like us to work on next?

Cihan Biyikoglu - SQL Azure — Tue, 28 Feb 2012 08:46:00 GMT

10 weeks ago we shipped federations in SQL Azure and it is great to see the momentum grow. This week, I'll be spending time with our MVPs in Redmond and we have many events like this where we get together with many of you to talk about what you'd like to see us deliver next on federations. I'd love to hear from the rest of you who don't make it to Redmond or to one of the conferences to talk to us; what would you like us to work on next? If you like what you see today, what would make it even better? if you don't like what you see today, why? what experience would you like to see taken care of, simplified? Could be big or small. Could be NoSQL or NewSQL or CoSQL or just plain vanilla SQL. Could be in APIs, tools or in Azure or in SQL Azure gateway or the fabric or the engine? Could be the obvious or the obscure...

Open field; fire away and leave a comment or simply tweet #sqlfederations and tell us what you'd like us to work on next...

Thanks

-cihan

Teach the old dog new tricks: How to work with Federations in Legacy Tools and Utilities?

Cihan Biyikoglu - SQL Azure — Fri, 24 Feb 2012 07:37:00 GMT

I started programming on mainframes and all of us would have thoughts these mainframes would be gone by now. Today, mainframes are still around as well as many Windows XPs, Excel 2003s and many more Reporting Services 2008s out there. Doors aren't shut for these tools to talk to federations in SQL Azure. As long as you can get to a regular SQL Azure database with one of these legacy tools, you can connect to any of the federation members as well. Your tool does not even have to even know to issue a USE FEDERATION statement. You can connect to members without USE FEDERATION as well. Here is how;

First step is to get the member database name discovered. You can do that simply by getting to the member using USE FEDERATION and run SELECT db_name(). The name of the member database will be system-<GUID>. The name is unique because of the GUID. If you need a tool to show you the way; use the fanout tool to run SELECT db_name() to get names of all members. Info on the fanout tool can be found on this post; http://blogs.msdn.com/b/cbiyikoglu/archive/2011/12/29/introduction-to-fan-out-queries-querying-multiple-federation-members-with-federations-in-sql-azure.aspx
Once you have the database name, connect to the server and the database name in your connection string and you are in. At this point you have a connection to the member. This connection isn't any different from a FILTERING=OFF connection that is established through USE FEDERATION.

This legacy connection mode is very useful for tools like bcp.exe, reporting services (SSRS), integration services (SSIS), analyses services (SSAS) or Office tools like Excel or Access. None of them need to natively understand federations to get to data in a member.

Obviously, future looks much better and eventually you won’t have to jump through these steps but 8 weeks into shipping federations, if you are thinking about all the existing systems you have surrounding your data, this backdoor connection through the database name to members will save the day!

Enjoy!

OData and Federations: yes you can… Just ask Maxim

Cihan Biyikoglu - SQL Azure — Tue, 21 Feb 2012 22:14:33 GMT

In case you guys have not seen this; Maxim Glukhankov has a new sample on codeplex that shows how to set up OData on top of SQL Azure Federations. Just go under “Source Code” at the top and download!

http://odatafederation.codeplex.com/

Not every tool in the world natively understand federations today. OData simplifies life for a lot of the tools that can’t speak ‘sqlfederations’ yet and the list of OData consumers is full with great names like Excel! be able to query your federated data using Excel now that should appeal to many!

You can find the full list here; http://www.odata.org/consumers

Browsers IE, Chrome, Safari etc; Most modern browsers allow you to browse Atom based feeds. Simply point your browser at one of the OData Producers.

Excel 2010: PowerPivot for Excel 2010 is a plugin to Excel 2010 that has OData support built-in.

Client Libraries: Client libraries are programming libraries that make it easy to consume OData services. We already have libraries that target: Javascript, PHP, Java, Windows Phone 7 Series, iPhone (Objective C) and .NET. For a complete list visit the OData SDK.

OData Helper for WebMatrix: The OData Helper for WebMatrix and ASP.NET Web Pages allows you to easily retrieve and update data from any service that exposes its data using the OData Protocol.

Tableau: Tableau - an excellent client-side analytics tool - can now consume OData feeds

Telerik RadGrid for ASP.NET Ajax: RadGrid for ASP.NET Ajax supports automatic client-side databinding for OData services, even at remote URLs (through JSONP), where you get automatic binding, paging, filtering and sorting of the data with Telerik Ajax Grid.

Telerik RadControls for Silverlight and WPF: Being built on a naturally rich UI technology, the Telerik Silverlight and WPF controls will display the data in nifty styles and custom-tailored filters. Hierarchy, sorting, filtering, grouping, etc. are performed directly on the service with no extra development effort.

Telerik Reporting: Telerik Reporting can connect and consume an existing OData feed with the help of WCF Data Services.

Database .NET v3: Database .NET v3 - A free, easy-to-use and intuitive database management tool, supports OData

Pebble Reports: Pebble Reports lets you create reports consisting of tables, charts and crosstabs. Reports can be exported to Word, Excel and PDF formats, or emailed via Outlook.

Implementing MERGE command using SQL Azure Migration Wizard by @gihuey

Cihan Biyikoglu - SQL Azure — Mon, 20 Feb 2012 18:09:17 GMT

Well I am sure you noticed that federation’s support SPLIT AT and DROP AT commands but not MERGE AT yet today. What would this MERGE command do? It would do the symmetric opposite of SPLIT. SPLIT introduces a new split point into the federation. So MERGE would glue that back together. Here is what that would look like in real life; imagine a member layout like member#1: 0 to 50 and member#2: 50 to 100 range. MERGE would bring back these 2 members into a single member with a command like this: (note: by the time we are done with it it could be a different syntax.)

ALTER FEDERATION … MERGE AT (key=50)

This operation would move the data back into a single member that would cover all federated data in 0..100. MERGE AT would also allow transactions to still continue to happen on the 2 source members: 0..50 and 50..100, while the system rearranges the data back into a single member and would not require any downtime.

Some do ask why we did not implement MERGE? Sure, we could have waited until MERGE is implemented to light up federations but many customers told us that they won’t need MERGE right away and they do need SPLIT AT badly. Given the original pricing model in SQL Azure MERGE also didn’t make sense. Not so any more… The new pricing model explained here, gives you benefits when you combine members into larger sizes.

So what can you do today, until we get MERGE done? Big thanks to George Huey (@gihuey), here is a very timely article that talks about how to implement MERGE yourself using SQL Azure Migration Wizard to take advantage of the new pricing model and pay less. There are a few limitations; it isn’t a single simple high level command like ALTER FEDERATION and it does require downtime for your app so not online but it can be done! Take a look under the section titled “scaling down” for details.

http://msdn.microsoft.com/en-us/magazine/hh848258.aspx

The New Pricing Model for SQL Azure Explained!

Cihan Biyikoglu - SQL Azure — Fri, 17 Feb 2012 00:53:00 GMT

The new pricing model is an amazing improvement over the original model. In 60 seconds here are the 2 major improvements you will notice:

#1 - New 100MB Option: Smaller databases has been a big ask. If you do not have a multi-tenant database tier, this may be the way to go given you can have many tenants. The story is told better with this picture. This is just going up to database capacity going up to 10GB. Look at that bottom left corner. that is the 0.1GB database.

#2 - Granular Increments: The new model introduces 1GB Increments as opposed to the original jump points : 1-5-10-20-30-40-50-150…GB. The picture looks so different that I think it is easier to begin telling the story with the only that didn’t change: 1GB database capacity. That is still $9.99 but everything else simply improved for customers. Not only do we have granular increments but we no longer have business edition starting at 10GB. You can have a 100MB business edition database and still pay 4.99 a month instead of 99.99. All these sound fantastic to all of you but the engineering team is in tears – just like that VWJetta commercial. Just to help you appreciate the price difference, here is the full picture to show you savings all the way up to 150GB databases. We have saving over %78 for some of the segments.

One important point to remember is; for this price, you are not only getting database storage for this price! Beyond just storage in database, the simplified price measurement on database-GBs in SQL Azure also include rich TSQL language with stored procedures for high performance query processing as well as transactional processing, the built in high availability and worry free physical administration you get with SQL Azure

For more information here are a few useful links;

Pricing Federations in SQL Azure: http://blogs.msdn.com/b/cbiyikoglu/archive/2011/12/12/billing-model-for-federations-in-sql-azure-explained.aspx

Price Calculator: http://www.windowsazure.com/en-us/pricing/calculator/

Pricing Details: http://www.windowsazure.com/en-us/pricing/details/

Offers: http://www.windowsazure.com/en-us/pricing/purchase-options/

Pay by Invoice option: http://www.windowsazure.com/en-us/pricing/invoicing/

Thanks!

Great new post on Federations in SQL Server Management Studio: Simplified development and management for the whole app lifecycle with Federations.

Cihan Biyikoglu - SQL Azure — Wed, 15 Feb 2012 18:10:47 GMT

Ambrish posted a great new article on the Windows Azure Team Blog on new functionality coming in SQL Server 2012 Management Studio for Federations. Here is the full writeup;

http://blogs.msdn.com/b/windowsazure/archive/2012/02/15/managing-federations-in-sql-azure-with-the-sql-server-management-studio-of-sql-server-2012-rc0-new.aspx

You can download the tool form SQL Server 2012 page;

http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

Thanks

-cihan

Want to demo federations to your boss? Here is the full package: Slides & the AdventureWorks database fully scaled-out with Federations

Cihan Biyikoglu - SQL Azure — Tue, 14 Feb 2012 22:25:23 GMT

What a great day! first there is some great news on SQL Azure pricing changes and now the AdventureWorks database is out with a new version for SQL Azure and it contains a flavor that is scaled-out with Federations.

Thanks to Scott we now have a scalable AdventureWorks database full utilizing federations. Here is Scott’s blog announcing the news;

http://geekswithblogs.net/ScottKlein

Now you guys have the full package. Sample AdventureWorks with Federations and Slides to go tell your boss, your friends, family members and your personal trainer all about federations. Here are the slides for federations;

http://www.slideshare.net/cihangirb/federations-in-sql-azure-building-large-scale-elastic-data-tiers-in-the-cloud

Follow @cihangirb

Connection Pool Fragmentation: Use Federations and you won’t need to learn about these nasty problems that come with sharding!

Cihan Biyikoglu - SQL Azure — Thu, 09 Feb 2012 05:25:36 GMT

Sharding has been around for a while and I have seen quite a few systems that utilize SQL Azure with 100s Databases and 100s of compute nodes and tweeted and written about them in the past. Like this case with Flavorus;

Ticketing Company Scales to Sell 150,000 Tickets in 10 Seconds by Moving to Cloud Computing Solution

James has certainly been a great partner to work with and he is an amazingly talented guy who can do magic to pull some amazing results together in a few weeks. You need to read the case study for full details but basically he got Jetstream to scale to sell 150,00 tickets in 10 seconds roughly with 550 SQL Azure databases and 750 compute nodes. And he did that in about 3 weeks time testing included!

These types of systems are now common but the scale problems at these levels certainly get complex. one of those that hit many of the customers is the problem with connection multiplexing or simply connection pool fragmentation. Bear with me, this one takes a paragraph to explain:

Imagine a sharded system with M shards and N middle tier servers and with a max of C concurrency requests. The number of connections you need to establish is M*N*C. That is because every middle tier server has to establish possibly C connection to every shard given requests come in randomly distributed to the entire site. Now imagine the formula with some numbers; I’ll be conservative and say 50 shards, 75 middle tier servers and 10 concurrent connections. Here is what you end up with;

M * N * C = 50 * 75 * 10 = 37,500 connections

Over 37K… That is a lot of connections! Here are some other numbers;

Each middle tier server ends up with M connections pools with C connections in each at the worst case. that is 500 connection from each middle tier machine.
Also every shard maintains 750 connections from the middle tier servers in the worst case. that is a lot of connections to maintain as well. Large number of connection are bad because that can cause you to become victim of throttling… not a good thing…

Another fact of life, is that life isn’t perfectly distributed so what happens most times is these 500 connections in the middle tier server don’t get used enough to stay alive. SQL Azure terminates them after a while of being idle. You end up with many of these idle connections dead in the pool. That means the next time you hit this dead connection to a shard from this app server, you have to reestablish a cold connection from the client all the way to the db with a full login and whole bunch of other handshakes that cost you orders of magnitude more in latency. This issue can be referred to as connection pool fragmentation… Connection Pooling documentation also makes references to the issue: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx and look for fragmentation. Push M or N or C higher and things get much worse.

Federation cure this for apps. With federations the connection string points to the root database. Since we ‘fool’ the middle tier to connect to root in the connection string, M=1 and the total connection from all middle tier servers to SQL Azure is only N*C = 750 total connections. Compared to over 37K that is a huge improvement!

Not only that but each middle tier server has 1 connection pool to maintain with only C=10 connection… Much better than 500. Member connections can get more complicated because of pooling in the gateway tier but it is much much better than 750 connections per shard as well. So this makes the problem go away! This is the magic of USE FEDERATION statement. No more connection pool fragmentation.

Another great benefit of USE FEDERATION is that it give you the ability to maintain your connection object and keep rewiring your connection from gateway to the db nodes in SQL Azure without a disconnect. With sharding, since we don’t support USE statement yet, the only way to connect to another shard is to disconnect and reconnect. With USE FEDERATION you can simply keep doing more USE FEDERATIONs and never have to close your connection!

If you made is this far, thanks for reading all this… If you use federations, you can forget what you read. Just have to remember that you didn’t have to learn more about connection pool fragmentation or frequent disconnects & reconnects you have to face with sharding.

-Cihan

Follow @cihangirb

SQL Azure Migration Wizard: Fantastic tool for moving data to and from SQL Azure Federations

Cihan Biyikoglu - SQL Azure — Fri, 03 Feb 2012 22:13:48 GMT

If you worked with federations, I am sure you are already know about the online tools like SQL Azure Management Portal that give you the ability to orchestrate your federations with repartitioning operations or resize member MAXSIZE and edition.

In episode #69 on Cloud Cover, George and Wade cover moving schema and data between SQL Server, SQL Azure databases with Federations! So you can move SQL Server databases to SQL Azure Federation, or single scale-up SQL Azure databases to SQL Azure Federations or simply transfer, move data between SQL Azure Federations. All these are possible with the well known community tool : SQL Azure Migration Wizard. What is great is the code is available on codeplex and the tool generates the scripts you can run from command line for you. You don’t get the great retry logic or the parallelism with that SQL Azure Migration Wizard provides when you do that however…

There is one more good news in all this; George does not cover this in his talk but, you can also use SQL Azure Migration Wizard to perform the missing MERGE repartitioning operation manually. There is an article that is coming on that topic soon. You simply export a member, then DROP the existing member that has been exported. Then import the data back in. Fantastic!

Well here is the episode; watch if for yourself;

Cloud Cover Episode #69

-cihan

PHP and Federations in SQL Azure - Sample Code from Brian

Cihan Biyikoglu - SQL Azure — Mon, 30 Jan 2012 06:03:00 GMT

Brian has a tone of samples on PHP and Federations in this post right here... Another great one!

http://blogs.msdn.com/b/silverlining/archive/2012/01/18/using-sql-azure-federations-via-php.aspx

-cihan

How much overcapacity are you running with today? I bet SQL Azure Federations can trim that!

Cihan Biyikoglu - SQL Azure — Thu, 26 Jan 2012 01:22:00 GMT

I know I already posted a whole bunch on “why use federations” or “what are federation for” but most conversations on federations, I get the question on ‘why’? so I wanted to go back to basics and what the combination of SQL Azure (a.k.a PaaS database in the cloud) and Federations is a killer combination.

Obviously federations can be used in many different ways; multi-tenancy for scaling to spikes and bursts or for gradually growing workloads. It is great for getting you over the capacity limitation of a single node in public cloud (typically a commodity machine) or the limitation of a single SQL Azure database like storage or computation capacity or simply transaction throughput with a single SQL azure database before you get throttled. But… But… But all that aside… The main reason in all these cases it is the amount of overcapacity you maintain. Mark Russinovich shows a similar chart in his talks and I’ll gladly borrow this for federations;

Imagine the isolated capacity you need at the database tier; Here is your capacity for the next 6-18 months and here is what you maintain as capacity on premise; You buy some more HW, fire it up and you get more cores, more memory and more IO capacity etc. You release new functionality that changes your workload, you get more customers, your customers data grows or whatever else that changes your workload over time, you push things to limits so you are under provisioned so you acquire some more HW and life goes on…

The above picture is also the representation of systems with static partitioning or sharding today on any system that offers no repartitioning operations. Lets say you start life with 20-30 partitions, you distribute and size things for the peak loads. Or if you are multi-tenant architecture already, you place 100 or 200 tenants per database or shard. But those tenants change and grow so these static decisions require some level of overprovisioning to be safe because repartitioning is offline and could be error prone every time.

With the cloud, the picture looks like the one below; You provision just in time and simply trace along the capacity line closely.

Federation is there for trimming overcapacity as well. You don’t need to make a static decision about how many tenants to put into a shard, you don’t need to decide how many shards you need for the web app up front for the next 3 months or the year. You can change your mind over time and Federations let you do repartitioning online without downtime so you don’t need to take down the app or the database. If it turns out some tenants grow and you cannot no longer fit 10000 tenants into 1 database and you need to go 5000 tenants per db… OR if you want to handle the black friday or the tax day or the end of month reporting and you need more capacity… OR if your service takes off and you acquire a whole bunch of customers… OR if you release a new version or a new functionality that changes the workload, you can prepare for it with federations. Kick off a SPLIT and it will engage more nodes. All online!

Happy sharding with federations!

Follow @cihangirb

Fan-out Querying for Federations in SQL Azure (Part 2): Scalable Fan-out Queries with TOP, ORDER BY, DISTINCT and Other Powerful Aggregates, MapReduce Style!

Cihan Biyikoglu - SQL Azure — Thu, 19 Jan 2012 22:03:18 GMT

Welcome back. In the previous post: Introduction to Fan-out Querying, we covered the basics and defined the fragments that make up the fan-out query namely the member and summary queries. Fan-out querying refer to querying multiple members.

We looked at queries examples in the previous post with member queries with no summary queries, that is the member queries were simply UNIONed. In this post, we’ll take a look at Summary queries. when are they needed? what are some common patterns and examples?

Summary queries are required for post processing the member queries. Simply put, summary queries can help reshape the unioned member results into the desired final share. Summary queries refer to object generated by the member queries and depending on the implementation can be executed on the client side or the server side. That said, today Federations do not provide a built in server side processing option for summary queries. Here are a few options for processing summary queries:

- LINQ To DataSets offers a great option for querying datasets. Some examples here. LINQ is best suited for the job in my opinion with flexible language constructs, dynamic execution and parallelism options.

- ADO.Net Expressions in DataSets offers a number of options for summary query processing as well. For example, DataColumn.Expressions allow you to add aggregate expressions to your Dataset for this type of processing. Or you can use DataTable.Compute for processing a rollup value.

- Obviously server-side full fan-out processing is also an option. This option refers to server side running member and summary query in a single round-trip from the client to SQL Azure. However as of Jan 2012, this is not built into SQL Azure Federations. We’ll take a look at a simulated version of this in the sample tool here; You can use the deployment closer to your database for efficiency;

Americas Deployment: http://federationsutility-scus.cloudapp.net/
European Deployment: http://federationsutility-weu.cloudapp.net/
Asian Deployment: http://federationsutility-seasia.cloudapp.net/

The tool provides a basic and full ‘fanout’ page. Basic page contains only member queries through a simplified interface. The results are simply unioned (or ‘union all’-ed to be precise) together. Full page provides additional capabilities including member and summary queries. Both member and summary queries is expressed in TSQL. The full page also allows for parallelism and allows specifying a federation key range other than all members. Tool has a help page with detailed notes on each of these capabilities.

Lets dive in and take a look at where summary queries can be useful. We’ll start with Ordering, TOP, aggregations and finally DISTINCT processing. By the way, for the examples in this post, I’ll continue to use the BlogsRUs_DB schema posted at the bottom of this article.

GROUP BY and HAVING with Fan-out Queries

With simple group-by items the rule is simple; if the grouping is aligned to the federation key in fan-out queries in federations, processing of group-by and having needs no special consideration. Simply union the results (or union-all to be precise) and we are done. However processing unaligned groupings (any grouping that does not include the federation key) requires a summary query.

When grouping isn’t aligned to federation key, grouping isn’t completely done with member queries. that means processing HAVING will generate incorrect results in member queries. Lets take the example form the previous post; here is the query simply reporting the months and counts of posts with more than a million posts;

SELECT DATEPART(mm, be.created_date) mon, COUNT(be.blog_entry_title) cnt
FROM blog_entries_tbl be
GROUP BY DATEPART(mm, be.created_date)
HAVING COUNT(be.blog_entry_title) > 100000

The grouping on month in each member yields the results from each member but grouping all members isn’t fully done yet! We need to GROUP_BY the same columns and expressions to finish grouping before we can apply the HAVING predicate.

So to correctly process HAVING predicate, we need to push t to the summary query;

SELECT DATEPART(mm, be.created_date) mon, COUNT(be.blog_entry_title) cnt
FROM blog_entries_tbl be
GROUP BY DATEPART(mm, be.created_date)

and the summary query should process the HAVING.

SELECT mon, sum(cnt) FROM #Table
GROUP BY mon
HAVING sum(cnt) > 100000

‘ORDER BY’ and ‘TOP’ with Fan-out Queries:

Lets start with ordering and TOP functionality in TSQL. The member queries can include ORDER BY and TOP but you will need the summary query to reprocess and finalize the ordering and top filtering of the member query results. Take the following example; The query is calculating the top 10 blogs created across the entire BlogsRUs_DB. Remember the blog_entries_tbl is federated on blog_id.

SELECT TOP 10 blog_entry_text FROM blog_entries_tbl
ORDER BY created_date DESC

Here is how to break this into a fan-out query with a member and a summary query; The #Table in the summary query refer to the resultset generated from the member query.

Member Query:
SELECT TOP 10 blog_entry_text, created_date FROM blog_entries_tbl
ORDER BY created_date DESC
Summary Query:
SELECT TOP 10 blog_entry_text FROM #Table
ORDER BY created_date DESC

Here is the output for the member query:

And the output with the summary query. Now the output only contains the true TOP 10 rows.

Additive Aggregates: MIN, MAX & SUM with Fan-out Queries:

When the processing of the MIN, MAX and SUM align with a grouping on the federation key, a UNION ALL of the results can simply yield the result. For example the count of blog entries per blog could be proceed with the following query;

SELECT blog_id, COUNT(blog_entry_id)
FROM blog_entries_tbl GROUP BY blog_id

Same applies to the OVER clause used for defining windows when processing aggregates. that is, as long as the PARTITION BY includes the federation key, a UNION ALL of the results is sufficient for the summary query.

However when the grouping does not align to the federation key, you will need summary queries. Here is the example that gets us the latest date that a blog entry was created. The grouping in the absence of a GROUP BY clause is a single bucket.

SELECT MAX(created_date) FROM blog_entries_tbl

Here is the way to break this down into a fan-out query;

Member Query:
SELECT MAX(created_date) FROM blog_entries_tbl
Summary Query:
SELECT MAX(Column1) FROM #Table

Fan-out Queries Processing AVG and COUNT

Average is a none additive aggregate thus it takes a rewrite to take an average when grouping does not align to the federation key. Here is an example of an average aggregate that aligns with a federation key: the next query calculates the average days between the blog entry and the last comment for the post for bloggers. the inner query gets the MAX date for the last comment on the blog entry. AVG is calculated per blogger for all the blog entries with the days between the create date of the blog and the last comment date on all blog entry.

SELECT blog_id, AVG(LEN(blog_entry_text))
FROM blog_entries_tbl
GROUP BY blog_id

There is no need for a summary query other than a UNION ALL of the results when fanning this query out given it is aligned to the federation key: blog_id.

However when the grouping is not aligned, there is work to do! Average and Count are not additive. That is, one cannot take 2 averages and average those to get the correct average or take 2 counts and use another count to calculate the output of 2 counts; we need SUM in the case of 2 counts to correctly get the final correct count and we need to use sum of values we want to average and the item count to calculate avg correctly from multiple member queries. Here is an example: Lets take the average length of blog entries across all each month. This time the grouping is on the month of the blog posts.

SELECT DATEPART(mm,be.created_date) month_of_entry,
AVG(LEN(blog_entry_text))
FROM blog_entries_tbl
GROUP BY DATEPART(mm,be.created_date)

Lets use SUM and COUNT to get to the correct average.

Member Query:
SELECT DATEPART(mm,created_date) month_of_entry,
SUM(LEN(blog_entry_text)) sum_len_blog_entry,
COUNT(blog_entry_text) count_blog_entry
FROM blog_entries_tbl
GROUP BY DATEPART(mm,created_date)

Summary Query:
SELECT month_of_entry,
SUM(sum_len_blog_entry)/SUM(count_blog_entry) avg_len_blog_entry
FROM #Table
GROUP BY month_of_entry

‘DISTINCT’ with Fan-out Queries:

DISTINCT is fairly easy to calculate much like the other additive aggregates. As long as the grouping is on the federation key. When grouping isn’t aligned to the federation key, a summary query reapplying the distinct for de-duplication is needed. Here is the query for DISTINCT count of languages used for blog comments across our entire dataset;

SELECT DISTINCT bec.language_id
FROM blog_entry_comments_tbl bec JOIN language_code_tbl lc
ON bec.language_id=lc.language_id

To break this apart, you have to run distinct in each member and then rerun the distinct on the summary query much like MIN and MAX;

Member Query:
SELECT DISTINCT bec.language_id
FROM blog_entry_comments_tbl bec JOIN language_code_tbl lc
ON bec.language_id=lc.language_id
Summary Query:
SELECT DISTINCT language_id FROM #Table

Now lets take a look at some more challenging none additive aggregates.

Fan-out Queries with TON N PERCENT and DISTINCT COUNT

TOP N PERCENT is not as popular as TOP N but for those who use it, lets dissect what you need to do with fanout queries. TOP N PERCENT simply require you collect all results without TOP from members and only apply the TOP N PERCENT in the summary query. Imagine the same query we used for TOP with TOP 10 PERCENT. Query gets latest 10% blog entries.

SELECT TOP 10 PERCENT blog_entry_text FROM blog_entries_tbl
ORDER BY created_date DESC

Here are the member and summary queries.

Member Query:
SELECT blog_entry_text FROM blog_entries_tbl
ORDER BY created_date DESC
Summary Query:
SELECT TOP 10 PERCENT blog_entry_text FROM #Table
ORDER BY create_date DESC

Alarm bell should go off whenever we are not able to push the filtration predicate (TOP clause) down to the member queries. So processing TOP N PERCENT is costlier that TOP N and additive keywords.

DISTINCT COUNT calculation is trivial if grouping is on the federation key. However when grouping isn’t aligned, it takes more work to calculate distinct count given that it isn’t additive. That means; you cannot simply add distinct-counts from each member given you may not know if you are counting certain things multiple times without full de-duplication of all the items. Here is an example; this query get the distinct count of languages per month across the whole resultset;

SELECT DATEPART(mm,created_date), COUNT(DISTINCT bec.language_id)
FROM blog_entry_comments_tbl bec JOIN language_code_tbl lc
ON bec.language_id=lc.language_id
GROUP BY DATEPART(mm,created_date)

We need to centralized processing so all comments language ids across days first need to be grouped across all members and then distinct can be calculated on that resultset. Here is the member and summary query for calculating distinct-count;

Member Query:
SELECT DISTINCT DATEPART(mm,created_date), bec.language_id
FROM blog_entry_comments_tbl bec JOIN language_code_tbl lc
ON bec.language_id=lc.language_id

Summary Query:
SELECT Column1, COUNT(DISTINCT language_id)
FROM #Table
GROUP BY Column1

Here is what the member query output looks like;

Here is the output with the summary query;

In conclusion, additive operations like MIN, MAX, COUNT or SUM and predicates like TOP or order by can easily be processed with fan-outs using simple modifications to original queries to break them into member and summary queries. With additive queries big advantage is that you can push these operation to the member queries for efficiencies. In cases where you need to deal with none-additive operations like average, there is some rewrite to help push filtrations and aggregate predicates to member queries. However there are a few cases like TOP N PERCENT or DISTINCT COUNT, where fan-out queries may require larger dataset shoveling thus will be more expensive to calculate because you cannot push these predicates to member queries and can only process them in the summary queries.

Happy fan-out querying.

Follow @cihangirb

*Sample Schema

Here is the schema I used for the sample queries above.

-- Connect to BlogsRUs_DB
CREATE FEDERATION Blogs_Federation(id bigint RANGE)
GO
USE FEDERATION blogs_federation (id=-1) WITH RESET, FILTERING=OFF
GO
CREATE TABLE blogs_tbl(
blog_id bigint not null,
user_id bigint not null,
blog_title nchar(256) not null,
created_date datetimeoffset not null DEFAULT getdate(),
updated_date datetimeoffset not null DEFAULT getdate(),
language_id bigint not null default 1,
primary key (blog_id)
)
FEDERATED ON (id=blog_id)
GO
CREATE TABLE blog_entries_tbl(
blog_id bigint not null,
blog_entry_id bigint not null,
blog_entry_title nchar(256) not null,
blog_entry_text nchar(2000) not null,
created_date datetimeoffset not null DEFAULT getdate(),
updated_date datetimeoffset not null DEFAULT getdate(),
language_id bigint not null default 1,
blog_style bigint null,
primary key (blog_entry_id,blog_id)
)
FEDERATED ON (id=blog_id)
GO
CREATE TABLE blog_entry_comments_tbl(
blog_id bigint not null,
blog_entry_id bigint not null,
blog_comment_id bigint not null,
blog_comment_title nchar(256) not null,
blog_comment_text nchar(2000) not null,
user_id bigint not null,
created_date datetimeoffset not null DEFAULT getdate(),
updated_date datetimeoffset not null DEFAULT getdate(),
language_id bigint not null default 1
primary key (blog_comment_id,blog_entry_id,blog_id)
)
FEDERATED ON (id=blog_id)
GO
CREATE TABLE language_code_tbl(
language_id bigint primary key,
name nchar(256) not null,
code nchar(256) not null
)
GO

Accessing Federations in SQL Azure using Entity Framework

Cihan Biyikoglu - SQL Azure — Wed, 11 Jan 2012 01:15:00 GMT

Entity Framework is a popular these days in many web applications and I get EF support in federations question a few times a week these days… Here is a quick collection of of articles on the topic;

This post just came out on the Entity Framework and Federations on the ADO.Net team blog;

SQL Azure Federations and the Entity Framework (ADO.Net Team blog)

SQLCAT folks, James, Rick and others;

A few of the limitations and workarounds are listed here;

Follow @cihangirb

Introduction to Fan-out Queries for Federations in SQL Azure (Part 1): Scalable Queries over Multiple Federation Members, MapReduce Style!

Cihan Biyikoglu - SQL Azure — Thu, 29 Dec 2011 22:55:00 GMT

Happy 2012 to all of you! 2011 has been a great year. We now have federations live in production with SQL Azure. So lets chat about fanout querying.

What is a fan-out query?

Federations provide a model for partitioning parts of your schema over to multiple member databases for harnessing scalability of many nodes. However applications still need for querying all of the data across federation members. Fan-out is a technique for querying data in your federation, across many federation members. Fan-out queries are much like map/reduce in that it is formed in 2 parts;

Member query is the piece that is sent over to all members involved in the query and
Summary query is the query that is the post processing piece to allow condensing the results from the member query to desired final result-set.

With fan-out queries the member query is always there but summary query may not be needed. For example if you are simply doing DML (we’ll have some examples like data pruning or reference data management etc) or DDL (we’ll look at schema deployment in detail below), fan-out would only have a member query but no summary query is needed. It is only when you need post processing, you need the summary query.

Last I want to say that fan-out queries are not exotic animals. Member query generates a resultset that is fed into the summary query to allow post processing. This kind of staged processing of results is something that many SQL developers already do in their stored procedure logic. This kind of results pipeline is similar to cases where you choose to use temp tables or table variables for staging the results in similar ways. OR if you have used CTEs (common table expressions) or views, you are using them to stage your processing or abstract processing logic to multiple stages. The shape of fan-out queries is a deliberate breakdown of the query processing based on your federation key but have great similarity to other techniques built into TSQL.

In the rest of the post, I’ll give you a tour of a tool and sample code that lets you do fanout processing and detail how to write fan-out queries with examples. Lets drill in.

Fan-out Query Utility

The sample utility is there to show how easy it is to execute fanout queries. Source for the sample is available on the site. You can visit the live version of the tool here; Recommend using the deployment closer to your SQL Azure deployment for efficiency;

Americas Deployment: http://federationsutility-scus.cloudapp.net/
European Deployment: http://federationsutility-weu.cloudapp.net/
Asian Deployment: http://federationsutility-seasia.cloudapp.net/

The basic fan-out query utility page shows you an example of a tool that can do member query. We will cover other parts of the tool in future posts but member queries are dead simple so wanted to show this for those of you who are looking for ways to deploy schema, maintain ref data etc. Simply go under the Home Menu and select the Fan-out Query Utility to access the page;

The tool simply take a connection string, federation name a query and executes the query (or batch) in all federation members. You can also get to the source code for the page through the link.

the member query is executed with the button click event for the “Submit Fanout Query”. Lets quickly walk through the code first;

First the connection is opened and three federation properties are initialized for constructing the USE FEDERATION statement: federation name, federation key name and the minimum value to take us to the first member.

49: // open connection
50: cn_sqlazure.Open();
51:
52: //get federation properties
53: str_federation_name = txt_federation_name.Text.ToString();
54:
55: //get distribution name
56: cm_federation_key_name.Parameters["@federationname"].Value = str_federation_name;
57: str_federation_key_name = cm_federation_key_name.ExecuteScalar().ToString();
58:
59: cm_first_federation_member_key_value.Parameters["@federationname"].Value = str_federation_name;
60: cm_next_federation_member_key_value.Parameters["@federationname"].Value = str_federation_name;
61:
62: //start from the first member with the absolute minimum value
63: str_next_federation_member_key_value = cm_first_federation_member_key_value.ExecuteScalar().ToString();

In the loop, the app constructs and executes the USE FEDERATION routing statement using the above three properties and through each iteration connect to the next member in line.

67: //construct command to route to next member
68: cm_routing.CommandText = string.Format("USE FEDERATION {0}({1}={2}) WITH RESET, FILTERING=OFF", str_federation_name , str_federation_key_name , str_next_federation_member_key_value);
69:
70: //route to the next member
71: cm_routing.ExecuteNonQuery();

Once the connection to the member is established to the member, query is executed through the DataAdapter.Fill method. The great thing about the DataAdapter.Fill method is that it automatically appends the rows or merges the rows into the DataSet.DataTables so as we iterate over the members, there is no additional work to do in the DataSet.

76: //get results into dataset
77: da_adhocsql.Fill(ds_adhocsql);

Once the execution of the query in the current member is complete, app grabs the range_high of the current federation member. Using this value, in the next iteration the app navigates to the next federation member. The value is discovered through “select cast(range_high as nvarchar) from sys.federation_member_distributions”

86: //get the value to navigate to the next member
87: str_next_federation_member_key_value = cm_next_federation_member_key_value.ExecuteScalar().ToString();

The condition for the loop is defined at line#71. Simply expresses looping until the range_high value returns NULL.

89: while (str_next_federation_member_key_value != String.Empty);

Fairly simple!

What can you do with Fan-out Query Utility tool?

Here are a few simple examples for member queries;

First, you can use the tool to do schema deployments to federations; To deploy schema to all my federation members, simply put a DDL statement in the query window… And it will run it in all federation members;

CREATE TABLE language_code_tbl(
    id bigint primary key,
    name nchar(256) not null,
    code nchar(256) not null);

I can also maintain reference data in federation members with this tool; Simply do the necessary CRUD to get the data into a new shape or simply delete and reinsert language_codes;

TRUNCATE TABLE language_code_tbl
INSERT INTO language_code_tbl VALUES(1,'US English','EN-US')
INSERT INTO language_code_tbl VALUES(2,'UK English','EN-UK')
INSERT INTO language_code_tbl VALUES(3,'CA English','EN-CA')
INSERT INTO language_code_tbl VALUES(4,'SA English','EN-SA')

Here is how to get the database names and database ids for all my federation members;

SELECT db_name(), db_id()

Here is what the output looks like from the tool;

Here are more ways to gather information from all federation members: This will capture information on connections to all my federation members;

SELECT b.member_id, a.*
FROM sys.dm_exec_sessions a CROSS APPLY sys.federation__member_distributions b

…Or I can do maintenance with stored procedures kicked off in all federation members. For example, here is how to update statistics in all my federation members;

EXEC sp_updatestats

For querying user data: Well, I can do queries that ‘Union All’ the results for me. Something like the following query where I get blog_ids and blog_titles for everyone who blogged about ‘Azure’ from my blogs_federation. By the way, you can find the full schema at the bottom of the post under the title ‘Sample Schema”.

SELECT b.blog_id, b.blog_title
FROM blogs_tbl b JOIN blog_entries_tbl be
ON b.blog_id=be.blog_id
WHERE blog_entry_title LIKE '%Azure%'

I can also do aggregations as long as grouping involves the federation key. That way I know all data that belongs to each group is only in one member. For the following query, my federation key is blog_id and I am looking for the count of blog entries about ‘Azure’ per blog.

SELECT b.blog_id, COUNT(be.blog_entry_title), MAX(be.created_date)
FROM blogs_tbl b JOIN blog_entries_tbl be
ON b.blog_id=be.blog_id
WHERE be.blog_entry_title LIKE '%Azure%'
GROUP BY b.blog_id

When do you need to think about a Summary Query?

Fanout queries with a simple member query is quite simple. However there are many cases you will need a summary query when writing fanout queries as well. Here is how you know that you need a summary query: If all you want to do it UNION ALL the results from member query, you don’t need a summary query. If I would like to do post processing such as order the whole resultset, aggregates data and get a grouping (GROUP BY) that does not align with the federation key, you need a summary query.

We’ll cover summary queries in a future “Part 2” post but as a teaser I’ll throw a few examples:

Imagine that I’d like to get the count of blog entries about ‘Azure’ that are created between Aug (8th month) and Dec (12th month) of the years.

SELECT DATEPART(mm, be.created_date), COUNT(be.blog_entry_title)
FROM blogs_tbl b JOIN blog_entries_tbl be
ON b.blog_id=be.blog_id
WHERE DATEPART(mm, be.created_date) between 8 and 12
AND be.blog_entry_title LIKE '%Azure%'
GROUP BY DATEPART(mm, be.created_date)

Here is the query results;

What’s wrong with this output? Well I was looking for a single row with full count for all blogs created on Aug 8th but I got a whole bunch of rows from each member.

Lets say I wanted to get the top 5 blog_ids with the busiest comment traffic. Here is TOP 5 query returning many more than 5 rows;

SELECT TOP 5 COUNT(*)
FROM blog_entry_comments_tbl
GROUP BY blog_id
ORDER BY 1

Just to make things exciting, I added the CROSS APPLY to sys.federation_member_distributions. that shows you the results are coming from many members. So the second column in the output below is the RANGE_LOW of the member the result is coming from.

Well, I think you get the idea. I’ll cover how we can process such queries using summary queries in Part II of the post.

So to wrap up, fan-out queries is the technique to execute queries across your federation members. With fanout queries, there are 2 parts to consider;

1- First, the part you execute in each federation member, that is the member query and

2- Second, the query part called summary query that is used to collapse the results from all members to a single result-set.

Yes this all sounds familiar: It is like map-reduce! The great thing about fan-out querying is that it can be done completely in parallel. All the member queries are executed by separate databases in SQL Azure. The down side is that you need to now consider your federation key and write 2 queries instead of 1.

Please let me know if you have feedback about that and everything else on this post; just comment on this blog post or contact me through the link that says ‘contact the author’.

Thanks and happy 2012!

Follow @cihangirb

If you like to read more about fan-out queries, here is Part 2 of the article.

*Sample Schema

Here is the schema I used for the sample queries above.

-- Connect to BlogsRUs_DB
CREATE FEDERATION Blogs_Federation(id bigint RANGE)
GO
USE FEDERATION blogs_federation (id=-1) WITH RESET, FILTERING=OFF
GO
CREATE TABLE blogs_tbl(
blog_id bigint not null,
user_id bigint not null,
blog_title nchar(256) not null,
created_date datetimeoffset not null DEFAULT getdate(),
updated_date datetimeoffset not null DEFAULT getdate(),
language_id bigint not null default 1,
primary key (blog_id)
)
FEDERATED ON (id=blog_id)
GO
CREATE TABLE blog_entries_tbl(
blog_id bigint not null,
blog_entry_id bigint not null,
blog_entry_title nchar(256) not null,
blog_entry_text nchar(2000) not null,
created_date datetimeoffset not null DEFAULT getdate(),
updated_date datetimeoffset not null DEFAULT getdate(),
language_id bigint not null default 1,
blog_style bigint null,
primary key (blog_entry_id,blog_id)
)
FEDERATED ON (id=blog_id)
GO
CREATE TABLE blog_entry_comments_tbl(
blog_id bigint not null,
blog_entry_id bigint not null,
blog_comment_id bigint not null,
blog_comment_title nchar(256) not null,
blog_comment_text nchar(2000) not null,
user_id bigint not null,
created_date datetimeoffset not null DEFAULT getdate(),
updated_date datetimeoffset not null DEFAULT getdate(),
language_id bigint not null default 1
primary key (blog_comment_id,blog_entry_id,blog_id)
)
FEDERATED ON (id=blog_id)
GO
CREATE TABLE language_code_tbl(
    language_id bigint primary key,
    name nchar(256) not null,
    code nchar(256) not null
    )
GO

…So Isn’t the Root Database a Bottleneck for Federations in SQL Azure?

Cihan Biyikoglu - SQL Azure — Thu, 15 Dec 2011 20:39:38 GMT

Dan posted a great comment to my previous blog post and I think the question he is raising is worth a blog post! Thanks for the question Dan!

Here is Dan’s question:

Dan - Tue, Dec 13 2011 11:57 AM

Cihan, thanks for keeping us all posted all the way until the D-day the 12th.

Having followed your posts and some Azure documentation (so far limited), there is 1 simple but key question that keeps bugging me, my team and quite likely many other developers - so far I have not found any reasonable explanation and I fail to understand on HOW and WHY having ALL(!) federation member requests go ALWAYS(!) through ONE(!) Federation Root DB would eliminate the SQL performance bottleneck problem that we are trying to get away with???

…

This is a common confusion I think we create with the root database but root, besides holding onto some metadata, really does not do the processing of transactions when it comes to federations. In fact, unless you have specific processing requests that happen in the root, root stays pretty idle. This is the magic of the USE FEDERATION statement.

Before I go into the detail of USE FEDERATION, let me dive into some architectural details of SQL Azure. SQL Azure physically has a separate layer in front of the database nodes called the services layer (a.k.a the gateway). Services layer owns managing connectivity, translation of logical to physical server names, some TSQL processing and a few other services. Services layer does some complicated TSQL processing; In fact processing of USE FEDERATION statement and all other CREATE/ALTER/DROP FEDERATION statements are done by the services layer not the platform layer (or the database nodes) in SQL Azure. Services layer is a scaled out tier and SQL Azure configuration controls the number of gateway nodes per cluster in SQL Azure. You can see the detailed picture of the layers below. You can find more details on SQL Azure architecture here.

Connections from your applications are distributed over to the services layer nodes through a load balancer. When a connection from a client application comes in, one of the services layer nodes handle the connection, translate which physical server now contains the logical server and database name in the connection string and establishes the connection to the database node in the platform layer.

Ok, so lets go back to federations and how federation do their processing: With federations, applications use the name of the root database in their connection string. At this point, a connection from the app to the services layer node is established (white arrows) and a connection from the services layer node to the root database node is established or if a pooled connection already exists to the root database it is reused (blue arrow).

In federation model, before you do transactional processing we require that you issue a routing statement called USE FEDERATION to work with your scaled-out schema. USE FEDERATION takes a federation name, a federation key value and some attributes. When USE FEDERATION is received by the services layer, the connection from the app to the services layer stay intact but the connection from the services layer to the database node is switched over to the federation member that contain the federation key value provided in the USE FEDERATION statement. So if you execute;

“USE FEDERATION orders_federation(tenant_id=155) …”

Connection from the application to Node#1 in services layer stays intact but the the services layer switches the connection from the root database to the federation member that contain tenant_id value 155 (white arrow from gateway node#1 to orders_federation member database node)

After this point all transaction processing, aside from a few TSQL statement gateway nodes process, happen directly on the federation member node. All your stored procedure executions to queries directly work with the node that contain the federation member which contains tenant_id 155. Each federation member and root database is spread over to various nodes in the platform layer so as more processing hit other federation members that contain tenant_id 655 or 1055, other nodes in the platform layer is engaged in processing those transactions.

At this point you may say, fine the transactional processing is decentralized but how about processing of USE FEDERATION itself? The information in the root database is the only way to figure out which federation member has tenant_id=155 or 655 or 1055? SQL Azure services layer is smart about that as well: For processing this connection routing, gateway nodes do caching of federation metadata and pooling of connections to process the USE FEDERATION command itself. That means while the cache and pool is getting built the root database receive queries asking for the federation map but after things get warmed up, services layer nodes do the heavy lifting.

So with the magic of USE FEDERATION statement both the processing of connection routing and the transaction processing in federation is decentralized! that gives you the ability to do processing much beyond capacity limits of a single node!

Hope this helps explain your question Dan!

-cihan biyikoglu

SQL Azure Federations is open for business!

Cihan Biyikoglu - SQL Azure — Mon, 12 Dec 2011 17:30:00 GMT

Wooohooowwwy!

Finally the day arrives! The year end update to SQL Azure is live and you can now use federations across all geographies of SQL Azure. There also a great new surprise we have been working on; we are making Federations available through open specification promise. OSP (open specification promise) is a promise not to assert patents against you for making, using, selling, offering for sale, importing or distributing any implementation that conforms to a Covered Specification. You can find the full definition here. This simply means that anyone in the open source community or in commercial development business can freely utilize the federations model in your libraries and utilize the same runtime model for representing distribution of data in your partitioning implementations. OSP for federations bring great interoperability and openness to the federations model. You can find more details on Ram’s blog here about details of the SQL Database Federations OSP.

Well, if you would like to get started, there are many how-tos and walk-throughs. here are a few links that will help;

Official SQL Azure products documentation on federations is available right here.
Windows Azure Training Kit has great examples for federations. You can also find the hands-on-lab right here.
Another UI based walkthrough is available on TechNet.
Fresh federations post on the Windows Azure team blog.

There are also a few chats and demos I have recorded over the last few weeks to help get you started on federations;

Here is a channel9 interview with myself and Scott Klein talking about federations technology in SQL Azure.
A demo of federations with the online management tools for SQL Azure.

We are obviously not done yet with federations. There will be more content, updates to the technology and other exciting news. Simply look for #sqlfederations in twitter or come to my blog regularly for updates.

Cihan Biyikoglu - Program Manager SQL Azure

Pricing and Billing Model for Federations in SQL Azure Explained!

Cihan Biyikoglu - SQL Azure — Mon, 12 Dec 2011 09:12:00 GMT

Note: the article has been updated with the new pricing model that went into effect in Feb 2012.

Now that Federations is live in production, lets talk about how the billing model works for federations. Good news is the model is very simple; With federations, every member can be considered a regular SQL Azure database. In fact, sys.database in master database report all federation members in the list. Federation members are marked using a special flag called is_federation_member in this view to help identify them as such.

Before I kick into explaining the federations billing model, if you are not familiar with the SQL Azure billing model, here is a quick overview. There are 3 important principles to remember as we discuss the billing model details for federation;

#1 – Charges on databases are prorated to a day. For example a single 1GB WEB EDITION database costs roughly $.33 a day.

#2 – All databases that existed in a day are charged even if they existed only for part of the day and was dropped during that day.

#3 – Only databases that are in ready state are charged. Databases that are being built are not charged. Charging is only done after these databases become accessible, that is you can connect and work with them.

Ok lets get started…

Creating Federations

The first federation member is created when the federation is created using the CREATE FEDERATION statement. The member inherit its EDITION and MAXSIZE from the root database. However like a new database. this new federation members contain no data so is an empty database.

Here is an example; Lets assume you have a user database with 25GB of data currently and is set to BUSINESS edition and has a MAXSIZE of 30GB. You decide to scale out the database and create a federation. Your first member created with this federation will have the same EDITION and MAXSIZE properties. However given that the member has no data yet, it will only cost you as much as the smallest BUSINESS edition database – that is 10GB.

Here is SalesDB with federation Orders_Fed after the first member has been modified to a MAXSIZE of 50GB and current data size grew to 48GBs after some data loading to the member.

Repartitioning Operations

Some more time passes and as you ALTER FEDERATION with SPLIT to scale out your database further, new members are created. These new federation members also inherit their properties from the source federation member that is being SPLIT. You can find the details of the SPLIT operation in this article but the important thing to remember is that the SPLIT operation does not reuse an existing database and always creates new databases to keep the operation online. From a billing standpoint, you can think of a SPLIT as two CREATE DATABASE statements to create the 2 new destination members, combined with a single DROP DATABASE to drop the source federation member.

First remember principle #3 above on SQL Azure billing: Only databases that are accessible are charged. So during the SPLIT operation, you don’t pay for the destination members that are created yet. You only pay for these new members after the operation completes.

Also remember principle #1 and #2 above; Billing is prorated to a day and every database is counted even if it existed only for the part of a day. That means the day of the SPLIT you pay for both the source and destination federation members. However the day after the SPLIT, source database is no longer charged to you since it is dropped. So you only pay for the 2 new members that are in place.

Typically the size of the destination members shrink given data will be filtered at the split point specified by the SPLIT operation. So the 2 new members are charged based on their size after the SPLIT.

Lets continue walking through our example; Imagine the federation member with a current size of 48GB is split into a 29GB and a 19GB federation members, your bill should look like this the day of the SPLIT;

…And should no longer include the source member the day after the SPLIT;

With ALTER FEDERATION to DROP members, we do reuse an existing database so form a billing standpoint, it equates to a DROP DATABASE statement dropping one of the members. In SQL Azure, databases are billed even if they existed only for a part of the day. So the day you run ALTER FEDERATION … DROP you still pay for both members. However here is what the bill will include the day after we run DROP AT (HIGH id=…)

Modifying Federation Member Billing Properties

It is also important to remember that federations are available at all editions; BUSINESS and WEB. You can mix and match EDITION and MAXSIZE setting with root and members. Federation root and each federation member can be altered using the ALTER DATABASE statement with the following options.

ALTER DATABASE database_name {
MODIFY (<edition_options> [, ..n])
}

<edition_options> ::= {
(MAXSIZE = {1|5|10|20|30|40|50|100|150} GB)
| (EDITION = {'web' | 'business'})
} [;]

You will need the member database name to use ALTER DATABASE. For the member database name. You can figure this out simply using db_name() function after switching to the member using the USE FEDERATION statement. You can follow along this article to figure out a federation members database name and other metadata on members.

Best Practices for Cost Optimizing Your Federations

As the final few words, I wanted to touch on an important topic. How do you cost optimize federations? Given the flexible nature of federation member setting on MAXSIZE and EDITION, I also get a lot of question on how to configure federation members. Should one have larger and fewer federation members OR smaller but many federation members?

With the updated pricing model on Feb 2012, SQL Azure now optimizes for consolidating data to larger databases. In Feb 2012, a single 50GB database roughly $4.06 a day. 50x1GB databases would cost $16.11. Both setups have access to the same storage capacity, however clearly 50x1GB databases have access to 50x more cores, memory and IOPS capacity as well as tempdbs and log files. Thus for cost conscious systems, you should choose to consolidate storage to fewer members, save on cost but risk higher latency for queries. However for mission critical workloads should invest more money and spread to many smaller members for better parallelism and performance. Given every applications workload characteristics are different, there isn't a declared balance-point for your app that I can declare but you can discover that by testing your workload and measure the query performance and cost under various setups with federations.

One thing is clear, in this new world with the updated pricing model in SQL Azure, it becomes even more important to build systems that are elastic. Apps that can adapt to changes in workload and, expand and in future shrink in time to handle the peaks gain great edge with the new pricing model. That makes federation a very valuable technology for apps that want to catch the best price performance. By the way if you'd like to shrink and consolidate data today, you can refer to this post; http://blogs.msdn.com/b/cbiyikoglu/archive/2012/02/20/implementing-alter-federation-merge-at-command-using-sql-azure-migration-wizard-by-gihuey.aspx

As always, love to hear feedback on your experience with federations and the billing model. you can reach me through the blog or at twitter @cihangirb.

Federation Metadata in SQL Azure Part 3 – Monitoring Ongoing Federation Operations

Cihan Biyikoglu - SQL Azure — Tue, 29 Nov 2011 02:56:59 GMT

In part 2 we talked about federation metadata history views. Federation history views only report operation that completed. For monitoring ongoing operations federations provide a separate set of dynamic management views under sys.dm_federation_operation*. All federation operations such as CREATE, ALTER or DROP consist of a set of steps that are executed async. With all async commands, a sync part of the command sets up and kicks off the operation first. Once the sync part is done, the control is returned to the executor of the TSQL. Then, SQL Azure in the background executes the async steps in the background. The initial sync part of these commands also set up the data for monitoring these async federation operations in the sys.dm_federation_operation* views. The views report metadata about the async operation such as the start date and time or the operation type that is running (ex: SPLIT or DROP etc) as well as the current progress of the operation.

Sys.dm_federation_operations represent the operations themselves whereas the sys.dm_federation_operation_members represents all members participating in the operation. Members are detailed as source or destination depending on the operation. In a SPLIT operation there are 2 destinations and 1 source for example.

For operations that are sync in nature, Sys.dm_exec_requests or sys.dm_exec_sessions provide great set of information. However with async operation such as federation operations these DMVs provide great information to let you explore the state after operations have been kicked off. Here are a few useful queries that can help you monitor your federations;

-- see how long a repartitioning operation has been active
select datediff(ss,start_date,getutcdate()) as total_seconds, percent_complete, *
from sys.dm_federation_operations
GO

-- display members with active federation repartitioning operations
SELECT fmc.member_id, 
  cast(fmc.range_low as nvarchar) range_low, 
  cast(fmc.range_high as nvarchar) range_high, 
  fops.federation_operation_type
FROM sys.federations f 
JOIN sys.federation_member_distributions fmc 
ON f.federation_id=fmc.federation_id 
LEFT OUTER JOIN (
 SELECT fo.federation_id, fom.member_id, 
    fo.federation_operation_type, fom.member_type
 FROM sys.dm_federation_operation_members fom 
 JOIN sys.dm_federation_operations fo
 ON fo.federation_operation_id = fom.federation_operation_id 
    AND fo.federation_operation_type='ALTER FEDERATION SPLIT' 
    AND fom.member_type='SOURCE') fops
ON f.federation_id=fops.federation_id AND fmc.member_id=fops.member_id 
ORDER BY f.name, fmc.range_low, fmc.range_high
GO

Federation operations are built with resiliency in mind. They have built in retry logic and in cases of unexpected events in the system such as node failovers or excessive performance issues, SQL Azure continues to retry, resume or restart the operations. However in the case of an unlikely failure, there is the additional sys.dm_federation_operation_error* views. They exactly mirror the sys.dm_federation_operation* DMVs but provide additional detail on the operation errors. It is important to note that these views should be empty under normal conditions however they are provided for additional system troubleshooting for support and engineers.

Enjoy.

-Cihan