Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Update my database at the same time that a foreign database does

 
Ilh Oleo
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi to all!!
I�m developing a system which involves my database and two foreign databases from where i need to get data to update mine.
I�m working with mysql and the other company works with sql server so i�m wondering how can i update my data at the same time that the other company does.
If they update their database each friday i should have that data too.

So this is a new task for me and i had never worked in this way.
Could someone explain to me what should i do?

thanks
 
Ilh Oleo
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the difference between replication and the dts (data transformation services) in sql server?
Could that be useful for what i�m trying to do???
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
replication is a database synchronization process (in this case since it is SQL Server, it would be between two SQL Server databases. This won't work between MySQL and SQL Server).

Data Transformation Services are a toolkit for ETL (extract, transformation, and load). This can work between SQL Server and MySQL. However, it isn't as simple as something that will automatically occur... you will have to build your ETL processes and come up with your Change Data Capture (CDC) strategy. This should take into account the latency of your data (this isn't an automated process... it would be a scheduled process and something that will not be real-time).

You do sound like you are wanting a replication server type process... you're limited by your target being MySQL. An alternative is to have an intermediate replication server (SQL Server database) to utilize as your source... the replication could occur on your schedule and align with your ETL... but that really is dependent on what you determine is the best approach.
 
Ilh Oleo
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for the answer. :thumb:
As i read in your post i should use DTS to program the updates.
My source will be the sql server so do i need an intermediate to work around with the mysql server??
The updates from the source will occur each friday so can i resolve that or it has to be through triggers and things like that?
Could you give me more info about it?
Thank you
 
Shawn Montague
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
do you have control over the SQL server or do you get your data through an interface of some sort? if you have control, then you can use DTS (SQL 2000) or SSIS (SQL 2005+) to push the data to your MySQL database.

if you don't have control over the SQL server, you will probably have to write something local that harvests the data you need every Friday...syncing this will be the issue.
 
Ilh Oleo
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I could get the permission to handle physically the sql server but i must know what should i do in case of blame somebody,jejeje :thumb: :thumb:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic