SQL Server Performance

Merge Replication

Discussion in 'General DBA Questions' started by dineshasanka, Nov 25, 2005.

  1. dineshasanka Moderator

    I have a requirement whcih I have given in following steps

    1. Main Database (MD) need to be replciated to Sub database (SD1)
    2. SD1 need to be replicated to another sub database (SD2)

    both replication should have merge replication. according to my understanding you can't have merge replication in at both stages.

    What are the alternatives that you guys can offer me!!

    ----------------------------------------
    Cast your vote
    http://www.geocities.com/dineshasanka/sqlserver05.html

    http://spaces.msn.com/members/dineshasanka
  2. ghemant Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><br />1. Main Database (MD) need to be replciated to Sub database (SD1)<br />2. SD1 need to be replicated to another sub database (SD2)<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />as i understand and feel it is not merged database replicate via MD -&gt; SD1 -&gt; SD2 ,<br />if MD -&gt; SD1 (Replicate via MD to SD1 and not SD1 to MD (Merged))<br />and<br />SD1 -&gt; SD2 (Replicate via SD1 to SD2 and not SD1 to SD2 (Merged)) <br />then you only have need to have Transactional Replication .<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  3. dineshasanka Moderator

  4. ghemant Moderator

    Hi,<br />is it something like this ! <br /><br />MD -&gt; SD1<br />SD1 -&gt; MD <br />and <br />SD1 -&gt; SD2 <br />SD1 &lt;- SD2 <br /> <br />or <br />Case 2 :<br />MD -&gt; SD1 <br />SD1 -&gt; SD2 <br /><br />i think this article would help you :<br /<a target="_blank" href=http://www.mssqlcity.com/Articles/Replic/SetupMR/SetupMR.htm>http://www.mssqlcity.com/Articles/Replic/SetupMR/SetupMR.htm</a><br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  5. dineshasanka Moderator

  6. ghemant Moderator

    Hi,<br />then why don't you tried for Transactional Replication !<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  7. dineshasanka Moderator

  8. ghemant Moderator

    Hi,
    above refered article said the same ,
    MD is a publisher & Distributor for SD1 and
    and SD1 is a publisher & Distributor for SD2


    Regards


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  9. dineshasanka Moderator

  10. satya Moderator

    Try removing and recreating afresh.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. dineshasanka Moderator

  12. ghemant Moderator

    Hi Dinesh,
    i think you are talking of case 1 .
    MD is a publisher & Distributor for SD1 and
    and SD1 is a publisher & Distributor for SD2

    and i feel its not possible to use subsriber database to be a publisher for another machine at the same time ...

    Dada, what do you say !


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  13. satya Moderator

    Yes in this case it is not possible to use as so.
    Dinesh, Imean to say remove replication and recreate with proper servernames again.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. ghemant Moderator

    Hi,<br />then what would if Dinesh set up Transaction Replication Vice - Versa !? is it feasible or not !<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
  15. dineshasanka Moderator

  16. ghemant Moderator

    Hi,
    and what if you do it Transaction Replication and log shipping !? mean

    MD -> SD1 (Transactional Replication) and
    SD1 -> SD2 (log shipping)

    is it feasible !


    Regards


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  17. dineshasanka Moderator

  18. ghemant Moderator

    Hi Dinesh,
    i was asked that what if you setup

    MD -> SD1 (MD is a publisher,distributor for SD1) and at the same time

    SD1 -> SD2 (SD1 is a publisher,distributor for SD2)

    SD2 -> MD (SD2 is a publisher,distributor for MD)

    or

    MD -> SD1 (Merged Replication)

    SD1 -> SD2 (Merged Replication)

    SD2 -> MD (Merged Replication)

    What do u think !


    Regards



    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  19. dineshasanka Moderator

  20. ghemant Moderator

    Hi Dinesh,
    now turns for Satya, Frank,Luis,Brad,marovic,adriaan ! see what they suggest !


    Regards


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  21. SQLDBcontrol New Member

    Yes, this is possible - you would need to use immediate updating subscribers.

    quote:Originally posted by dineshasanka


    quote:Originally posted by ghemant

    Hi,
    then what would if Dinesh set up Transaction Replication Vice - Versa !? is it feasible or not !


    Can trnasaction replication allow users to update from teh both ends. I mean fron the publisher nd from the subscriber

    ----------------------------------------
    Cast your vote
    http://www.geocities.com/dineshasanka/sqlserver05.html

    http://spaces.msn.com/members/dineshasanka


    Karl Grambow

    www.sqldbcontrol.com
  22. SQLDBcontrol New Member

    I accidentally pressed the submit button.

    I'm curious, why is that you want to replicate data from your subscriber? There are some situations where you might want to go for what you are suggesting - typically when you want to publish across a WAN instead of the LAN. This is why I was asking why you wanted to publish SD1 to SD2, I just want to understand your motivations a bit better.

    If you are using merge replication then your publisher (MD) will be updated whenever a change is made at your subscriber (SD1). So why not just publish MD to both subscribers, SD1 and SD2. This is how merge replication was designed to work and in this case it seems to make more sense.



    Thanks,


    quote:Originally posted by dineshasanka

    I have a requirement whcih I have given in following steps

    1. Main Database (MD) need to be replciated to Sub database (SD1)
    2. SD1 need to be replicated to another sub database (SD2)

    both replication should have merge replication. according to my understanding you can't have merge replication in at both stages.

    What are the alternatives that you guys can offer me!!

    ----------------------------------------
    Cast your vote
    http://www.geocities.com/dineshasanka/sqlserver05.html

    http://spaces.msn.com/members/dineshasanka


    Karl Grambow

    www.sqldbcontrol.com
  23. dineshasanka Moderator

  24. SQLDBcontrol New Member

    <br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by dineshasanka</i><br /><br />I am totaly agree with you. But you know these customers. They want excatly the same configuration that I specified.<br />I have already told them that this is not possible.<br /><br />----------------------------------------<br />Cast your vote <br /<a target="_blank" href=http://www.geocities.com/dineshasanka/sqlserver05.html>http://www.geocities.com/dineshasanka/sqlserver05.html</a><br /><br /<a target="_blank" href=http://spaces.msn.com/members/dineshasanka>http://spaces.msn.com/members/dineshasanka</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Actually, it is possible. The point though isn't whether it's possible but whether it is the right solution.<br /><br />All you need to do is set the resolver such that the subscriber SD1 wins all conflicts against the publisher MD. As long as you do not allow anonymous subscriptions then you should be able to set up SD1 as a publisher to SD2.<br /><br />The thing to realise though is that in setting the subscriber to win all conflicts you are effectively making that one a publisher - sort of. Which is exactly what I suggested when I said publish the database to both SD1 and SD2 from MD. Except now you're publishing from SD1 to MD and SD2.<br /><br />The point is, that with merge replication you can only really publish from the conflict winner (which is normally the publisher). And there's a good reason for that.<br /><br />It seems here that the problem is the customer rather than merge replication.<br /><br />You obviously know your customer better than I do but I would say that it is not the customers job to tell you what to do and how to do it. Generally the customer tells us what they want and we provide them with the solution that most effectively gives them what they want. The customer might think they want a given solution but more often than not it's the wrong solution [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]. In my experience anyway.<br /><br /><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  25. ghemant Moderator

    Hi,<br />the article<a target="_blank" href=http://www.mssqlcity.com/Articles/Replic/SetupMR/SetupMR.htm>http://www.mssqlcity.com/Articles/Replic/SetupMR/SetupMR.htm</a> refered by me is also saying that it can be done via * Publishing subscriber * <br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />

Share This Page