0

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 properties are not the same. There's only one column that allows me to join them. Basically, I get the object A in the SQL Server database and retrieve 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 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:

  1. Connection open for SQL Server database.
  2. Begin transaction.
  3. Insert A; Id is not set : Is it possible to ask NHibernate to record the Id value here?
  4. Connection open for MySQL database.
  5. Begin transaction.
  6. Insert B.
  7. Commit transaction for B.
  8. Commit transaction for A.
  9. Close transaction for B.
  10. Close transaction for A.
  11. Close connection for B.
  12. Close connection for A.
Improve this question
1
  • Could you make your question consistent in terms of A and B to represent only ONE HOST. Using A for your SQL Server and B for your MySQL server? If SQL Server is the MASTER but does not yet exist, then you have NO WAY to do any testing. MySQL does not usually cause ROW(s) of data to be stored into a SQL Server which appears to be what you may be trying to accomplish.
    – Wilson Hauck
    Apr 15, 2021 at 20:00

1 Answer 1

Reset to default
0

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

Improve this answer
2
  • My problem comes from the fact that object A must not exist exist if object B does not exist and vice versa. That's why I have to ensure the success of insertion on both side. Use uuid allows me to keep in hand the identifier assignment. I will take a look if there's potential incompatibility between MySql and Sql Server concerning uuid type.
    – Karine
    Apr 14, 2021 at 13:21
  • no there isn't you generated it in wpf and it is only bytes for both systems, the godd think as i explained is you send the send request after the first is done
    – nbk
    Apr 14, 2021 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.