Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Unanswered: Sychronizing data between two DBs

    Hello,

    Thanks in advance for any suggestions.

    I have an Oracle 8i database as well as a SQL server 2000 database. Each database is on a seperate server.

    These databases contain multiple tables that are identical. The Oracle 8i database is the source database where changes occur. I would like to keep the data on the SQL Server database as real time as possible.

    What we had been doing in the past was doing batch updates of all the tables from Oracle and overwriting the existing ones on SQL Server, every 20 min.

    This worked fine in the past, but it now seems to be putting a major strain on the Oracle server.

    Does anyone have suggestions on how I can send only the updated data to SQL Server rather than the entire table. (I need to include any changes that may have taken place).

    I also am very interested if this is more easily achieved using a SQL Server transfer to SQL Server. Rather than Oracle to SQL Server.

    Thanks Again.

    Jason
    Jason

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    One way to do this is to create a linked server (on SQL Server instance) to the Oracle database server. And write your code to pull the delta from Oracle to SQL Server. There are several options using SQL Server linked servers where you have a choice to have the processing done on the linked server or the initiating server.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Another possiblity using a linked server would be to simply query the Oracle table itself, bypassing the need to do any extract process at all.

    If you have a lot of money (mid six-figures), you could use Oracle's replication product.

    With SQL-2000 to SQL-2000 replication, this is trivial (and included in the cost of the SQL Server license).

    -PatP

  4. #4
    Join Date
    Jan 2004
    Posts
    53
    Thanks so much

    Any ideas on where to lookup more detail

    Thanks
    Jason

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •