Post reply

Data Migration

  • 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?

  • August 20, 2019 at 2:07 pm

    #3672779

    One time or regularly moving data?

  • August 20, 2019 at 3:37 pm

    #3672827

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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 21, 2019 at 5:03 am

    #3673001

    Thanks Steve,

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

  • August 21, 2019 at 12:54 pm

    #3673099

    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)

  • August 21, 2019 at 3:21 pm

    #3673159

    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

  • August 25, 2019 at 6:04 am

    #3674227

    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

     

  • August 26, 2019 at 2:46 pm

    #3674415

    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

  • August 26, 2019 at 3:37 pm

    #3674434

    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

  • August 26, 2019 at 4:49 pm

    #3674456

    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 🙂

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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