I am not sure if my title is correct, but I will try my best to explain the current problem.
- I have a php web application with MySQL database (let's call it
DB1) which is stored in Machine A.
- I have another php web application with MySQL database (let's call it
DB2) which is stored in Machine B,
- ASP.Net Application with SQL Server database (let's call it
DB3) which is stored in Machine C which will serve as a ledger.
- They are connected via VPN.
I have completed this:
Let's say the transaction will go like this, a new transaction occurs, it will validate records on both code behind A and push to through Web Service on Machine B for validation and posting(Transaction ID, and corresponding Transaction Details)
I will then get the response from B and if successful I will proceed to the problem below.
I have been reading and trying the following:
MySQL connector ODBC: It is working coz I use it to Linked Server MySQL in SQL Server.
Linked Server on SQL Server: I am not sure though if this is the solution for my current problem, but at the moment I can view and query DB1 from DB3, but the trigger should be in DB1 to push the record to DB3 everytime there is a new record inserted on DB1.
Transactional replication: Not sure if it will work out
SSIS: I have tried to add both DB1 and DB3 by creating Integration Service and used mysql connector odbc and added dsn(both user and system) but there is an error like what I have mentioned below
MySQL Data Controller: Been reading, have not tried due coz I need to recompile feetds lib with distributive pack of MySQL
MySQL Federated Table: But this only works for MySQL(Both end)
How can I 'push' the record from
DB3(SQL Server) and if successful get a valid response(ID preferably so that I can have a reference on both
DB3(via trigger in
I have tried SSIS, successfully added ADO.Net Source for 127.0.0.1 for
DB3(the SSIS resides on Machine B), but when I am adding a connection to the DB1 that resides on Machine B I get an error("Error[IM002][Microsoft][ODBC Manager] Data Source Not found and no default driver found")
I have tried Linked server but somehow it only shows
DB3, any software or addons/plugins so I can view
DB1ad push the records from
I need to have it as a transactional replication (somehow I read it is possible but at the moment I can only search for SQL Server to SQL Server)
I cannot put the inserting of new records/updating records on Code Behind(which is possible and easier) for security reasons (just in case there is/are changes on either DB). Same goes for the usage on Web Service for this part.
Performance likewise, will it be wise to do this if the number of transaction is large?(probably 360,000 inserted records in a daily basis)
Any detail explanation is greatly appreciated. Thanks
Taken from my question in: http://stackoverflow.com/questions/17583924/push-data-from-mysql-to-mssql