hostdude99 on Thu, 07 Jun 2012 18:58:37
I have a scenario where I have multiple clients within one database but each client has their own set of tables. The table names are prefaced with the client's unique GUID. Are there benefits to using Federations in this scenario or is the real benefit when the data for all clients are commingled in the same tables?
FlorinDumitrescu on Thu, 07 Jun 2012 20:18:29
SQL Azure Federations are built to enable horizontal partitioning for SQL Azure, which at its core involves distributing the rows of a table through multiple database instances. Your case, with data split at table rather that at row level, does not fit into that scenario.
You could model your scenario by creating individual federations for individual clients or for groups of clients. A newly created federation contains only one member which is actually a SQL Azure instance, so it would be like distributing the client tables between standalone databases.
There are two advantages I see out of using the Federations. First of all, if the usage from one of your clients becomes very intense, you could move that client's tables to a individual federation which you could horizontally scale with SPLIT operations.
The second advantage is that you will access all your data with a single connection string and avoid the potential danger of connection pool fragmentation.
But that being said, there are also disadvantages. First of all, distributing the tables from different clients between federations will have to be a manual operation, just as it would be if you would use standalone SQL Azure databases. You should be able to use tools like SQL Azure Migration Wizard in order to ease your work, but you will not get any built in advantage from using Federations.
Then, another disadvantage would be that Federated databases are bit harder to backup than the standalone ones.