SQL Azure Database Scalability with Federations
Cihan Biyikoglu introduced an upcoming feature for scalability in SQL Azure databases called Federations at Tech Ed 2011. In his presentation he explains that Federations are objects inside an Azure database which allow the data they contain to scale. This is done through the use of federation members, which are additional databases; each containing part of the data that the Federation holds. Data is distributed across the Federation members according to the Federation distribution key, which is defined when the Federation is created. A block of data containing the same distribution key is considered an atomic unit and will never be split across multiple federation members. The SPLIT and MERGE commands allow the number of federation members to be increased or decreased at runtime.
While the individual Federation member databases are directly accessible, the model expects that connections will be made through the Federation root database. After the connection is made, the USE FEDERATION statement must be executed in order to tell the system what Federation to run the following queries against. Federation members contain both Federated data and reference data. Federated data is data that is split across multiple federation members while reference data is lookup data that is cloned across all members.
We contacted Cihan Biyikoglu to ask him a few questions.
InfoQ: The root db seems like a single point of failure - can the root db be mirrored? Is there any recommended method for boosting reliability with this framework?
Cihan Biyikoglu: In sql azure, we already provide HA built in with 3 copies of your database. root db is made available by sql azure internally keeping 3 copies of the database.
InfoQ: How do operations that act on an entire table function when the table is split across multiple databases?
Cihan Biyikoglu: In the federations model, Apps have to be aware of partitioning of data. No doubt about that. That said, there is a safe mode for developers to operate on in federations; that is called an atomic unit. If you target atomic units (AUs) only, you don’t have a problem with any repartitioning operation. We don’t split an atomic unit. However for cases like fan-out querying or schema deployment, you would typically target a larger range of data as opposed to AUs. For these operations, there is some help in v1 through metadata to discover which range you are targeting. Fairly simple piece of code can help you ensure you can safely address ranges of data in the presence of repartitioning operations. However it takes awareness in app to do this. In future we plan to enhance this support and make that even easier.
InfoQ: Is there any way to influence the resources for Foundation Members? For example, maybe newer data receives 100x the traffic of older data, so one FM receives 1 request a second and another receives 100 requests per second.
Cihan Biyikoglu: This is very common for partitioned apps and federations as well; you can express this by setting your physical distribution to accommodate this. You get to pick the SPLIT points and SKU and size of data in each fed member.
InfoQ: Will Azure Foundations work out-of-the box with ORMs such as the Entity Framework, Link to SQL, and nHibernate?
Cihan Biyikoglu: Teams are working on this as we speak. We may not have full blown support for existing versions but we will lay out how one can work with ORMs existing versions with federations and some will have native support in future for federations.
InfoQ:After a table is created, can you use ALTER TABLE to change its type from Federated to Reference or Central?
Cihan Biyikoglu: No architectural limitations on why we could not do this but we decided to scope this out from v1. So there is no way to do this in v1 but in the future we may enable this.
Nominations for the Federations technology preview program can be submitted via the link on Cihan’s blog. Cihan can be contacted via email at firstname.lastname@example.org.
Todd Montgomery Dec 19, 2014