March 12, 2020 at 1:01 am
I am looking for large data transfer from sql server to oracle on a daily basis and incrementally And the Sql server will be configured as always on. I have looked into few options but want to know more from the experts about pros and cons.
Sql server Replication to oracle is an option but its getting deprecated and not supported in the future releases.
ETL is option but the number of tables count is approx 510 tables and tables are very large. Not sure it would be an option?
How about CDC?
Any others? Thanks in Advance!
March 13, 2020 at 1:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 13, 2020 at 2:55 pm
I have not tried it but Oracle's Goldengate will probably work. I suspect this would be the expensive option.
March 20, 2020 at 8:29 pm
Thanks! SQL Server replication to Oracle would be a good option but going forward Microsoft claims that this will be no longer supported.Any other options?
March 21, 2020 at 9:31 am
Microsoft is depreciating replication to Oracle but Oracle is not.
Goldengate allows for replication to a number of database types. I have no idea about the cost.
March 21, 2020 at 3:54 pm
Do a BCP out to a file and have Oracle import it (I can't remember the name of the built in tool on the Oracle side). The last time I had to do such a thing was way back around 2002 but it was nasty fast on both ends. The really cool part is both methods are supported and both methods are FREE.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2020 at 5:11 pm
Do a BCP out to a file and have Oracle import it (I can't remember the name of the built in tool on the Oracle side). The last time I had to do such a thing was way back around 2002 but it was nasty fast on both ends. The really cool part is both methods are supported and both methods are FREE.
I don't recall whether or not Oracle's import can be configured for incremental loads - but I don't think it does. That means someone has to write code on both sides...export only the data that has been updated and merge (UPSERT) the data on import.
I believe the only option here is an ETL process...no simple/easy implementation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 21, 2020 at 9:25 pm
Jeff Moden wrote:Do a BCP out to a file and have Oracle import it (I can't remember the name of the built in tool on the Oracle side). The last time I had to do such a thing was way back around 2002 but it was nasty fast on both ends. The really cool part is both methods are supported and both methods are FREE.
I don't recall whether or not Oracle's import can be configured for incremental loads - but I don't think it does. That means someone has to write code on both sides...export only the data that has been updated and merge (UPSERT) the data on import.
I believe the only option here is an ETL process...no simple/easy implementation.
Just like in T-SQL, it shouldn't matter... load the data into a staging table and do your MERGE/UPSERT from there. It'll be much faster than trying to do it across any kind of a link.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2020 at 12:35 am
Thanks! Has anyone has any thoughts on Change Data Capture. The CDC function is, probably similar to the transactional replication. It is not the same, but it has this feature each transaction is represented in the CDC tables.
March 23, 2020 at 12:43 am
I'm not sure how the use of CDC will help you replicate to Oracle.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2020 at 1:19 am
Thanks! Has anyone has any thoughts on Change Data Capture. The CDC function is, probably similar to the transactional replication. It is not the same, but it has this feature each transaction is represented in the CDC tables.
CDC that is what I use for one of my systems (90 tables that need to be replicated to Oracle).
It will work but with its own issues if the tables under CDC are subject to DDL changes - and all tables will require a Primary Key to be set - without one CDC won't work
rough setup is
(Note - due to differences related to empty strings Load using Attunity driver must sometimes be set to Array load instead of Fast Load - although slower it allows the load to retain trailing spaces)
Depending on exact setup and volume of data using CT (Change Tracking) may also be an option - and with less impact on DML operations - but harder to determine the values that changes as it will only tell you the PK that changed and what columns.
an alternative to the above is having a column on all tables that gets set for any modification to the record (through triggers) so that you always know which records have changed since last ETL.
March 23, 2020 at 3:50 am
Interesting... thanks, Frederico.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2020 at 4:21 pm
Thanks Frederico. So you agree that CDC is similar to SQL Server transactional replication? Since setting up SQL Server replication also requires all tables to have primary key. You mentioned that "It will work but with its own issues if the tables under CDC are subject to DDL changes"? Can you elaborate more and provide the issues if you noticed any with CDC.
The steps you outlined is how you configured CDC or other option for SQL to Oracle? I have been researching and found that stay away from CDC to determine changed data because of issues where transaction log was not flagged.
March 23, 2020 at 4:40 pm
Thanks Frederico. So you agree that CDC is similar to SQL Server transactional replication? Since setting up SQL Server replication also requires all tables to have primary key. You mentioned that "It will work but with its own issues if the tables under CDC are subject to DDL changes"? Can you elaborate more and provide the issues if you noticed any with CDC.
The steps you outlined is how you configured CDC or other option for SQL to Oracle? I have been researching and found that stay away from CDC to determine changed data because of issues where transaction log was not flagged.
No CDC is not similar in any way, shape or form similar to SQL Server Transactional Replication.
They both have similar requirements and both use the same process to identify the changes (T-Log reader) but that is as far as it goes.
As for issues with CDC - read the documentation regarding CDC - mainly related to the fact that each table can have 2 CDC instances active at any time due to DDL changes - and how that impacts any ETL job that depends on CDC tables to determine changes.
Regarding T-log not flagged - do supply a link to that - it may have been a "mis-understanding" of whoever posted it as all changes go to the T-log - there is no way for that not to happen if a table is under CDC as it automatically makes it to be fully logged even if the database is in simple or bulk modes.
do read these procs Change Data Capture SPs
And a minor note - Although I did say it requires a PK (or Unique Index) that is not really true - it can work without one - but then it is a lot harder to get the changes out, and net changes (e.g. final row values after multiple changes) is not possible - this limits its usefulness quite a lot for high transaction tables where a single record can be updated multiple times between ETL processes
June 5, 2020 at 4:52 pm
Why would you use CDC instead of ETL option? Is there any benefits over ETL?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply