How to do Azure Blob storage and Azure SQL Db atomic transaction

Question

We have a Blob storage container in Azure for uploading application specific documents and we have Azure Sql Db where meta data for particular files are saved during the file upload process. This upload process needs to be consistent so that we should not have files in the storage for which there is no record of meta data in Sql Db and vice versa. We are uploading list of files which we get from front-end as multi-part HttpContent. From Web Api controller we call the upload service passing the httpContent, file names and a folder path where the files will be uploaded. The Web Api controller, service method, repository, all are asyn.

var files = await this.uploadService.UploadFiles(httpContent, fileNames, pathName);

Here is the service method:

public async Task<List<FileUploadModel>> UploadFiles(HttpContent httpContent, List<string> fileNames, string folderPath)
        {
            var blobUploadProvider = this.Container.Resolve<UploadProvider>(
                new DependencyOverride<UploadProviderModel>(new UploadProviderModel(fileNames, folderPath)));

            var list = await httpContent.ReadAsMultipartAsync(blobUploadProvider).ContinueWith(
                task =>
                {
                    if (task.IsFaulted || task.IsCanceled)
                    {
                        throw task.Exception;
                    }

                    var provider = task.Result;
                    return provider.Uploads.ToList();
                });

            return list;
        }

The service method uses a customized upload provider which is derived from System.Net.Http.MultipartFileStreamProvider and we resolve this using a dependency resolver. After this, we create the meta deta models for each of those files and then save in the Db using Entity framework. The full process works fine in ideal situation.

The problem is if the upload process is successful but somehow the Db operation fails, then we have files uploaded in Blob storage but there is no corresponding entry in Sql Db, and thus there is data inconsistency.

Following are the different technologies used in the system:

Azure Api App
Azure Blob Storage
Web Api
.Net 4.6.1
Entity framework 6.1.3
Azure MSSql Database (we are not using any VM)

I have tried using TransactionScope for consistency which seems not working for Blob and Db, (works for Db only)

How do we solve this issue?
Is there any built in or supported feature for this?
What are the best practices in this case?

Gaurav Mantri Gaurav Mantri · Accepted Answer · 2016-11-25T13:28:55

Is there any built in or supported feature for this?

As of today no. Essentially Blob Service and SQL Database are two separate services hence it is not possible to implement "atomic transaction" functionality like you're expecting.

How do we solve this issue?

I could think of two ways to solve this issue (I am sure there would be other as well):

Implement your own transaction functionality: Basically check for the database transaction failure and if that happens delete the blob manually.
Use some background process: Here you would continue to save the data in blob storage and then periodically find out orphaned blobs through some background process and delete those blobs.

How to do Azure Blob storage and Azure SQL Db atomic transaction

1 Answers