Building a Large-scale Database with Azure SQL
Windows Azure SQL Database–the cloud-based relational database platform–is a great platform for building dynamic solutions. And we're not just saying that because we employ it for our solutions–and it's our job to stay up-to-date with it front to back–but because it offers a little known but impactful feature called federations. Federations enable anyone to build a self-scaling application by allowing them to partition selected tables–called federation members, across multiple databases. SQL database instances can be created programmatically without any configuration and require very little administration.
Let's take a look at a sample application. Say we have a SaaS application where we provide services to approximately 1,000 companies, but the application is beginning to slow down due to the amount of data in our database. Using federations, we can split this data into two physical databases.
The first will contain customers one through 500 and the second will contain customers 501 through 1000. We just doubled throughput by splitting it into two lists. And this split can be accomplished without downtime while the application is still running. If needed, these databases can even be split again further.
For easy use, federations appear as a single database to the application, regardless of how many federation members there are. Federations also help applications access data, keeping complexity out of the application layer and providing these key benefits:
- Massive Scale: Federations bring in the sharing pattern in Azure SQL and allow the harnessing of massive capacity at the database tier.
- Best Economics: With federations, database tiers become truly elastic. Administrators can repartition applications based on workload. With federations, there is no downtime required for repartitioning operations.
- Simplified Development and Administration: By managing federations and repartitioning operations online at runtime, federations greatly simplify management of databases at scale.
While this is just a brief overview of federations, there are a few design considerations. When designing a federation, one of the most important decisions is what value to federate on. Ideally, you want to select a key that allows you to federate data from multiple related tables, so that related rows are stored together. Additionally, it's really important to consider how best to insert new records so that all federation members are equally used, rather than storing all new records as one member. This is something that needs to be decided on in the early stages.
Feel free to contact us at Devbridge if you'd like more information on federations and how to work with this feature of SQL.