Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44

    Unanswered: tables need to be in line across multiple databases (was "Give Me Your Suggestion")

    Hello All,

    I have multiple SQL Server 2000 databases all identical in structure (Company1.mdf, Company2.mdf, Company3.mdf). I have a situation where while connected to Company1, i want to create a record in Table1 and have that same record automatically be created within Company2 and Company3.

    If i was working with MSAccess, i could use linked tables where i would link Company2 and Company3 to Company1 Table1. This way if any changes are made, all three companies and working from the same record set.

    My situation deals with mutiple tables that need to be in line with each other across mutliple databases.

    Your suggestions are appreciated.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you could merge you database or use merge replication if you got the stomach for it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    Correct me if i'm wrong, my understanding of Merge Replication is something thats done periodically, where as what i need is a real time data update between the 3 companies.

    I forgot to mention that Company1, Company2, and Company3 are all on the same SQL Server Registration.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Sammy_S
    Correct me if i'm wrong
    Consider yourself corrected.

    Merge replication will work near real-time (milli-seconds good enough?) if desired.

    Or if you want to tie up all your databases for consistency purposes (real real-time), you could use a trigger to synchronize all tables in all databases.

    Or you could log ship with maybe a minute or two latency between databases if that's good enough.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    the concensus seems to be Merge replication...

    any objections or alternatives!

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it's a pain in the arse to impliment and monitor.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Thrasymachus
    it's a pain in the arse to impliment and monitor.
    Aye laddie, that it 'tis...

    However, with a well designed and well architected database and with good network links, domain controllers that never falter, users who only enter data between 9am and 4pm, a schema that never needs to be changed and tables that are all normalized with clear foreign key constraints, it can be a real breeze!

    JK...

    I am in the process of deploying a 60+ node Merge Replication solution with a database that is moderately well normalized and contains lots of identity columns. It's been a HUGE challenge for me personally, but I do think the results will be worth it.

    Regards,

    hmscott
    Have you hugged your backup today?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My suggestion....

    Ditch Access
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Brett Kaiser
    My suggestion....

    Ditch Access
    yeah, get a web server and fire up IIS. one database and thin clients. it's a new century.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    I'm not using Access, I'm using SQL Server!!!

Posting Permissions

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