MERGE command for SQL Database Federations RRS feed

  • General discussion

  • Hi,

    I am a big fan of the Federations feature in Windows Azure SQL Databases, which allows to easily scale-out the computing power available in the data layer. One of the greatest things behind Federations is the SPLIT command which allows replacing a database member by two new ones and thus split the load that once was served by one instance between two instances. The implementation behind the SPLIT command is quite clever, since it is being performed with almost zero down time (very well explained here), being a so called online operation.

    A MERGE operation would be the symmetric opposite of SPLIT and would join two members into a single member. Just like the SPLIT, MERGE would have to be an online operation (no downtime while being performed). A better description on how it should work is available here.

    There are two main scenarios I see for MERGE. The first one would be true elasticity at the database layer. The SPLIT command makes it really easy to scale-out when the demand on the application is high, the MERGE command would make it easy to scale-down when the demand is not as big anymore.

    The second, and potentially more important scenario, is flexibility on the splitting strategy. When creating a federated database, you might start with a generic federation strategy. Over time you might discover that some of your data is much more accessed than other so you might want to optimize the partition strategy. In order to do that you will inevitably need both SPLIT and MERGE operations.

    The problem with MERGE is that it does not yet exist and it also does not seem to be on the road map for the near future. The reason I started this discussion is because I wanted to hear opinions from others related to the MERGE feature. Are there any other reasons besides the ones above why you would see it useful? Or, are there any reasons why you don't think it is really needed?

    For those who would like to see MERGE implemented in the future versions of Windows Azure SQL Database Federations you can vote for it on the Azure feature voting site here.

    Before ending my post I would like to add just a couple more notes. First of all, I am aware that MERGE can be implemented as a manual process by using data migration tools like the SQL Azure Migration Wizard. However, such manual operations will require downtime every time they are performed and also they are not part of a thoroughly tested process (if the Azure team is to implement the MERGE feature it would spend infinitely more time testing it than any of us would have the chance to do for our manual implementations).

    The second and final note is that I am aware that from a technical point of view MERGE is more difficult to implement than SPLIT (especially considering that the federation members are actually just individual databases that can be altered independently from each other), but probably still not impossible to do (probably with some constraints on the members).

    Read my technical blog: | Connect with me on twitter: @fdumitrescu

    Tuesday, June 19, 2012 12:51 PM

All replies

  • Hi,

    Agreed, I think MERGE operation if members have the same schema would be nice as V1 :)

    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful". Best Regards.

    Tuesday, June 19, 2012 1:11 PM