One Way Transactional Replication: Multiple subscribers for single Publisher

  • Hi Friends,

    I have a scenario where there are two servers A and B. A act as a publisher and B acts as a subscriber and transactional replication runs from A to B and B is used for reporting work.

    Now, I have a request from the business to create a new server C with the same replicated data. I have two choices here:

    1) I create Server-C as a subscriber and let server Server-A continue acting as publisher, therefore acting as subscriber for Server-B and Server-C. But I am not sure how this is going to be maintained?

    Let's say I need to re-initialize subscriber-C with a new snapshot, will it mean that it will apply same snapshot to existing and old subscriber Server-B.

    Can these two subscribers work independently?

    2) Another option in front of me is to treat Server-B as a publisher and have it replicate the data to Server-C. So for me it will become something like a daisy chain:

    A--->B---C

    I am worried here because what happens if I want to re-initialize subscriber B. In that case a snapshot from Server-A will run and the distributor agent by default drops all the tables in the subscriber and populate them again and since Server-B will be acting as a publisher for Server-C, it may not be allowed for distributor agent to work normally.

    Please give your inputs to this situation. Thanks in advance!

    Regards

    Chandan Jha

  • If you are only worried about generating snapshot, which will affect both B & C, Use Server A as Publisher for both, and instead of initializing from snapshot, choose initialize from backup.

  • I think option 1 would be the best and easiest to manage in this situation.

    Re-publishing from a subscriber adds another level of complexity.

    You can re-initialize a single subscription without effecting the other, or as previously mentioned you could do this via a backup.

    Cheers

    Vultar

  • Thank you folks for replying. I have kept the publisher common and added the B and C as subscribers there only. Initially, I was not able to reinitialize server C without affecting the other subscriber B and to meet the deadlines I chose to reinitialize both the subscribers.

    I need to learn how to reinitialize one subscriber with a new snapshot without affecting the other subscriber.

    Please feel free to put any sort of ideas that you have worked upon, it doesn't have to be a complete answer.:-P

    Regards

    Chandan

  • Another option other then suggested above would be, Initilizing the subscriber from backup, this saves a lot of time of applying snapshot to subscriber,

    The intial snapshot apply would also be problem where your databse is huge with millions of rows and the connectivity between publisher and subscriber not that good.

  • Bhaskar.Shetty (5/23/2013)


    Another option other then suggested above would be, Initilizing the subscriber from backup, this saves a lot of time of applying snapshot to subscriber,

    The intial snapshot apply would also be problem where your database is huge with millions of rows and the connectivity between publisher and subscriber not that good.

    I understood your suggestion but in case the connectivity is not so great, taking a fresh backup and copying it and restoring it would take almost an equal amount of time if not less. What's your take on that?

    Thanks

    Chandan

  • chandan_jha18 (5/23/2013)


    Bhaskar.Shetty (5/23/2013)


    Another option other then suggested above would be, Initilizing the subscriber from backup, this saves a lot of time of applying snapshot to subscriber,

    The intial snapshot apply would also be problem where your database is huge with millions of rows and the connectivity between publisher and subscriber not that good.

    I understood your suggestion but in case the connectivity is not so great, taking a fresh backup and copying it and restoring it would take almost an equal amount of time if not less. What's your take on that?

    Thanks

    Chandan

    Taking a fresh backup from publisher and restoring it on subscriber should not be slow irrespective of network connectivity, as you are restoring the whole database from the backup set, and later add the subscriber using below code.

    -- To Add Subscription from Backup Set --

    EXEC sp_addsubscription

    @publication ='myPublication', --> Name of the publication

    @subscriber = 'myserver.myDomain.com', --> Fully qualified name or IP of subscriber server

    @destination_db ='MySubscriberDB', --> Name of the database you just restored (doesn't have to be the same as the publisher)

    @sync_type = 'initialize with backup', --> no need to change this

    @backupdevicetype = 'disk', --> no need to change this

    @backupdevicename = 'backupfile.bak' --> Pointer to the last backupfile that was restored, but from the folder on the on the

    --> publishing server. If you restored trans logs also the last translog file is what you

    --

    This will only work if Initilize subscriber from backup set is set to true in publication property.

  • Thanks Bhaskar. Will try this option once to see how it works. I have a question though. Lets say that the replication is running for 1 subscriber and I edit the publication property to reinitialize from backup.

    Now while I take the backup, there will be new transactions originating from publisher. So will those be automatically applied to the subscriber when i restore the subscriber database from backup or do i need to do anything else

    Thanks

    Chandan

  • chandan_jha18 (5/23/2013)


    Thanks Bhaskar. Will try this option once to see how it works. I have a question though. Lets say that the replication is running for 1 subscriber and I edit the publication property to reinitialize from backup.

    Now while I take the backup, there will be new transactions originating from publisher. So will those be automatically applied to the subscriber when i restore the subscriber database from backup or do i need to do anything else

    Thanks

    Chandan

    After you take backup and till you apply it on subscriber, disable the Distribution clean up: distribution agent, this will stop the cleanup of distributed transaction after replication to subscriber, after subscription added sucessfully, you can re-enable this agent so all the pending transaction will get applied to new subscriber too..

    To Disable this agent Goto SSMS -> Sql Server Agent -> Jobs -> Distribution clean up: distribution.

  • Thanks a lot for your help on this. Will post again if I find difficulty in this method. worth giving a shot rather than snapshot method.:-D

    Cheers!

    Chandan Jha

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply