I have to guarantee consistency of data stored in two databases installed on different servers. The first one is a MySql database and the second is a Sql Server database.
Data's properties are not the same. There's only one field that allows me to join them.
Basically, I get the object A in the SqlServer database and retrieve the object B in the MySql database using a where clause
A.Id = B.A_Id.
Primary keys are auto generated and INT(11) type. The Sql Server database is not yet implemented.
The connection at the MySql database is managed by a WPF application and the connection at the Sql Server database is managed by a WebAPI. I'm using the ORM NHibernate to perform databases handling.
I have to ensure that no errors occurs during an insert. I have to insert the object A in the Sql Server database to be able to set the A_Id property of my object B before inserting it. If the insertion in the MySql database fails, I have to rollback the insertion in the Sql Server database.
I would like to find a solution to avoid an insert/delete in the Sql Server database. What is the best approach to achieve this ?
I try to implement something like this but I'm facing an issue with the primary key of object A:
Connection open for SqlServer database
Insert A -> Id is not set : Is it possible to ask NHibernate to book an id here ???
Connection open for MySql database
Commit transaction for B
Commit transaction for A
Close transaction for B
Close transaction for A
Close connection for B
Close connection for A\
Thanks in advance.