1

Which technology should be used if I want to move my data from SQL Server to MySQL server when data is entered to SQL Server row by row and getting feedback from MySQL server?

Should it be 1. Linked-Server or 2. SSIS?

Also,

  1. Each time I insert a row in SQL Server it should also insert into MySQL and when it is done successfully I should update a flag column in SQL Server to 1 for acknowledgement of data insertion.

  2. What if I update MySQL table. Would it be reflected in SQL Server also?

Improve this question
6
  • 1. yes every time i insert row in MSSQL it should be copied into mysql and when it is successfully entered then i would like to update my local mssql table flag to 1 so that it confirms insertion.No updation on mysql. 2.what if i should get every update to mssql when mysql row has updated?
    – Harsh Vyas
    Commented Jul 29, 2015 at 7:28
  • ok sure i will update.
    – Harsh Vyas
    Commented Jul 29, 2015 at 7:31
  • You're missing your own point. Linked Servers or SSIS packages do not, by themselves, trigger the inserts and updates you want.
    – Ozzie
    Commented Jul 29, 2015 at 15:44
  • Besides that, SSIS would always be a scheduled batch operation. Linked servers are resource consuming for the database server. Don't even get me started about triggers and CPU usage... Is your SQL Server edition enterprise? If so, I'd suggest you look into CDC and Change Tracking.
    – Ozzie
    Commented Jul 29, 2015 at 15:57
  • @Ozzie first of all i know that both of them needs trigger to run it but i think i will run that via stored procedure. if i use linkedserver then use openquery and if ssis then i will run it also with sp. so i just wanted to know that which option is better for this given scenario.
    – Harsh Vyas
    Commented Jul 30, 2015 at 6:36

1 Answer 1

Reset to default
0

Given the above comments, I'd recommend going with an SSIS package. If you go with the linked server option you are injecting database dependency between the replication and the MSSQL server objects. Linked servers are also a security liability, anyone with sufficient permissions on the MSSQL database can access the target database on the linked server. Choosing SSIS gives complete control over the datasources, execution, scheduling and effectively decouples the data transfer from the actual databases.

Improve this answer
0

Your Answer

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

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