Hi,

Our legacy works with LINQ to SQL and our new development is going to use DO. This means that when we want to update both system simultaniously we might get currupted data when 1 of the 2 transactions fails.

Now there are 3 possible solutions to this problem:

  1. Implement a two phase commit when possible in LINQ to SQL and DO. Is probably the best way I guess since that is supported by many databases.
  2. First commit the legacy changes and when that succeeded commit DO's. When the latter fails rollback the changes in the legacy. For this we would have to implement some kind of undo mechanisme for LINQ to SQL changes.
  3. Combine the two sessions in the database using the transaction token. Both DB's must be MSSQL for this to work. This limits our ability to switch DB's in the future.

So my question is: What is the best way to synchronize 2 databases with different technologies involved?

Regards Paul

asked Oct 12 '12 at 08:25

Paul%20Sinnema's gravatar image

Paul Sinnema
261878896

edited Oct 12 '12 at 08:26


2 Answers:

Hello Paul,

it's hard to give universal advice without detailed knowledge about all involved systems. 2-phase commits are difficult to deal with. I'd better avoid them if possible. If your legacy DB and your DataObjects.Net DB are on the same server you could simply use one transaction for applying changes.

If it's tolerable to have databases to be out of sync for some short delay you might consider implementing async approach when changes in one database are logged and then applied to other.

answered Oct 15 '12 at 06:43

Denis%20Krjuchkov's gravatar image

Denis Krjuchkov
179325

In our case we use TransactionScope and DTC for combine LINQ to SQL and DO with same mssql db. DTC supports scenarios with different databases, different sql servers, different servers and wcf (you can even combine with ntfs transactions). DTC have limitations (nested transactions unsupported, in failure case you can only rollback all transactions and etc). Reprocessing extension take care all these limitations.

P.S. if LINQ to SQL and DO use same tables, then you must save all changes and reload entities (if logic can change them) when switching ORM

answered Oct 19 '12 at 01:41

proff's gravatar image

proff
75336

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×1
×1
×1

Asked: Oct 12 '12 at 08:25

Seen: 12,673 times

Last updated: Oct 19 '12 at 01:41

Related questions

powered by OSQA