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?
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.