Lately, I've been working on a project which basically is a huge rewrite in .NET Core F# + Event Sourcing + PostgreSQL of an old sub-ledger legacy app written in .NET 4.6 C# + SQL Server.
Since the whole rework thing cannot happen overnight and the legacy process needs to run till every single piece is tested replaced, we opted for distributed transactions via the
TransactionScope class, it usually works, but the tradeoff is that you need clean up orphaned transactions in case that there is a crash (and basically that can happen whenever you're updating a service), chances are not high, but that can still happened and already happened.
Long story short, we need to keep a certain consistency between what is written in the legacy system (ie. SQL Server) and what is written is the new system (ie. PostgreSQL) until everything is fine, it's a critical system so can't really mess up with it.
So I'm wondering is there really an alternative when it comes to write some data into both databases (albeit with a different format)?
So that we can have the guarantee that the transaction has worked out (or not) for both DBs (I put the emphasis on both, cause it should be either true or false). What we absolutely want to avoid is that there is a piece of data written into one and not other (system).
I've heard about the saga pattern but not too sure how this can be applied in that context knowing that we can't change much the legacy system.
usingkeyword is not enough to get you covered in case of a failed distributed transaction. Something that can happen at the wrong time can still happen and already happened, usually (but on rare occasions) when you're not shutting down a service not that gracefully.