Blog Post

Make Sure to Schedule Your SQL Server Replication Jobs

,

You setup SQL Server Replication and everything looks fine. Rows are flowing between the servers and you think you’re done.

Right? Wrong..

At some point in time, your replication jobs will stop (some replication failure, stop for maintenance, etc..). Once that happens, will they start working again?

When you setup a publication and subscription (Transactional for that matter), a few jobs are created:

  • The Log Reader Agent
  • The Snapshot Agent
  • The Distribution Agent

The problem is that they are created with a schedule that starts them once the SQL Server service starts. If one of them stops for another reason, it won’t be started automatically:

Replication Agents Not Scheduled

So here’s the recommended way for scheduling replication jobs..

For the Distribution and LogReader agent jobs, do the following (you usually don’t need to do so for the Snapshot agent):

  1. Double-click on the job name and choose Schedules.
  2. In the Schedules screen, click on Pick:

SQL Server Agent Scedule Pick

  1. Choose from the variety of options:

SQL Server Agent Schedule Pick

Once you do that, even if the jobs fail or stopped for some reason, they will be restarted. If a job is already running, it won’t be interfered or started again.

However, you should remember that in case you want to intentionally stop replication for some kind of maintenance,  you should disable the job or the schedule in order for it not to start when you don’t want it to.

The post Make Sure to Schedule Your SQL Server Replication Jobs appeared first on .

Rate

Share

Share

Rate