THIS TOPIC APPLIES TO: noSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Drops a federation, including data contained in the federation, in Azure SQL Database.


The current implementation of Federations will be retired with Web and Business service tiers. Consider deploying custom sharding solutions to maximize scalability, flexibility, and performance. For more information about custom sharding, see Scaling Out Azure SQL Databases.

DROP FEDERATION federation_name


The name of the federation to be dropped.

Properties of the SPLIT operation

  • The DROP FEDERATION statement must be the only statement in the batch, and cannot be part of an outer transaction

  • The DROP FEDERATION statement can only be executed while connected to the federation root database.

  • The DROP FEDERTION operation is an asynchronous operation.

    When DROP is complete;

    1. All metadata and logical objects about the federation are cleaned up, and all of the federation members are dropped. The total time it takes to drop all federation members varies depending on the number of members in the federation.

    2. Existing connections and pooled connections to federation members are closed as the members are dropped.

    3. All existing connections to the federation members are disconnected. The dropped federation member no longer accepts connections.

Concurrency of the DROP FEDERATION operation

The DROP FEDERATION operation can be performed while ALTER FEDERATION operations such as SPLIT or DROP are in progress. In this case, DROP FEDERATION takes precedence and will cancel operations on all remaining federation members before dropping them.

To drop federations, you must be connected to the federation root and must be a member of the dbmanager role on the server. You must also be a member of the dbo group for the database.

You must have CREATE and DROP DATABASE permissions on the source federation members and on the server that you are connected to. In Windows Azure SQL Database this is restricted to the members of the dbmanager server role. The owner of the source federation member becomes the owner of the destination federation member, regardless of the user account executing the operation.

Community Additions