Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Question Unanswered: Database Mirroring...

    Guys.. i need a help...

    Here is the situation...
    I have two databases.. Database1 and Database2....have same schema...
    What ever i change( Update or delete or insert... what ever ) in Database1, Database2 should also get changed with same changes...

    But when i change something in Database2... Database1 should not be changed...

    Got itt.. its something like Official accounts database ( Database1 ) and Unofficial accounts database ( Database2 ).....

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ahh, the old double ledger problem.

    Database Mirroring is likely out for this application, because the mirror database will be down and not updatable while the primary database is up. I think you will need to look into either Transactional Replication or more likely Merge replication.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd maintain two copies of the data structure in the target database, under different schemas. One schema would be local, while the other schema would be replicated from the source database. Union together the tables in views for querying the combined data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2008
    Posts
    3
    Can u explain it briefly...

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    More briefly than I already did?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2008
    Posts
    3
    sorry i was refereing to MCrowely... he was telling about Transactional Replication.. i dont know that concept.... and i was asking him to explasin abt it briefly...

    any ways thanx for the reply.. ill implement it... and post if i get any probs..

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You should be able to find a wealth of information about Transactional Replication and Merge Replication in Books online.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you really update the subscriber without replicating back to the publisher? And don't you dare refer me to BoL.... A "yes" will suffice.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In transactional replication, of course!

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. That is "one-way" replication.
    Whether it is a good idea is another issue...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've used one way replication - I just assumed that the published data was read only. I had no reason to want it otherwise so never looked at the issue. So how does it handle replicating updates from the published database if the equivalent row has been edited in the subscriber database? * There are a variety of different ways to handle this that would depend on the business rules.


    *Actually don't answer that - it's sort of rhetorical

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I believe the publisher simply sends the new value. If you ran something like this on the publisher;
    Code:
    update products
    set price = price * 1.1
    Only the new prices would be sent to the subscriber. The subscribers would not be required to try to do all that tricky decimal math in figuring out the new price. After all, why should they? The publisher already knows what the new price is supposed to be.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So that would update the price column only in every row at the subscriber? All other columns in the row do not get replicated? And what if someone had changed the price at the subscriber - does that get overwritten?

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    That should get overwritten, but I don't have a system to prove that out on.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's why I would really want to know the replication rules inside out as it is not too unlikely that would contradict the business rules.

    Don't go proving owt. I do fancy rereading BoL replication now though because (and again I didn't have this question in mind when I last worked with it so am a bit hazy) I thought the entire row was replicated, not just the changed column value.

Posting Permissions

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