none
SQL Azure Federation Questions

    Question

  • (Moved post from SQL Azure Labs Support)
    I have been reading through Cihan Biyikoglu's blog posts about SQL Azure Federation (great posts by the way!) and had a few questions.  Forgive me if I'm not using the correct terminology.  From what I understand you create a federation and within that you have various federation members split up by a range of your federation keys and then within an individual federation member you have various atomic units with distinct federation keys.
    1. I presume that since each federation member resides in its own SQL Azure database, if you perform an operation that locks a specific table, the only connections that will be blocked will be those atomic units that reside in the same federation member.  But, if you have filtering turned on and you perform a locking operation, will only the connections using the same federation key be blocked or will all atomic units in that federation member be blocked as well?
    2. Since a federation member resides in its own SQL Azure database, can you create backups or copies of the individual federation member?  Or can you only create a backup/copy of the entire root database?
    3. I think this might have been addressed in one of the blog posts but I wasn't entirely clear on it.  If you have filtering turned on and you are inserting a new record into a table, do you have to specify the federation key value or will that be automatically inserted?
    4. The blog posts give the example of creating an Orders federation that is split up based upon the customer_id.  Lets take a more complex application with many more components, lets say an application used to manage an apartment complex where you can enter invoices, work orders, keep track of all your residents, and manage your bank accounts.  Would it be best to create four separate federations, invoice_federation, workorder_federation, etc, all based on the same federation key of customer_id?  Or would it be better to create a single apartment_federation that contains all of the tables, invoices, workorders, etc?  My thought process was that if the various tables (invoices, workorders, etc) need to work together then it would be necessary to keep them all in a single federation as you can't currently do joins across multiple federations.  If you follow the latter idea then essentially your federation would contain all the entities for a given customer and you would be just splitting the customers databases over any number of nodes.
    Any thoughts on the above questions would be appreciated.  
    Thank you
    Nick

    Nick's Programming Tips
    Thursday, April 28, 2011 7:11 PM

