I want to setup a sync service using Microsoft Sync Framework. The main objective is to sync some tables between an Oracle Database and a SQL Server Database.
How can I do it? There are any OracleSyncProvider?
Add a comment
|
3 Answers
if you go the Sync Framework way, there is a sample Oracle sync provider at: http://code.msdn.microsoft.com/Database-Sync-Oracle-and-037fb083
Comments
There are several forms of third party software which will automate this process for you, however if you would like to do it manually I would suggest the Oracle CREATE DATABASE LINK.
This approach allows you to access information (tables,views, etc) from another database using the Heterogeneous Services.
Once you have established a connection between the two databases you can create a trigger for the Oracle tables which in-turn fire on updates/inserts/deletes so that the same action gets invoked on the Microsoft database.
See the following link for additional details on Oracle's DATABASE LINK : http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5005.htm
The same setup can be applied to the SQL server so that whenever an update/insert/delete is performed the correlating table in Oracle gets updated, "synced", as well. For the Microsoft approach to this see the following link, http://msdn.microsoft.com/en-us/library/ms188279.aspx .
Comments
To solve this problem I used a Linked Server to the Oracle DB Server and after that I created a SQL SERVER JOB that runs the Merge command.