Transaction between SQL Azure and Azure Table Storage

2
votes

At my current project we are working on a distributed cloud application that will store the state of an entity and the associated events. When a message is recieved from the queue we update the state of an entity in SQL Azure and save the events associated with that update in table storage.

It looks something like this:

  • Start UnitOfWork
    • Update Entity in SQL Azure
    • Save Events to table storage
    • Commit transaction
  • End UnitOfWork

The problem we are encountering here is that we can't submit the changes in SQL Azure and Table storage within one transaction. So when the saving of the entity in SQL Azure fails, we want to rollback the saving of the events in table storage.

Any help is much appreciated.

1
sql-serverazuretransactionsazure-sql-databaseazure-table-storage
watch through this video it is really interesting and discusses your scenario. It is not a direct solution though! But gives good insight of the "cloud thinking" vs "DTC thinking". – astaykov

1 Answers

2
votes

If I understand your objectives you are trying to cover for the following 2 scenarios:

  • If DB insert fails, rollback Azure Table
  • If Azure Table insert fails, rollback SQL Azure

Assuming you are performing both requests inside the same thread, did you try an approach like this (pseudo-code)? Note that I am using the transactional capability of the database engine; this is not the same as MSDTC because I am only doing this for a single connection - so this is supported.

dbConnection.BeginTransaction;

try { 
     // add record in database - it is in a transaction so it can be rolled back
     insert_into_sqlazure;
     }
catch
{ 
    // automatic rollack - just exist with error
    exit_with_error;
}

try
{
     insert_into_azure_table;
     dbConnection.CommitTransaction;
}
catch
{
    dbConnection.Rollback;
    exit_with_error;
}