Federations: What’s Next? Announcements from TechED 2012

Federations: What’s Next? Announcements from TechED 2012

Rate This
  • Comments 3

Federations have been available for 6 months in SQL Azure (now called Windows Azure SQL Database) as of today! In thisimage post, I’ll first cover few of the improvements we have made to the SQL Database Federations and talk about a few of the announcement we made at Teched this week on what’s next for the technology.



Improved Latency for USE FEDERATION: One of the recent changes that is key to performance of your scale-out systems is the improvements we made to the “USE FEDERATION” statement. With the improvement the latency of USE FEDERATION drastically improved. Internally with USE FEDERATION, we now have 2 types of caching.

  • Connection Pool to the Backend: As applications go back to a hot member, with the pooled connections, they won’t have to reestablish new connections from the GW to the DB nodes in the system and will reuse the existing pooled connection lowering latency for the app.
  • Caching of the Federation Map: USE FEDERATION is used for routing your connections to the given federation key value. This information reside in the root database but it is cached at the gateway layer in the system. This means the root database isn’t ever hit for executing the USE FEDERATION stmnt. This lowers latency and makes USE FEDERATION extremely efficient.

USE FEDERATION connection management extremely efficient for applications by allowing them to point to a single connection string (to a single endpoint name that the server scale out). This solves a huge connection management problem known as connection pool fragmentation. This is a nasty problem and with USE FEDERATION you never have to learn or be aware of the issue.



Support for “Timestamp”, “Rowversion” and “Identity” on Reference Tables: With the upcoming update to SQL Azure, This will enable using IDENTITY property and timestamp data type on reference tables. Federated tables will still be restricted. This means your schema in the federation member can now have the following reference table without issues:

CREATE TABLE zipcodes(
id bigint identity primary key,
modified_date datetime2,
ts timestamp)

Manual Setup with Data Sync Service: Within the next few months we will also have manual setup enabled with Data Sync Services. Data Sync Service can be used for operation like synchronization of reference data between federation members and root or for moving your data in federations to SQL Server or to other Windows Azure SQL dbs.

Federation SWITCH Operation: With the improvements in federations we will make moving federation members in and out for the federation easy as well though an ALTER FEDERATION statement. With the SWITCH IN and OUT operation, it becomes easy to compose and decompose federations. Note: The syntax shown below is just a placeholder and the final version may be different.



Database Copy (DBCopy) Support for Federations: We will also enable DBCopy command for federation root or member database. You will be able to point to a federation member of a root with copy database command.

CREATE DATABSAE [Customers-100-200]
AS COPY OF [system-d6c763f4-eda2-427e-af9e-3a8fedd4a16c]



Disaster Recovery with Federations: There are a number of disaster recovery improvements we are working on with Windows Azure SQL Database. The general idea is to enable better local and geographic disaster recovery scenarios for your critical data. To simplify DR, we will make point-in-time-restore operation available that enable you to recover from user and admin errors. With Geo-DR, we will enable ability to make your data redundantly available in multiple data center and continuously keep them in sync. You can find more information about them on this session from Sasha: Business Continuity Solutions in Microsoft SQL Azure

Point-in-time-Restore support with Federations: As we make point in time restore available, we will also enable the ability to do point in time restores of federation data in the root or the members. Point in time restore technology allows recovering from user and admin mistakes like dropping tables or deleting a whole bunch of rows accidentally. PITR allows travel back into time, much like database RESTORE command. Provide a data and time for a database and we can restore a snapshot of it for the given time slot..


Geo Disaster Recovery: Again as we make the geo disaster recovery features available with Windows Azure SQL Database (SQL Azure). You will be able to make the members and root geo redundant with the capabilities. that is you will be ale to have your data in multiple data centers to protect against data center level failures.


Love to hear feedback on all of these features. If you have questions, simply email through the blog or post comments.

Many Thanks

-cihan biyikoglu

  • Any idea as to when Federations will get MARS support? (needed to use Lazy Loading with Entity Framerwork)

  • Things look great.  The biggest clincher for us is indexed views.  If we could create indexed views that could sit in each federation member, or, ideally across all, we would be able to successfully use SQL Azure for data warehousing.

    We need ROLAP-style indexed view aggregations on the data to improve query performance.  Is this coming or has it fallen off the radar?

  • Are there any plans to add the FEDERATION functionality to SQL Server 2012? I would allow us to use the exact same database schema for both in-house and cloud databases. In-house would be a multi-tenant database with a single tenant (or a few tenants for independent teams) and cloud would obviously have lots of tenants. I read that SQL Server 2012 and SQL Azure are basically built on the same code base, so this should not be terribly difficult.

Page 1 of 1 (3 items)