Updates from February, 2012 Toggle Comment Threads | Keyboard Shortcuts

  • Richard 9:35 am on February 17, 2012 Permalink |  

    Simple node.js socket listener 

    This simple snippet just writes back everything it receives, but it’s a useful test:

    var net = require('net');
     socket.on('data', function(data) {
  • Richard 10:06 am on February 10, 2012 Permalink |  

    Dynamic Table Storage 

    I maintain a project called AzureSugar, a library of extensions to the standard Windows Azure C# SDK which contains a number of handy methods for making the Azure Storage API easier to work with. I have just included support for using dynamic objects for table storage. As Table Storage does not enforce a fixed schema, it has always seemed a shame to me that the C# SDK forces you to use static types. Perhaps you don’t know the schema at compile time, perhaps you want to store a dictionary of items instead of a class, maybe you don’t want so much ceremony to just write a record to a table? The DynamicTableContext solves these problems.

    To use it, first, create a context object:

    var context = new DynamicTableContext("TableName", credentials); 

    Then inserting a record is easy using a dynamic object for example:

    context.Insert(new { PartitionKey="1", RowKey="1", Value1="Hello", Value2="World" }); 

    You can do the same with a dictionary:

    var dictionary = new Dictionary<string, object>(); 
    dictionary["PartitionKey"] = "2"; 
    dictionary["RowKey"] = "2"; 
    dictionary["Value3"] = "FooBar"; 

    Retrieving an entity is striaght forward, just pass in the values for partition key and row key:

    dynamic entity = content.Get("1", "1"); 

    You can also pass in a query:

    foreach (dynamic item in context.Query("Value1 eq 'Hello'")) 

    This is the first version, there are plenty of extra features I would like to add over time, but I hope there is enough here to be useful.


  • Richard 3:41 pm on February 6, 2012 Permalink |  

    SQL Azure Federations 

    SQL Azure Federation is a way of splitting a table (or tables) across a number of SQL Azure databases. The technique is also know as sharding. For more information, see this MSDN article.

    There are a few use cases for federations:

    1. Your table is too big to fit in a single database (i.e. it’s greater than 150GB)
    2. Query performance is a problem, it’s a way of breaking up the table into smaller pieces, which run on different physical hardware.
    3. In a multi-tenancy scenario, it’s a way of physically partitioning tenants into separate databases.

    Creating a federation

    Federated databases have a ‘Federation Root’ database. This is the principal database you connect to. Once connected to this, you can create a Federation with this command:

    CREATE FEDERATION federation_name (distribution_name <data_type> RANGE)

    You need to specify a ‘range’, which is a value you will use to split your data into the separate federations. Typically you would use a bigint. Federations can also be created in the management portal.

    When you create a table, you then specify which column to use for the value of the range (i.e. which column to federate on).

    USE FEDERATION federation_name(distribution_name = value) WITH RESET, FILTERING={ON|OFF}
        [ schema_name . ] table_ame
            ( { <column_definition> | <computed_column_definition> }
            [ < table_constraint> ] [ ,...n ] )
    FEDERATED ON (distribution_name = column_name)

    Unfortunately you can’t ‘ALTER’ a table for federation, you can only issue the command on CREATE.

    Using federation

    When you connect to the database you need to include an extra command to indicate that we want to work with federations. You also need to supply a value to indicate which of the federations to work with. One of the advantages of Federations, is that we don’t need to specify the actual federation, just the value for range. SQL Azure will determine which Federation this value falls in, and routes you accordingly.

    USE FEDERATION federation_name (distribution_name = value)

    The disadvantage is that this command needs to be included in your  application. The command is associated with a connection, so if there is some clever connection pooling going on (i.e. entity framework) you may have some extra work to do. You cannot call this command from within a stored procedure.

    With filtering on, only records which match the range value will be retured. With filtering off, all records from the federation can be returned.

    Splitting a federation

    Splitting federations is straight forward, you can either issue a command:

    ALTER FEDERATION federation_name SPLIT AT (boundery_value)

    …or use the management portal.

    In either case, it’s an atomic operation, requiring no down-time.

    What’s missing?

    • Identity columns are not allowed in federated tables. You can either use GUIDs (not a very nice thing to split on) or create some kind of identity issuing table in the federation root.
    • Timestamp columns are not allowed.
    • You can’t join federations back together (unless you write the code yourself!).
    • Pricing.

    Other interesting features

    • Each federation is just a separate database. You can connect to them and alter the schema yourself if you want (I’m not sure I advise this!).
    • There is a tool to help you migrate your data.


    This is the first release of SQL Azure Federations, and whilst this isn’t a panacea to solve all big data problems (I’m not sure anyone has a good solution for sharding) it does present some interesting features. Separating the the layout of the federations from the concern of the application is nice, and the splitting operation is good too. It’s a shame that applications need to be modified, and include the extra ‘USE’ command, but perhaps that’s something that will change?

    I see Federations as a compromise between SQL Azure and Table Store. You give away a few features (e.g. identity and timestamps) but you gain scalability.

    Another tool in the box.

    • sivaL 4:30 pm on September 25, 2012 Permalink | Log in to Reply

      Richard, Its nice article.
      I am getting some issue while connecting to the federated root database. Connection is not successful whenver i connect to entity framework. But if i connected to federated database connection work fine.
      Provider = “System.Data.SqlClient”,
      ProviderConnectionString = “data source=testdbserver.database.windows.net;initial catalog=test;TrustServerCertificate=True;Encrypt=True;user id=crmsa;password=Foraar12″,
      Metadata = @”res://*/test.csdl|res://*/test.ssdl|res://*/test.msl”

      Please let me know if you have any thoughts.

Compose new post
Next post/Next comment
Previous post/Previous comment
Show/Hide comments
Go to top
Go to login
Show/Hide help
shift + esc