Azure Blob Transactional Access

Question

I would like to store files in Azure Blob storage. So far, so good. I would also like to store additional metadata about the file; I use the Azure SQL database for this (so I can easily query the files in the blob repository).

Therefore, when I add a new file to the repository, I would like to make sure that blob, as well as metadata, were successfully written. So something like a transactional context comes to my mind.

Is there a way to create such a transactional 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 block first and then add a record to the database. Because the database serves as an index for your needs, Blob is essentially invisible to your code until the database records are saved.

A more attractive option is to implement your own commit logic. You will process the database insert (with the flag in the record set, for example, 0), save the Blob and, if necessary, set the flag in the database to 1.

You can also save metadata in Azure tables, although searching in Azure Tables can slow down significantly if you have many records. A search in the SQL database will be performed most often in most cases.

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

Answer 2

An existing blob is harmless, but a database entry pointing to a blob that does not exist will be bad. Therefore, I would execute a transaction with the following logic.

Create ... add blob first, then create the database entry. If the blob download failed, just go back. If the blob download succeeds but the database record failed, delete the blob and return. The fact is that you do not want to create a database record until AFTER a successful blog upload. You also want to clear blob if the database record cannot be updated. If the blob cleanup fails, I just have a service that runs periodically to clear the unrecorded drops created more than an hour earlier so as not to try to delete one between loading and creating a database record.

When deleting ... First delete the database entry. If this fails, just go back, the blob record and the database are still there. If the database record is deleted normally, the drop does not hurt anything, remaining there, so you can either try to delete it immediately, or leave it to the cleanup service to take care later.