Stop synchronizing the replication for 2 days.

Ben Thul

2010-08-22 22:31:48 UTC

These all seem like reasonable steps. However, I'd set your
min_distretention higher than you need it to be for the duration of
your request. That way, if somehow the distribution cleanup agent
runs on accident, you're covered. You can always set it back down to
what it normally is after you're done. Here are a couple of other
things to consider:

* Consider setting the retention higher gradually before you need to
do this event. This may or may not be possible depending on how far
away it is. I'm thinking about something like increasing min and max
retention 5 hours per day until they're where you want them to be.
That way, you can accommodate for the inevitable growth in the
distribution db. If you're feeling frisky, you can pre-extend the
database to what you think it will be as a result of this change (i.e.
if you normally keep one day's worth, you'll want to double the size
of the distribution db)
* Since you're going to be at least two days in the hole when this is
done, consider using subscription streams (look at BOL for
sp_changesubscription for more info on how to do this) to make the
delivery of replicated commands from the distributor to the subscriber
faster. You can always set it back down later if you don't like the
effect.
* If there are any indexed views (or something similar) at the
subscriber, consider dropping them while you're catching back up.
I'll be faster to do that and re-create them when all the data is
there than to have each and every replicated command update each view.
* I'm paranoid, so I might do something more to the distribution job
than just disabling it. I suppose it all depends on what the
consequences are if it runs accidentally during your event.
* Turn off any monitoring, alerts, etc for your topology. This is
especially true if you use any alerts like "drop inactive
subscribers".

That's all I can think of right now.

Good luck!
--
Ben

Post by Iter
Hi, I have question reqarding stoping synchronizing the replication for 2
days. I have sql server 2008 running windows 2003. according user's request,
they want me to stopp synchronizing the replication for 2 days. after 2 days,
start to synchronize the replication. I will do following steps to implement
it.
1. Stop the distribution agent(View Synchrozization status -> stop),
2. Disable the job of REPL- distribition
3. Disable the job of distribution cleanup - distribution
4. increase the following distributor property in order to save the
transation in distribution db for 2 days. Do I need to restart service in
order to take effect?
Can anyone tell me if this is enough? what else do I need to do? The
replication setup as non initial snapshot, so I cannot use snotshot to synch.
So I need to keep the transaction in distribution db for save. Let me know.
Thanks in advance.