Introduction

The Azure SQL DB federations feature is being retired along with the Web/Business editions in September 2015. At that point in time, applications that utilize the federations feature will cease to execute. To ensure a successful migration, it is highly encouraged that migration efforts begin as soon as possible to allow for sufficient planning and execution.

The purpose of the Federations Migration Utility is to assist in the migration of a Federations application to an elastic database tools application.  The utility performs two distinct functions: 1) creating a Shard Map Manager and Shard Maps as a copy of the Federations in a Federation Root, and 2) converting Federation members into regular Azure SQL databases.

For a conceptual overview, please see the following video.  To learn more about elastic database tools, get started here.

Building the Utility

Simply build and execute the sample.  The Federations Migration Utility is an interactive utility that will prompt you to select what actions you want to perform.

Description

1) Creating a Shard Map Manager and Shard Maps

The Federations Migration Utility creates Shard Maps to represent all the Federations in the targeted Federation root. The utility first reads the Federations metadata from the targeted Federations root, including distribution key data type and members' key ranges.  It then connects to each member to get the database name.  From this information, the utility builds a Shard Map for each Federation.  


The end result is a Shard Map Manager and Shard Map for each Federation - with each Shard Map containing a range mapping for every Federation member.  Please note that the Shard MapManager database will incur charges no different than any other Azure SQL database.  By default, the Shard Map Manager is created as a Standard (S0) database.  The edition and performance level of the database can easily be changed in the portal.

The Federations Migration Utility can build the Shard Map on a separate database (recommended), or on the Federation Root.  With either of these options, the utility will create a new Shard Map Manager on the specified database, which will overwrite any existing shard maps on that target database.

2) Converting Federation members into Regular Databases

The Federations Migration Utility uses the ALTER FEDERATION ... SWITCH OUT command to convert all Federation members of a particular Federation to regular databases.  It does this by stripping away all Federation metadata and updating all federation records to reflect that the databases are no longer Federation members. After this action is done, the following functions are possible:

Issuing a SWITCH OUT against a Federation member is a one-way change and is permanent.  Once a SWITCH OUT has been issued against a Federation Member, the database will NOT be able to participate in the Federation any more. 
Please be aware that it is not possible to build a complete shard map once a SWITCH OUT has been issued against any Federation member.  Please be sure that you have created a current shard map before issuing a SWITCH OUT against your Federation member databases.

Using the Federations Migration Utility

The Federations Migration Utility is an interactive utility that will prompt you to select what actions you want to perform.

1) Select what action(s) you want to perform: 

(1) Create a Shard Map Manager as a copy of a Federations Root   

(2) SWITCH OUT all Federation members in a Federation

2) Enter information needed to access your Federation root database and associated Federation members.

3) (If creating a Shard Map Manager) Decide whether to create the Shard Map Manager and Shard Maps on a separate database (recommended) or on the Federation root.   

4) (If creating separate Shard Map Manager database): Enter the name for the Shard Map Manager database, or enter nothing to use the default name: <Federation Root DB>_SMM   

5) (If switching out Federations members): Enter the name of the Federation whose members you want to switch out.   

6) (If switching out Federation members): Re-enter the name of your Federations root database to confirm that you really want to make this change.   

7) Actions will be performed. The utility will print out the names of any created shard maps and will confirm that the shard maps were successfully switched out.

Troubleshooting

Common Error Messages:

Interruption during the creation of the Shard Map Manager: if the building of a Shard Map Manager is interrupted, simply run the utility again. The utility will create new shard map manager and overwrite the partially created shard map.

Interruption of Federation member switch out: if some members were switched out and the process was interrupted, run the utility again.  The utility will ignore the switched out members and switch out the rest.

Source Code Files

More Information

For more information, please see the following:

SqlTypeConverter.cs - Method to convert byte array to a T-SQL literal.