1

I am working on an ASP.Net MVC 3 application using C# 4.0 and SQL Server 2008 R2. I have a scenario where:

  1. Several database rows are inserted into a SQL Server Database.
  2. An xml file built from the content of one of these rows is written to the file system.

Currently the database operations are wrapped in a TransactionScope and the file system write is the last operation before the call to TransactionScope.Complete().

I am trying to combine the file system write with the database inserts as a single atomic operation.

After reading a similar post I have tried using Transactional NTFS (TxF) and it seems to work OK. However within the team I work with there is some reluctance to use this due to the lack of evidence and experience with TxF.

What are other decent approaches/patterns that can be used to make a combined DB and file system change atomic?

Community
  • 1
  • 1
TonE
  • 2,975
  • 5
  • 30
  • 50

2 Answers2

2

You are using SQL Server 2008. You can use the FileStream storage. This is wrapped in the transaction along with the database changes.

Maarten
  • 22,527
  • 3
  • 47
  • 68
  • Thanks for the suggestion. Once the file has been written it needs to be processed and moved to a different folder by a third party application. Will this be possible when using FileStream storage? – TonE Apr 18 '13 at 14:40
  • @TonE Nope, its the storage of the file that is handled by SQL Server, and where it is stored is also handled by SQL Server. You cannot move it elsewhere. AFAIK. – Maarten Apr 18 '13 at 14:52
  • A quick scan here http://technet.microsoft.com/en-us/library/cc645940(v=sql.105).aspx suggests it may be possible to use the Win32 file API to read the file contents and then write them to another location, but this is no improvement on my current situation. – TonE Apr 18 '13 at 15:04
1

Unable to use Transactional NTFS or SQL Filestream storage I ended up developing a kind of two stage commit.

1) The database is considered the master over the file system.
2) A staging folder is used as the file write destination in the TransactionScope.
3) A separate scheduled process checks the staging folder for any files.
4) If it finds any it checks the database is consitant for this file.
5) If the database contains a record for this file it is moved to the final location and the transaction is complete.
6) If the database does not contain a record of the file it is moved out of the staging folder and quarantined where it can be inspected and/or deleted.

Obviously careful consideration is needed around the error reporting in the scheduled process.

TonE
  • 2,975
  • 5
  • 30
  • 50