Data Migration

Question

Post reply

Data Migration

Rechana Rajan

SSCertifiable

Points: 7709
More actions
August 20, 2019 at 11:17 am

#3672697

Hi ,
Our new project demands migration of data from Oracle database to multiple SQL Server databases. We are planning to go with SSIS package to achieve this. Is there any better way to achieve this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 734434

August 20, 2019 at 2:07 pm

#3672779

One time or regularly moving data?

Phil Parkin SSC Guru Points: 246692 More actions · Answer 2

I'm just waiting for Jeff to chime in on this one ...

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 3

Thanks Steve,

We need to do multiple times till the project goes live in SQL Server as we are removing Oracle for this.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

Phil Parkin wrote:

I'm just waiting for Jeff to chime in on this one ...

I've not done an Oracle to SQL Server migration since 2002 and that was a "one time" transfer.

Since this will have to be done multiple times, I can certainly see the advantage of building an SSIS package to do this to preserve the table mapping. I don't know if the OP needs to do a "drop'n'rebuild" each time or if it will consist of an initial population and then try to "keep things in sync", the latter of which being a bit more complex.

Since I don't use SSIS, I'm also ill-equipped to make a recommendation here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 5

If you are doing updates, then SSIS works great. If this is a full migration each time, there is the SQL Server Migration Assistant that works well. A number of friends have used this for their moves from Oracle.

https://docs.microsoft.com/en-us/sql/ssma/oracle/migrating-oracle-databases-to-sql-server-oracletosql?view=sql-server-2017

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 6

Steve Jones - SSC Editor wrote:

If you are doing updates, then SSIS works great. If this is a full migration each time, there is the SQL Server Migration Assistant that works well. A number of friends have used this for their moves from Oracle.
https://docs.microsoft.com/en-us/sql/ssma/oracle/migrating-oracle-databases-to-sql-server-oracletosql?view=sql-server-2017

Thanks Steve, the Source Oracle database is 200GB and we need to move all these data including attachment to SQL Server. The downtime of oracle is almost nil so our plan is to do incremental move to reduce the data to be moved finally and at the end we will stop the oracle for about 10mins and will move that last remaining transactions from Oracle.

I have tried SSMA and its giving errors. Another requirement is that the data has to be moved from one database to multiple database and some data from multiple columns to a single column in destination. How this can be achieved will least downtime?

I was able to establish connection between Oracle and SQL in SSIS but linked server is failing(just to test whether the whole tables can be moved to SQL along with data keeping the existing structure).

Thanks Again

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 7

If you're trying to do this incrementally, then you need some way to determine what data has changed. SSIS is likely the best choice here as you need to do some transformation (move/merge/split), and that has capabilities to do this.

You don't need a linked server. SSIS can just connect to Oracle. https://oakdome.com/programming/SSIS_Devart_OracleDataConversion.php

webrunner SSC-Dedicated Points: 31715 More actions · Answer 8

Hi Rechana,

Your question is interesting to me even though unfortunately I don't have any expertise to share with you. but I subscribed to see what you end up choosing as your approach. Thanks.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Phil Parkin SSC Guru Points: 246692 More actions · Answer 9

webrunner wrote:

Hi Rechana,
Your question is interesting to me even though unfortunately I don't have any expertise to share with you. but I subscribed to see what you end up choosing as your approach. Thanks.
-- webrunner

In future, you may like to simply click on the handy 'Subscribe' button to do this 🙂

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.