All replies

  • Hi Nick, Federations is still in development and some of the details are still being worked out.

    #1 - yes you are correct.

    #2 - we do backup databases internally in sql azure and federation members are in no way special. DBCopy may not be supported in v1, however would love to hear feedback about that. What level would you like the copy to work? db level or the root db and all its members in all of its federations?

    #3 - yes on filtering connection you need to have specified the filtering value already. Not clear on what you are referring to with the value that is auto insert however. Can you expand on that?

    #4 - good question and the answer is it depends. If you 'd like to independantly scale-out these collection of tables, you can create multiple federations. You may choose to create seperate federations if the federation key you like to use is different for each case. If the # of nodes you like to engage for the workload on the table is wildly different, you may choose to use different federations as well. In general, the overhead of cross federation joins is large thus folks tend to keep entities that can be federated with the same distribution key aligned in a single federation for ease of use.

    Many Thanks

    -cihan

     

    Thursday, May 5, 2011 5:44 AM
  • Cihan,

    Thanks for the answers.  Find more information and questions below:

    #1 - What is the "Yes you are correct" referring to?  I have mentioned two things.  Will connections from all atomic units, the entire federation member, be blocked regardless of which unit is performing the operation or will only connections using the same federation key be blocked?

    #2 - It would be great to be able to perform both db and root db backups.  In fact, what would be even nicer is to be able to backup a single atomic unit.  I know that is asking a lot but that coupled with a way to restore an atomic unit would resolve the all too often talked about multi-tenant database issue of not being able to backup and restore a single tenant when a client calls and says they accidentally did something that is unrepairable.  Again, I know that is asking a lot but any possibility of such a feature?

    #3 - From what I understand, if you have an Orders table federated on the ClientId and you have filtering turned on, you can simply right the statement "SELECT * FROM Orders" and it will only return the Order entities matching the federation key used when the connection to the database was made and filter is turned on.  My question was if you are inserting an Order with filtering turned on do you need to supply the ClientId in the Insert statement, "INSERT INTO Orders (Date, ..., SubTotal, Total, ClientId) VALUES ('2011-5-5', ..., 200, 200, 1)" or with filtering can you leave off the ClientId value and will the filtering feature automatically insert it for you? So the insert statement would be "INSERT INTO Orders (Date, ..., SubTotal, Total) VALUES ('2011-5-5', ..., 200, 200)", ie not specifying the ClientId.  Not an issue either way, I was just wondering out of curiosity.

    #4 - Good information.  Thanks.

    Nick

     


    Nick's Programming Tips
    Thursday, May 5, 2011 4:54 PM
  • Any thoughts on the above questions?
    Nick's Programming Tips
    Friday, May 13, 2011 2:13 PM
  • Cihan announced the opening of a product evaluation program for SQL Azure Federations.

    #2 is question 53 in the nomination survey.

    #3 is answered by the following post by Cihan:

    -- With the FILTERING connection option set, the query on customers table “SELECT … FROM dbo.customers” will return only rows that have customer_id=55. That is the query execution engine will insert the predicate, customer_id=55” automagically for you when the target of your query is a federated table. This is true for all types of statements including INSERT, DELETE and UPDATE statements. The connection works just like a constraint. If you try to INSERT or UPDATE a row to a different customer_id instance, you get an error. However DELETE and SELECT statements simply effect no rows if you try to reach outside of the scope of customer_id=55.

    In some presentation or post I saw the suggestion that the intent of the filtered connection was to simplify the porting of a single tenant databases into a multi-tenant solution.

    Friday, May 13, 2011 4:25 PM
    Moderator
  • Hi, apologies for the delay.

    #1 yes you are correct if for locking does not change in scope with filtering connections. Filtering connection automatically injects into the query the federation distribution key when a federated table is referenced. locking scope is not changed in any way. so yes you can lock the table.

    #2 we may not have all experiences by v1 but we are considering all of these different scopes; rootdb+all members vs federations members, vs single member vs atomic units.

    #3 yes, we will have a way to populate the column with a default value in a filtering connection. In unfiltered connections we don't have the value so we'll require that to be provided.

    Thanks

     

    • Proposed as answer by Cihan Biyikoglu Wednesday, December 21, 2011 11:52 PM
    Saturday, May 21, 2011 3:54 PM
  • Hi Cihan,

    I have just started working with SQL Azure Federations and am seeing different behavior than described above.  Specifically, if I do not include the federated column in an insert, than I get an error.  Here's what I have:

    CREATE FEDERATION CustomerFederation(CustomerId INT RANGE)
    GO

    USE FEDERATION CustomerFederation(CustomerId=1) WITH RESET, FILTERING=ON
    GO

    CREATE TABLE Entities

    (

    CustomerId int NOT NULL,
    EntityId uniqueidentifier NOT NULL
    ) FEDERATED ON (CustomerId = CustomerId)
    GO

    When I include the federated column in the insert, it works fine:

    insert into Entities (CustomerId, EntityId) Values (1, newid())

    However, if I do not specify the federated column (like the following)

    insert into Entities (EntityId) Values (newid())

    Then I get the error:

    Cannot insert the value NULL into column 'CustomerId', table 'system-340370f2-f55c-47e7-bbc7-c25a0c5eb4fd.dbo.Entities'; column does not allow nulls. INSERT fails.

    Am I doing something wrong or does the federated column need to be specified in inserts?

    Thanks,

    Eric


    Monday, February 13, 2012 8:59 PM
  • Actually, after a bit more searching, I was able to find out that you can (in creating your table) specify that you want the federated column to default to the current filtered value.  That is, the create table above should be changed to:

    CREATE TABLE Entities

    (
    CustomerId int NOT NULL DEFAULT federation_filtering_value('CustomerId'),
    EntityId uniqueidentifier NOT NULL
    ) FEDERATED ON (CustomerId = CustomerId)
    GO

    I think that's all I need to do to get the desired behavior.

    Thanks,

    Eric

    Monday, February 13, 2012 9:12 PM
  • Great! that is exactly the way to maintain the federation key automatically. OR else you will need to fill in the value for the federation key yourself.

    thanks

    -cihan

    Monday, February 13, 2012 9:35 PM
  • Hi Cihan,

    At first thanks for great posts about federations. 

    I wanted to hear about any updates on your answer (to Nicks 2nd question). Is it possible to backup and restore only one tenant`s (one AU) data?

    Thanks,

    Mehmet

    Monday, February 20, 2012 10:52 AM
  • Merhaba Mehmet,

    There will be additional improvements coming in future but right now the only way to backup and restore federation is to use SQLAzureMW - SQL Azure Migration Wizard to export your data. You can read more about the tool here; http://msdn.microsoft.com/en-us/magazine/hh848258.aspx

    Thanks

    -cihan

    Monday, February 20, 2012 9:26 PM
  • Hi guys,

    On the topic of backing up federated Sql Azure databases, I have recently wrote a blog post on the subject which can be found here. The main ideas are:

    • at the moment, you can only use the Sql Azure Migration Wizard or the BCP tool to export your data. The Migration Wizard is an integrated tool that will help easily export both the schema and data of a database. The BCP tool can only be used to extract data from one individual table at a time and if you decide to go for it, you will need to manually add the code for detecting the tables in the database and for exporting them one by one.
    • you cannot export a federated database as an atomic unit. You can only export the individual instances (be them the root or federation members) at a time.
    • the export process is pretty straight forward, but the restore is a bit more complicated. If you are loosing entire databases, you can recover all their data and schema from the SqlAzureMW export, but the federation related elements (like federations, federation keys, federation tables, splits) will have to be created manually.
    • the SqlAzureMW also comes with a command line version, which can be used for automated backups. The only catch is that the tool will only make exports to the local disk drive. You will have to write the code to upload the backups to a durable environment, like the Azure Blob Storage.

    Regards,
    Florin


    My technical blog: ducons.com/blog

    Tuesday, March 27, 2012 8:16 AM