ALTER FEDERATION SWITCH operation failed. Specified boundary value does not exist

Question

We have a Windows Azure Federated database, which we need to turn into a normal database (due to Federations being retired shortly).

Having read through copious amounts of documentation and tried various things, the answer seems to be the ALTER FEDERATION ... SWITCH OUT AT command:-

https://msdn.microsoft.com/library/dn269988.aspx

Removes all federation metadata and constraints from the federation member database. After execution, the federation member is a standalone database.

The format for the command is given as:-

ALTER FEDERATION federation_name SWITCH OUT AT ([LOW | HIGH] distribution_name = boundary_value)

LOW or HIGH determines the federation member that will be switched out on the respective side of the given federation boundary_value. The boundary value must correspond to an existing partition value, range-high or range-low, in the existing federation.

and there is a specific example to switch out the Federation with a boundary of 99:-

ALTER FEDERATION CustomerFederation SWITCH OUT AT (LOW cid = 100)

So, taking all of the above information, I queried the Federation values, which returned the following:-

SELECT * FROM sys.federations  
federation_id : 65536  
name : CustomerFederation  

SELECT * FROM sys.federation_members  
federation_id : 65536  
member_id : 65536  

SELECT * FROM sys.federation_distributions  
federation_id : 65536  
distribution_name : cid  
distribution_type : RANGE  
system_type_id : 127  
max_length : 8  
precision : 19  
scale : 0  
collation_name : NULL  
user_type_id : 127  
boundary_value_in_high : 1  

SELECT * FROM sys.federation_member_distributions  
federation_id : 65536  
member_id : 65536  
distribution_name : cid  
range_low : -9223372036854775808  
range_high : NULL  

However, no matter what value I try to use for boundary_value, I get the following:-

Msg 45026, Level 16, State 1, Line 1  
ALTER FEDERATION SWITCH operation failed. Specified boundary value does not exist for federation distribution cid and federation CustomerFederation.

I've tried using the range_low value:-

ALTER FEDERATION CustomerFederation SWITCH OUT AT (LOW cid = -9223372036854775808)  
ALTER FEDERATION CustomerFederation SWITCH OUT AT (HIGH cid = -9223372036854775808)  

I've also tried one either side of that value, as the example used 100 to SWITCH OUT 99

I've tried using 0, as that's the value I use to connect to the Federation, but that gives the same error, as does -1 and 1, for both LOW and HIGH.

I've also tried specifying to use the Federation Root before running the command:-

USE FEDERATION ROOT WITH RESET  
GO

ALTER FEDERATION CustomerFederation SWITCH OUT AT (LOW cid = -9223372036854775808)

I have tried running it from the main database and from the Federation.

Has anyone successfully used the ALTER FEDERATION ... SWITCH OUT AT command and can point me in the right direction please?

Answer 1

After hunting around some more, I found a link to a Federation Migration Utiluty:

https://code.msdn.microsoft.com/vstudio/Federations-Migration-ce61e9c1

Looking over the code, it appeared that the correct command was one I'd already tried:

ALTER FEDERATION CustomerFederation SWITCH OUT AT (HIGH cid = -9223372036854775808)

This time it worked. Not sure why it didn't the first time, possibly something else I'd tried before it had thrown it out.