locked
Replication better approach RRS feed

  • Question

  • Hi All,

    I am doing transaction Replication of one database and want better approach and some clarification

    for example:-

    I have A database and want to replicate in two different location It create two publication for database A in Local Publication .My question is if it create two publication then it send the data two times to distributor ?

    And one more question I want to replicate same DB to two different location in US and Kolkatta and my publisher db also in Kolkata and I want one replication server in Kol and one in US  then I am using remote distributor in Kol and Now this is my approch:-

    A- Publisher in Kol

    B-Distribution in Kol

    C- Subscriber in Kol

    D- Subscriber in US

    Please let me know what i have to use pull and push Subscription and any other approch which i have to use sending data through WAN

    if any modification required then please help .

    Thanks in advance.

    Tuesday, May 13, 2014 9:23 AM

All replies

  • Yes. There are no restrictions on the number of publications that can use the same distribution database. for example, if database article has undergone 12 transaction, since it has two publisher, there will be 24 transaction at the distributor and again it will split 12 transactions to each subscription databases.

    You can use push subscription.

    --Prashanth

    Tuesday, May 13, 2014 2:49 PM
  • The log reader will read it one time from the log and construct the commands and send it to the distributor twice for each publication.

    Use pull subcriptions as there are efficiencies for using pull subscriptions over a wan.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Proposed as answer by Sofiya Li Wednesday, May 14, 2014 7:40 AM
    Tuesday, May 13, 2014 4:24 PM
    Answerer
  • If you subscribing same data to your subscribers in Kol and USA, I do not see the point of creating two publication.  It is just more work for you distribution server.

    Maybe there are advantage that I am missing.  Is there a reason you wanted to design that way?

    Tuesday, May 13, 2014 5:07 PM
  • Hi All,

    Thanks for replying.

    I think Hilary is saying right .

    @hilary : Is there any way for sending the data in distribution only one time for two publication .

    Any other approch which i have to use sending data through WAN

    I am subscribing same data twice for two different application read same data in different location and reporting purpose also.

    Is there any othe way for same approach ..

    if any modification required then please help .

    Thanks in advance.

    Tuesday, May 13, 2014 5:45 PM
  • Hi Everyone,

    waiting for response..........

    Thanks

    Friday, May 16, 2014 11:47 AM
  • I am still a little confused about what you are trying to do.

    It sounds like you have a single publisher/distributor and are replicating to 2 different subscribers. You want to make this as efficient as possible.

    Your topology is the best, because if data does not go to subscriber 2 but makes it to subscriber 1, you want it to also go to subscriber 2 when it comes on line. This store and replay method ensures that replication will be able to pick up where it left off and to ensure that both subscribers get the same dataset, although they might not get the same data at the same time - ie Kol being closer to the publisher will have a lower latency and be more up to date than the US server.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, May 16, 2014 5:10 PM
    Answerer
  • Hi Hillary,

    Thanks for updating and best approach required ,I had done some modification in same scenario  and I ahve raise a new request so please helpul in that thread also.

    Thanks

    Saturday, May 17, 2014 6:24 PM