Implementing MERGE command using SQL Azure Migration Wizard by @gihuey
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.