wood burning stoves 2.0*
The moose likes JDBC and the fly likes Update my database at the same time that a foreign database does Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update my database at the same time that a foreign database does" Watch "Update my database at the same time that a foreign database does" New topic
Author

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

Ilh Oleo
Ranch Hand

Joined: Feb 07, 2007
Posts: 57
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

Joined: Feb 07, 2007
Posts: 57
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

Joined: Oct 06, 2007
Posts: 338
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

Joined: Feb 07, 2007
Posts: 57
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

Joined: Aug 06, 2008
Posts: 25
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

Joined: Feb 07, 2007
Posts: 57
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:
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Update my database at the same time that a foreign database does