Transactional Access of Azure Blob Storage

Question

I would like to store files in Azure Blob Storage. So far so good. I also would like to store addtional meta-data about the file; for that I use a Azure SQL Database (so I can easily query for files on the blob storage).

So when I add a new file to the store I would like to make sure, that the blob as well as the meta-data was successfully written. So using something like a transaction-context comes to my mind.

Is there any way, to create such a transaction-context using blob storage as well as sql storage?

Answer 1

There is nothing built-in that will do this as far as I know; you will need to manage this yourself. The simplest scenario is to save your blob first, then add your database record. Since the database serves as an index for your needs, the Blob is essentially invisible to your code until the database records gets saved.

A more involved option is to implement your own commit logic. You would handle a database insert (with a flag on the record set to 0 for example), save the Blob, and if successful set the flag in the database to 1.

You could also save the metadata in Azure Tables, although searching in Azure Tables can slow down significantly if you have lots of records. Searching in SQL Database will be faster most of the time.

Which approach you choose depends on your objectives, but I think the first option is the simplest.

Answer 2

The blob existing is harmless, but a database record that points to a blob that doesn't exist would be bad. Therefore, I'd implement the transaction with the following logic.

On create... add the blob first, then create the database record. If blob upload fails, just return. If blob upload succeeds, but database record fails, delete the blob and return. The point is you wouldn't want to create a database record until AFTER a successful blob upload. You also want to clean up the blob if the database record couldn't be updated. If blob cleanup fails, I'd just have a service that runs periodically to clean up unreferenced blobs created more than an hour earlier just so it doesn't try to delete one between upload and creation of the database record.

On removal... Delete the database record first. If that fails, just return, the blob and database record are both still there. If the database record is deleted ok, the blob isn't hurting anything by remaining there, so you can either try to delete it immediately or leave it for a cleanup service to take care of later.