Scaling Out Azure SQL Databases
Updated: April 24, 2014
Microsoft Azure SQL Database (Azure SQL Database) is built on standardized hardware that is owned, hosted, and maintained by Microsoft. When additional resources are required to accommodate an increase in workload, unlike traditional on-premises databases that scale up by increasing hardware, Azure SQL Database requires the use of scale out solutions. Scaling out in Azure SQL Database requires sharding, which horizontally partitions data across multiple databases. Each database in this model is referred to as a shard.
Approaches for Scaling Out
Azure SQL Database supports two methods to implement sharding:
- Custom Sharding
- Design custom sharding solutions to maximize scalability and flexibility for resource intense workloads. These solutions use application code across multiple databases.
- The Federation feature in Azure SQL Database automatically partitions data horizontally based on a federation distribution key. For more information about Federations, see Federations 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.|
Comparing Approaches for Scaling Out
Although the built-in capability for shard management that Federations offer may simplify some administrative tasks, databases using this approach have several limitations. These limitations include:
No support for Import/Export service on federation members.
Maximum number of shards per federation is limited to the maximum number of databases per server minus 2 (one master, one root). Currently the maximum number of databases per server is 150, so the default limit is 148 shards. Upon request, Microsoft can increase the maximum number of databases per server to 500 which results in a maximum of 498 shards.
Premium Edition is not supported on federation members.
Multiple concurrent USE FEDERATION commands may lead to scaling or performance issues.
SPLIT and DROP operations may run for extended periods of time.
Removing federations requires manually copying data out of each shard. This often requires application downtime.
In comparison, custom sharding solutions require more time to design and implement, but allow customers to overcome these limitations.
Recommendations for Scaling Out
In order to create the most scalable solutions, customers developing new applications should follow a custom sharding approach. Customers with existing Federation solutions can contact Microsoft support for assistance with converting to a custom sharding solution.