| (materialized view) Replication Oracle - MS SQL Server [message #263575] |
Thu, 30 August 2007 07:51  |
panzertape
Messages: 20
Registered: August 2007
|
Junior Member |
|
|
Hey guys!
We are searching for some possibilities to copy data between Oracle and MS SQL Server (in both directions)
We have found some possible solutions so far:
- Import/Export with XML
No solution for us, because of some restrictions 
- DB-Links
We will try that
- transactional replication
As for transactional replication...As far as i have read Oracle doesn't support that. We could use Oracle Streams but we don't want to pay for the license.
So i stumbled across replication (e.g. materialized view replication)
Do you guys think that this is a possible solution for our problem? Is it possible to do that with NON-Oracle-Databases? Any other ideas?
Any help would be appreciated
thanks in advance
panzertape
[Updated on: Thu, 30 August 2007 07:52] Report message to a moderator |
|
|
|
| Re: (materialized view) Replication Oracle - MS SQL Server [message #264010 is a reply to message #263575] |
Fri, 31 August 2007 09:02  |
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
|
Member |
|
|
While I am waiting on an answer to my question I thought I would provide some feedback for yours.
I have some trigger based logic that I have implemented to go from one Sybase server to another. The same logic could be used between Oracle and MS SQL Server. Let me know and I will send you a high level description of the process.
|
|
|
|
|
|
| Re: (materialized view) Replication Oracle - MS SQL Server [message #264078 is a reply to message #263575] |
Fri, 31 August 2007 12:12 |
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
|
Member |
|
|
Panzertape,
The logic only works with specified tables, so if you need to replicate the data for all tables you would have to specify all tables.
Here is the 30,000 foot view:
An insert/update/delete trigger in the source database on table source_t populates table source_replica_t that has the same columns, but also has a sequence_id column, and an transaction_flag column that will contain 'I', 'U', or 'D' (for insert, update, or delete).
The source_replica_t table is periodically unloaded to a file system (via bcp for MS SQL Server or SQL*Loader for Oracle).
This file transferred to the destination box through whatever mechanism you deem appropriate (scp - secure copy, ftp, ...etc.), or maybe you have an NFS mounted disk that both the source and destination box have access to.
Either way, the next step is to load the data into a dest_replica_t table in the destination database which has the same structure as source_replica_t.
You then have a SQL script that executes the transactions in the same order (using the sequence_id column) and populates a dest_t table that has the same structure as source_t.
If you have success up to this point, then you delete all the data in dest_replica_t, and then delete only the data in source_replica_t for the transactions that were just transferred. This way, during the transfer process the source database can continue to load new data into source_replica_t via the trigger.
Hope this helps.
|
|
|
|
| Re: (materialized view) Replication Oracle - MS SQL Server [message #264445 is a reply to message #264078] |
Mon, 03 September 2007 06:32 |
panzertape
Messages: 20
Registered: August 2007
|
Junior Member |
|
|
tomstone, thank you very much for your description! I really appreciate that!
But unfortunately we can't use it.
The main problem is, that normally we don't have admin-rights on the DB-server. So it's not possible to create a file for dataset-exchange or establish a tcp-connection...
Do you have any idea how we could solve that?
thanks again!
panzertape
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
