CODEWITHSUNDEEP
c#sqldatabaseazureblob
kavalerov
kavalerov

Reputation: 390

How to sync Azure SQL with Azure BLOB Storage?

I have files which are saved in Azure Blob storage, and i have a database which keeps URIs of those files. Is there any standart way to sync them? I mean that there should be no way to delete file without deleting record in the database and vise versa.

Upvotes: 2

Views: 1150

Answers (5)

Jude Fisher
Jude Fisher

Reputation: 11294

There is no out of the box way of doing this, so you need to write your own. I found this file monitor example useful: http://ben.onfabrik.com/posts/monitoring-files-in-azure-blob-storage

2022 Update As pointed out in comments, in the decade since this answer was posted, the link has gone dead. The wayback machine has it here: https://web.archive.org/web/20130629015900/http://ben.onfabrik.com/posts/monitoring-files-in-azure-blob-storage

Given the many changes to Azure since 2012, it is highly doubtful that this is still the best way to solve this problem.

Upvotes: 0

user1852503
user1852503

Reputation: 5607

My solution is having a single entry point like scripni suggested and adding to it a transaction like approach.

I use the database as a write-ahead log for the whole transaction.

1) mark the database records as "shouldBeDeleted" = true.

2) try to delete the blobs.

3) if there is an error and the blobs are not deleted, you still have the records and you know that the blobs that they point to should be deleted. and you can try to delete them again later, more importantly you can ignore the records in read queries just like you would if you are using the soft delete approach.

4) if the blobs are deleted, you can now delete the records.

5) if this fails, the records are still marked as "shouldBeDeleted", letting you ignore them and clean them up later.

For me the main benefit is that I don't have to create a worker just for this purpose, and also that the change is instant, as opposed to dependent on a sync cycle leaving a window when the records are not synced.

You can implement the same idea for an insert/update in pretty much the same way.

*A bit late but I believe the problem is still current and that other have run into it.

Upvotes: 0

hocho
hocho

Reputation: 1749

One way to implement transactions on the Azure platform is to use queues. Place a message on a queue and only delete the message after it has been successfully processed.

That way if any operation fails, you can attempt to rectify the situation, the next time the message is visible.

Upvotes: 0

Tom
Tom

Reputation: 1611

I agree with scripni, you can also vote up the following ask:

http://www.mygreatwindowsazureidea.com/forums/34192-windows-azure-feature-voting/suggestions/469736-event-handler-support-for-blob

If blob storage had events, you could know when one is inserted and then update the database. You could also have a worker role or just cron job on-premise that queries blob storage every so often and updates SQL accordingly.

Upvotes: 1

scripni
scripni

Reputation: 2164

There is no tool which can assure referential integrity between database records and the Azure Blob Storage.

As an alternative, you can either have a worker role which would keep the files in synch with the database (but you will deffinetly have a delay here).

Another debatably clean approach would be to only have one entry point for adding/removing blob files, and handle the synchronization with the database in that place.

Upvotes: 1

Related Questions