sql-serverazuresql-azure-federations

Azure Federations are Deprecated. What are my options?

The background:

We have an application where the main entity is a customer. All information in this applications starts from the customer. We thought it would be really nice if we can use this for some kind of partitioning. We designed the service with Azure SQL Database as a backend.

Our tables look like this (only the relevant part is left for brevity):

TABLE dbo.Orders
(
     CustomerId INT NOT NULL DEFAULT( FEDERATION_FILTERING_VALUE( 'FEDERATION_BY_CUSTOMER' ) ),
     OrderId INT NOT NULL,
     ....,
     CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED ( CustomerId, OrderId )
) FEDERATED ON ( FEDERATION_BY_CUSTOMER = CustomerId );

Now this allowed us to do some crazy things. Our entry points to all SQL related stuff always contains the following command first:

USE FEDERATION GroupFederation( FEDERATION_BY_CUSTOMER = 1 ) WITH RESET, FILTERING = ON

In this case this statement:

SELECT * FROM Orders

or

INSERT INTO Orders ( OrderId ) VALUES ( 10 );

will work without a problem, working only on the data of the given customer. The CustomerId COLUMN will always be inferred from the system function FEDERATION_FILTERING_VALUE;

Now we could have all our customer in a single database without a problem, and they would be isolated from each other. If sometime in the future, one of them got too big, we could SPLIT the federation at that particular customer ID and we don't have to change anything in our code to support it.

Heck, we could have each customer in separated federation database and the service using it wouldn't know a single thing about it.

We were very happy with our solution and I thought I was very clever coming up with it. Not until recently when Microsoft announced that they are deprecating the azure federations feature with the new azure database editions that are coming up. Read more about it here and here.

I hope you see my problem. What do you think my alternatives are? Do you use Azure Federations and how are you going to transition?

Thank you.

Solution

  • We have seen that custom sharding solutions typically lead to better results regarding scalability, flexibility, and performance as compared to Federations. You can find more information about Federations and custom sharding here: http://msdn.microsoft.com/en-us/library/dn495641.aspx. This is part of the reason for the announcement to retire Federations together with the Web and Business Editions in Windows Azure SQL Database.

    I would encourage you to look into self-sharding as an alternative. There was good guidance published last year by the CAT team around self-sharding patterns at http://social.technet.microsoft.com/wiki/contents/articles/17987.cloud-service-fundamentals.aspx , and more material like this will be coming shortly.

    Feel free to reach out to me to discuss your alternatives for migrating your existing Federations application. I am part of the Azure DB product team and you can reach me at torsteng(at)microsoft.com

    Thanks,

    Torsten