1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

create a distributed database on two servers

Discussion in 'Other Databases' started by Benjamin, Mar 21, 2014.

  1. Benjamin

    Benjamin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi everyone,

    I have two Windows XP machines connected as a LAN

    I have installed the Oracle database software on the first one (11g R2) and the SQL SERVER 2012 software on the second one

    I want to create a database (really simple just for test) distributed between both machines (half of data one machine and the other half on the other) so as I could run SQL queries on any of the machines and get data just like if it was a single database.

    Thanks in advance for any answer or help
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why on earth would you do that? It isn't an easy task as you can 'talk' to SQL Server from Oracle but it's harder to do the other direction (originate Oracle queries from SQL Server).


    If I read this correctly you want half of the data in the Oracle database, and half of it in SQL Server. That's not a distributed database it's a management nightmare and nothing any enterprise would want to attempt.


    You really need to explain WHY you want to try this.
     
  3. Benjamin

    Benjamin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I want to try this because I know that you can have a distributed database over several servers (different dbms)
    I know that I didn't explain it very well, but what I actually want to do is to have a kind of link between data in different machines and different dbms so as I can query data from my machine like a single server

    I hope I have explained well the purpose
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    If I understand you correctly.

    Oracle DB 1 SQL Svr DB 2
    ------------ --------------
    Car_Table Car_Table
    Chevy Audi
    Dodge BMW
    Ford Mercedes

    ...you want to (from any location):

    select * from Car_Table;

    ...and see

    Audi
    BMW
    Chevy
    Dodge
    Ford
    Mercedes


    Is that right?


    CJ
     
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Hmmmm....well, so much for the spacing. ;-)
     
  6. Benjamin

    Benjamin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Yes right, it's exactly what I'm trying to do

    Any ideas ?
     
  7. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    It might be possible to create a view of the Car_Table from the Oracle DB to the SQL Server DB...and vice versa. No idea if that's possible...I've not heard of a business having an Enterprise System spanning different databases and querying them from either side w/ the same results.

    There are hub products out there from Oracle and IBM that setup translation protocols where one database 'listens' for changes on the other and that intermediary sucks up and posts the insert/update/delete...so the databases are all in sync. But that breaks your paradigm.

    I suspect your best option, should you continue pursuing this, would be to feed a 'cloud' from the disparate sites and query from that. It would be a lot simpler...might be cheaper as well.

    CJ
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot query one database and get results from the other, different, database included in the output with a single-table query. You MIGHT be able to create a materialized view in Oracle to contain data from both tables, and you may be able to create a standard view on one database containing all of the information from both.


    "I know that you can have a distributed database over several servers (different dbms) " I have never seen this done nor have I ever seen it documented that it CAN be done.
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    As a rule, the distributed database is created on a uniform platform.
    Between Oracle and MS Sql you won't be able to implement distributed database.
    That you can make, so it create communications between servers and exchange direct requests.
    Or to use other means for integration provided oracle.
    can be other option this use of ETL processors, such as Informatica