How to: Specify Synchronization Schedules (Replication Transact-SQL Programming)

When you create a subscription, you can define a synchronization schedule that controls when the replication agent for the subscription will run. If you do not specify scheduling parameters, the subscription will use the default schedule. You can define synchronization schedules programmatically using replication stored procedures. The stored procedures that you use depend on the type of replication and the type of subscription (pull or push).

A schedule is defined by the following scheduling parameters, the behaviors of which are inherited from sp_add_schedule (Transact-SQL):

  • @frequency_type - the type of frequency used when scheduling the agent.

  • @frequency_interval - the day of the week when an agent runs.

  • @frequency_relative_interval - the week of a given month when the agent is scheduled to run monthly.

  • @frequency_recurrence_factor - the number of frequency-type units that occur between synchronizations.

  • @frequency_subday - the frequency unit when the agent runs more often than once a day.

  • @frequency_subday_interval - the number of frequency units between runs when the agent runs more often than once a day.

  • @active_start_time_of_day - the earliest time in a given day when an agent run will start.

  • @active_end_time_of_day - the latest time in a given day when an agent run will start.

  • @active_start_date - the first day that the agent schedule will be in effect.

  • @active_end_date - the last day that the agent schedule will be in effect.

To define the synchronization schedule for a pull subscription to a transactional publication

  1. Create a new pull subscription to a transactional publication. For more information, see How to: Create a Pull Subscription (Replication Transact-SQL Programming).

  2. At the Subscriber, execute sp_addpullsubscription_agent (Transact-SQL). Specify @publisher, @publisher_db, @publication, and the Microsoft Windows credentials under which the Distribution Agent at the Subscriber runs for @job_name and @password. Specify the synchronization parameters, detailed above, that define the schedule for the Distribution Agent job that synchronizes the subscription.

To define the synchronization schedule for a push subscription to a transactional publication

  1. Create a new push subscription to a transactional publication. For more information, see How to: Create a Push Subscription (Replication Transact-SQL Programming).

  2. At the Subscriber, execute sp_addpushsubscription_agent (Transact-SQL). Specify @subscriber, @subscriber_db, @publication, and the Windows credentials under which the Distribution Agent at the Subscriber runs for @job_name and @password. Specify the synchronization parameters, detailed above, that define the schedule for the Distribution Agent job that synchronizes the subscription.

To define the synchronization schedule for a pull subscription to a merge publication

  1. Create a new pull subscription to a merge publication. For more information, see How to: Create a Pull Subscription (Replication Transact-SQL Programming).

  2. At the Subscriber, execute sp_addmergepullsubscription_agent. Specify @publisher, @publisher_db, @publication, and the Windows credentials under which the Merge Agent at the Subscriber runs for @job_name and @password. Specify the synchronization parameters, detailed above, that define the schedule for the Merge Agent job that synchronizes the subscription.

To define the synchronization schedule for a push subscription to a merge publication

  1. Create a new push subscription to a merge publication. For more information, see How to: Create a Push Subscription (Replication Transact-SQL Programming).

  2. At the Subscriber, execute sp_addmergepushsubscription_agent. Specify @subscriber, @subscriber_db, @publication, and the Windows credentials under which the Merge Agent at the Subscriber runs for @job_name and @password. Specify the synchronization parameters, detailed above, that define the schedule for the Merge Agent job that synchronizes the subscription.