Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am not sure if my title is correct, but I will try my best to explain the current problem.

Current Scenario:

  1. I have a php web application with MySQL database (let's call it DB1) which is stored in Machine A.
  2. I have another php web application with MySQL database (let's call it DB2) which is stored in Machine B,
  3. ASP.Net Application with SQL Server database (let's call it DB3) which is stored in Machine C which will serve as a ledger.
  4. 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:

  1. MySQL connector ODBC: It is working coz I use it to Linked Server MySQL in SQL Server.

  2. 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.

  3. Transactional replication: Not sure if it will work out

  4. 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

  5. MySQL Data Controller: Been reading, have not tried due coz I need to recompile feetds lib with distributive pack of MySQL

  6. MySQL Federated Table: But this only works for MySQL(Both end)

  7. Incremental load:

Problems:

  1. How can I 'push' the record from DB1 (MySQL) to DB3 (SQL Server) and if successful get a valid response(ID preferably so that I can have a reference on both DB1 and DB3 (via trigger in DB1)

  2. 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")

  3. I have tried Linked server but somehow it only shows DB1 from DB3, any software or addons/plugins so I can view DB3 from DB1 ad push the records from DB1?

  4. 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)

  5. 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.

  6. 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

share|improve this question
    
There have been some similar questions in the recent past, for example this one here. Personally I suspect that if you pursue this, the technology stack is going to get much more complicated than what you really need, and replicating to a remote database server over a VPN on every transaction is not going to perform well. –  James Jul 11 '13 at 6:54
    
Hi Ponce, welcome to Database Administrators. We ask that you limit cross-posting across many Stack Exchange sites as we will migrate if there is a better site-candidate for your question. We do like to offer folks the opportunity to pick one or the other before one gets a lot of answers. So if you would like to keep this one or the one on Stack Overflow let us know, otherwise we'll close the least active one. –  jcolebrand Jul 11 '13 at 8:26
    
Oh I see. I am sorry for the inconvenience, stackexchange is specialized in database administrator that is why I thought I will post here as well, Thank you though. Cheers –  Ponce Jul 11 '13 at 8:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.