Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

One Way Transactional Replication: Multiple subscribers for single Publisher Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 6:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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
Post #1454510
Posted Monday, May 20, 2013 8:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 31, 2014 4:01 PM
Points: 835, Visits: 1,192
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.
Post #1454550
Posted Wednesday, May 22, 2013 11:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 238, Visits: 1,107
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
Post #1455629
Posted Thursday, May 23, 2013 12:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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.

Regards
Chandan

Post #1455832
Posted Thursday, May 23, 2013 12:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 530, Visits: 442
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.
Post #1455838
Posted Thursday, May 23, 2013 1:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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

Post #1455848
Posted Thursday, May 23, 2013 1:23 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 530, Visits: 442
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.
Post #1455851
Posted Thursday, May 23, 2013 2:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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

Post #1455866
Posted Thursday, May 23, 2013 2:12 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 530, Visits: 442
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.
Post #1455868
Posted Thursday, May 23, 2013 2:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:29 PM
Points: 466, Visits: 1,925
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.

Cheers!

Chandan Jha
Post #1455874
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse