SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


One Way Transactional Replication: Multiple subscribers for single Publisher


One Way Transactional Replication: Multiple subscribers for single Publisher

Author
Message
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2205 Visits: 2134
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
Neeraj Dwivedi
Neeraj Dwivedi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1943 Visits: 1341
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.
vultar
vultar
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 1220
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
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2205 Visits: 2134
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
Bhaskar.Shetty
Bhaskar.Shetty
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 509
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.
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2205 Visits: 2134
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
Bhaskar.Shetty
Bhaskar.Shetty
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 509
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.
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2205 Visits: 2134
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
Bhaskar.Shetty
Bhaskar.Shetty
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 509
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.
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2205 Visits: 2134
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search