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
Begin transaction
Insert A -> Id is not set : Is it possible to ask NHibernate to book an id here ???
Connection open for MySql database
Begin transaction
Insert B
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.
Best Answer
If SQL server is the master,
LEt him insert first and get an id, this id you use to insert into mysql. As the sql server control ist. Mysql has an index on the id but is not a primary key., so you can insert what ever you need.
If both can generate the id, you must switch to uuids, these would always guarantee, that you are unique, and so it doesn't matter who generated the id first. as teh application generates the uuid, no autoincrrement is needed any more.
It will always be a good idea, to switch to other unique identifiers when mnore than one server is invooved.
The price is that the queries get slower, even with an index on the ids