June 3, 2008 at 2:53 pm
My apologies if this has been asked before, but I'm interested in how other individuals restart their replication after a downtime maintenance event.
My scenario is that I have a 358 GB db, but only 40 tables of that are being replicated to the subscriber. The initial replication requires that I copy an entire complete db backup (unable to use the snapshot method) to the subscribing server and restore it. Then during the subscription creation, I synchronize using a recent trans log backup. I'm then free to remove all the unwanted objects from the subscribing db.
However, after I have to break replication for any reason, do I have to go through copying the complete database backup over to the subscribing server again and go through all that? Please tell me there is an easier way.
My appreciation in advance for any and all advice or ideas.
Bill
June 3, 2008 at 3:40 pm
IF your distribution retention period is BIG enough to cover for the outage you should be fine!
* Noel
June 3, 2008 at 4:47 pm
OK, so what is the recommended method for stopping/pausing replication for the duration of the maintenance event? What is the recommended method for restarting it?
June 3, 2008 at 7:23 pm
Is your replication a real replication or backup/restore? Seems there are a lot of manual processes for replicating the data?
June 3, 2008 at 7:28 pm
I'm only able to initialize replication with the backup method. It is the recommended method as per our vendor documentation.
June 4, 2008 at 3:06 am
How much data do your 40 published tables take up? Surely you'd be better off syncing them by BCP'ing out those 40 tables. Transferring them over to the subscriber and BCP'ing them in?
June 4, 2008 at 8:39 am
So if my subscribing db only has the 40 tables that I need replicated (via transactional replication), and I make sure no one is making any more changes to the publishing db, and bcp out the data in the 40 tables and load it into the subscribing 40 tables, how do I then initialize the subscription?
I appreciate you tolerating what must be dumb questions, but I'm trying to find a 1000% easier way to do this then the way the vendor docs say it must be done.
June 4, 2008 at 8:45 am
Another question,
If it takes three hours to run a full backup my db, and three hours to restore it to the subscriber, how do I ensure that my initializing transaction log backup will work considering there are going to be continuing updates to the publishing db up until the restore to the subscriber is complete.
Again, this may be a non-issue if I can get the 40 table bcp thing to work.
June 4, 2008 at 9:50 am
What reason would you need to "break" replication during the day? Just trying to figure your circumstances.
Intialising a subscription involves a line of code to add the subscriber (can be done by putting the subscriber in place manually, then scripting out replication and taking the bit you need for easiness).
And then you need to put the custom procs in place. Run on the publisher "sp_scriptreplicationcustomprocs " and run those scripts on the subscriber.
Sorry gotta dash!
June 4, 2008 at 12:10 pm
wjones21 (6/3/2008)
OK, so what is the recommended method for stopping/pausing replication for the duration of the maintenance event? What is the recommended method for restarting it?
You can stop and disable the Distribution Agent JOB. that's it.
As long as the distribution database is "retaining" the transaction happening during the subscriber maintenance you are FINE! Nothing else needs to be done. Once the maintenance is done you simply re-enable and start the distribution Agent Job.
hth
* Noel
June 5, 2008 at 1:00 pm
I believe I've been able to skirt this issue for now and have gone ahead and use a snaphot for the 40 tables in question. It seems to be working, but I have a new question that I have added labeled:
"Number of commands in the Distribution DB waiting to be applied to the Subscriber..."
I would imagine it's probably something simple but my lack of experience with replication leaves me in the dark.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply