SSMA 4.0 for Oracle - Delta Data migration

Question

Hi Team,

 

            I have installed SSMA 4.0 in my Box.

 

I have my source database in Oracle and I have performed Initial Data extract to Sql Server. Oracle is still used by business, I want to schedule a job to migrate the delta data from Oracle to Sql Server.

Can this be achieved by SSMA 4.0 for oracle. Please suggest.

Answer 1

Hi kori123,

SQL Server Migration Assistant for Oracle are just designed to migrate data from Oracle to SQL Server, so it cannot achieve the work that automatically copy the delta data from Oracle to SQL Server periodically.

I recommend that you use replication to accomplish this work.

With replication, you can replicate data from not only SQL Server but also Oracle to SQL Server and specify a subset of publisher's data to be replicated. You also can specify the period of synchronization between the publisher and the subscriber.

And I recommend that you use transactional replication in your scenario.

If there is anything unclear, please feel free to ask.

For more information, please see the following article in Microsoft Web site:
"SQL Server Replication": http://msdn.microsoft.com/en-us/library/ms151198.aspx
"Transactional Replication Overview": http://msdn.microsoft.com/en-us/library/ms151176.aspx
"Configuring an Oracle Publisher": http://msdn.microsoft.com/en-us/library/ms151243.aspx

Regards,
Tom Li - MSFT

Answer 2

Hi Team,

           

I have extracted initial data from Oracle to Sql Server, and the business still uses Oracle Database. To load the delta data from oracle to Sql server 2008, I would like to know if I can use SSMA 4.0 to migrate the delta data and schedule it as a SQL job.

 

Please provide your inputs. Thanks and Good Week ahead.

 

Answer 3

Hi,

U can use SSIS as well to transfer data from oracle using CDC(Changed data capture) of attunity for retreiving only the delta data.

 

 

---

Apps Dev

Answer 4

Can I schedule it as a sql job?

Answer 5

Tom,

My objective is migrate data delta data from Source(Oracle) to Sql Server 2008. How will my delta data will be in synch as of oracle database.

Can I use the transaction replication in this case?

Or let me know if I am missing anything before I consider transaction replication in my case.

Thanks

 

Answer 6

Yes u can very wel do it.

Pls find the following link:-

http://msdn.microsoft.com/en-us/library/ms139805(SQL.90).aspx 

for scheduling an SSIS package through a job.

---

Rojit