SQL Server Performance Forum – Threads Archive
Help me with Transactional replication.Hi, everybody.
I have a problem with Transactional replication.
I configure Publisher and Distributor in the same server. Subscriber is in another server. I create a publication and push it from Publisher to Subscriber. Everything works well.
Now I turn off Subscriber. After about 30 minutes or more (I’m not sure), there is an error in Publisher. The error is "The … could not connect to Subscriber…". Of course, replication die. I check Distribution Agent. It doesn’t work.
Now I turn on Subscriber. Replication doesn’t still work. I have to right click Distribution Agent and choose "Start Agent". Everything works well.
I don’t know exactly how much time from when Subscriber shutdowns to when replication die. I wonder if I can prolong this time or not. And I want replication to continue automatically although I shutdown Subscriber a long time and then turn it on.
A kind man suggested me to do as follows. I should create a job that checks Distribution Agent and start it if it stops. It seems little complicated to me.
Is there another way to solve my problem?
Please, help me.
If you have scheduled the distribution agent to run every hour or something it should try and replicate again even if you shut down the subscriber for 2 hours. If you are running continuous replication it might fail and never try again. In that case I would schedule replication to every 1 minute if you need that fast updates and skip the continuously setting. Under "Configure Publishing, Subscribers and Distribution" -> distribution tab you can also specify how many hours the transactions should be stored in the distribution database. I think 72 hours is default.
You mean I should not use continuous replication. But if I schedule Distribution Agent to run every 1 minute, will replication die for 2 hours? In my company, the Subscriber may shutdown for 5 days.
I’m saying do not use continuous replication if it fails and never restarts. Schedule the replication to an intervall to your liking be it 1 minute or 1 hour. If your subscriber is down for 5 days then increase the number of hours/days the transactions are stored in the distribution database. Note that if you have many transactions your distribution database can become very large. Change this under:
"Configure Publishing, Subscribers and Distribution" -> distribution tab A better option for you might be to use a pull subscription from the subscriber instead so that it fetches the data when it comes online.