November 6, 2009 at 10:02 am
I have a transactional publication containing one table with about 8million records(and growing). For this publication I have around 110 subscribers. And the number of subscribers increases every month or two. My concern is that if I ever had an issue where this publication needed to be rebuilt or all subscribers needed to be reinitialized I am not entirely sure how I would accomplish that. Right now taking the initial snapshot of that table takes around 50minutes. If I try and push that snapshot out to multiple subscribers at once it fails. So basically what I am seeing is if I had to reinitialize this then I am looking at around 120 hours to get all subscribers running again, assuming I don't hit any other issues.
I am the first to admit that this is a horrible design but this is something that I inherited and fixing the underlying issues is not a small task.
So I am looking for options on how i could do this in a more expedient fashion. I'm hoping that I could do something like stop all the distribution agents, mark all subscriptions for reinitialization, take the snapshot and then one by one turn on the distribution agents. But right now I do not believe that the initial snapshot is retained, which is probably a good thing normally. So is their a way to retain that snapshot and just keep using it?
would setting immediate_sync = true do this, or am I looking at the wrong command.
November 26, 2009 at 9:00 am
Hmmm..that does sound horrible.
Is it only new data (inserts) that is occuring or are there historic changes ?
How big is the database ?
If you needed to reinitailze all subscribers...could you backup/restore on each subscriber and apply no-synch subscriptions.
Have you tried BCP to data files and transferring those to the subscribers and then applying the snapshot locally (Alternate snapshot location).
Could you keep the replicated table size quite small by archiving records off locally and using unions in your queries at each subscriber.
Just a few things to think about...some probably no good at all 🙂
HTH
Graeme
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply