Hoping someone out there can help a novice with what is likely a basic question:
I am currently trying to configure transactional replication between a remote publisher and my local subscriber (both MS SQL 2000) using a pull subscription from the subscriber. The remote publisher is rebooted about once per week, and the distribution agent is not restarting/reconnecting to resume applying transactions when the publisher comes back up after the reboot.
I tried to modify the properties of the distribution agent to retry every minute for 60 attempts (i.e. an hour of downtime), and the reboot of the publisher only takes about 15 minutes. Since I want to the distribution agent to be running continually, should I set up a circular set of steps in the properties of the distribution agent so that on failure it just continues to retry the agent? Or does this boil down to the Refresh Rate/Settings? After ten minutes, I see the "agent is suspect" wanring, but don't know whether this is a cause or effect of my problem. Any suggestions are greatly appreciated for a fella trying to firm up his SQL server and replication knowledge...
In a fully replicated environment, or log shipped, restart SQL or the server itself would have to be stopped and started in sequence. Stop and start one component of the replication could cause data discrepancy problem or dead agents. The sequence of stop and restart is 1) Subscriber, 2) Distributor, and 3) Publisher. When you restart reverse the order of stopping.
It was my understanding that when the server hosting the publication database was rebooted, any transactions not yet applied were queued for application to the subscriber when the connection was re-established. The maintenance reboots on the server in question are done once per week in the wee hours of the morning about 1000 miles from where I'm located with the subscription db. So...I'm hoping to be able to automate the process so that the distribution agent will begin applying the transactions as soon as the publication db is available (i.e. when that server is back up after the reboot).
Is this problematic? I thought that the only issue would be creating steps to perhaps restart synchronization on the pull subscription after a reboot and/or tinker with the distribution agent to have it continually try to reconnect until it could get to the publisher. If I'm asking for trouble with this scenario, let me know...
OK! So you don't have control of the publisher machine. In that case, you would have to change the agent schedule to avoid the publisher reboot. What I mean is to make the pull agent idle during that time. This way it won't call for information from the publisher during that time. Once it reboot has been completed, the agent will work normally. But the risk is that if the Publisher doesn't come up and you didn't notice, the pull agent will die.