I should migrate part of the service provided from one back-end to another.
Company A has an application that connects to a .Net server in windows with a Sql database. Company B has an application that does something very similar, but connects to a node js server in debian with a MySql database.
Initially some of the services of the application will be transferred from A to B. The structure of the two databases is similar, but not quite the same. The basis of the solution I am preparing is:
- Copying current data from A to B (with appropriate modifications, since there are differences in tables).
- Creating triggers for create, update and delete operations on database A, so that all changes on database A will be reflected in database B
Step 1 I have figured out mostly. There will be some work to be done so that changes in tables are adapted. I will be using navicat to export tables from db A into excel spreadsheets. These spreadsheets will be modified to fit the structure of db B and then imported into db B, keeping in mind table dependencies.
For step 2, as I mentioned, I was considering using triggers. As far as I have been reading about triggers, they would require both databases to be sql or both of them to be mysql, which is not the case. I also tried this article, but could not make it work (server A is windows and server B is linux, this article does not seem to work for this case).
Any idea / correction / lead to performing the task? I have been thinking and googling but without making anything work.
PS: I am not asking help in writing the actual queries or statements that will replicate / update data from A to B, only a general method that will work considering that the databases are of different languages (sql to mysql), in different servers of different OS (windows to debian), and are used by code of different languages (.net + C# to node js).
PPS: Altering the code in server A to execute queries in both server A and server B is not an option.
PPPS: In the title I mention real - time replication. That would be ideal, but in lack of an appropriate solution, replication in intervals of time is also an option, as long as it is reasonably correct. Triggers managed real - time transfer, but in the worst case I could have a cron job in server B fetch the data from A, as long as I can tell which data to handle.
