May 20, 2013 at 6:53 am
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
May 20, 2013 at 8:14 am
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.
May 22, 2013 at 11:46 am
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
May 23, 2013 at 12:31 am
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
May 23, 2013 at 12:46 am
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.
May 23, 2013 at 1:17 am
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
May 23, 2013 at 1:23 am
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.
May 23, 2013 at 2:05 am
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
May 23, 2013 at 2:12 am
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.
May 23, 2013 at 2:32 am
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