How to configure distributor thread for being up while replicated database is down

Hi
I often deal with a problem when replicated database is down due to electric outage or any other scheduled type of downtime. Primary database is up but distribution agent is going down 'after 10 attempts to connect to replicated database'. That's the problem I think because not only distribution agent goes in undetermined state and I have to restart sqlagent service, but I need to go through whole process of snapshot and applaying it to replicated db. I would like it to accumulate transactions in distribution database and once replicated db is up - distribute them.
I wonder if anybody can tell me how to configure distribution thread so it does not go down after 10 unsuccessfull attempts but stays for day or two.
Thank you, Gene.

Gene, why do you need to re-apply the snapshot, is your retention period very small? By default it's 72 hours, so if distribution agent can get back up within that time period, it should just pick up where it left off.

Unfortunately if your distribution agent is running in Continuous mode (which you mention since it goes down after 10 unsuccessful attempts), the workaround would be change it so that the agent runs on a scheduled interval, like every minute. This way in the case the machine gets knocked off the network, the agent will always run every N minutes, pass or fail. As soon as the machine gets back on the network, it should pick up where it left off, provide the oldest txn that hasn't been replicated is within the retention period.

|||

The other possible solution is to increate the number of retry attempts for your distribution agent job, when you view job steps of an agent job, there is an advanced tab, on that screen, you can specify a big value for "Retry Attempts" and how often you want the retry to happen "Retry Interval (minutes)". For example, if you want the retry lasts for 2 days, you could put 2880 for Retry Attempts and 1 for Retry Interval.

Hope that helps,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||I don't think this exists for SQL 2000, but if it does, then this is also a good solution.