Skip to content

Latest commit

 

History

History
328 lines (183 loc) · 23.5 KB

specify-synchronization-schedules.md

File metadata and controls

328 lines (183 loc) · 23.5 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords monikerRange
Specify Synchronization Schedules
Learn how to specify synchronization schedules in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects.
MashaMSFT
mathoma
03/14/2017
sql
replication
how-to
updatefrequency5
subscriptions [SQL Server replication], synchronizing
scheduling synchronization [SQL Server replication]
synchronization [SQL Server replication], schedules
replication [SQL Server], synchronization
=azuresqldb-mi-current||>=sql-server-2016

Specify Synchronization Schedules

[!INCLUDE SQL Server SQL MI] This topic describes how to specify synchronization schedules in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or Replication Management Objects (RMO). 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.

Subscriptions are synchronized by the Distribution Agent (for snapshot and transactional replication) or the Merge Agent (for merge replication). Agents can run continuously, run on demand, or run on a schedule.

In This Topic

Using SQL Server Management Studio

Specify synchronization schedules on the Synchronization Schedule page of the New Subscription Wizard. For more information about accessing this wizard, see Create a Push Subscription and Create a Pull Subscription.

Modify synchronization schedules in the Job Schedule Properties dialog box, which is available from the Jobs folder in [!INCLUDEssManStudioFull] and from the agent detail windows in Replication Monitor. For information about starting Replication Monitor, see Start the Replication Monitor.

If you specify schedules from the Jobs folder, use the following table to determine the agent job name.

Agent Job name
Merge Agent for pull subscriptions <Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<SubscriptionDatabase>-<integer>
Merge Agent for push subscriptions <Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<integer>
Distribution Agent for push subscriptions <Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<integer> 1
Distribution Agent for pull subscriptions <Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<SubscriptionDatabase>-<GUID> 2
Distribution Agent for push subscriptions to non-SQL Server Subscribers <Publisher>-<PublicationDatabase>-<Publication>-<Subscriber>-<integer>

1 For push subscriptions to Oracle publications, it is <Publisher>-<Publisher> rather than <Publisher>-<PublicationDatabase>

2 For pull subscriptions to Oracle publications, it is <Publisher>-<DistributionDatabase> rather than <Publisher>-<PublicationDatabase>

To specify synchronization schedules

  1. On the SynchronizationSchedule page of the New Subscription Wizard, select one of the following values from the Agent Schedule drop-down list for each subscription you are creating:

    • Run continuously

    • Run on demand only

    • <Define Schedule...>

  2. If you select <Define Schedule...>, specify a schedule in the Job Schedule Properties dialog box, and then click OK.

  3. Complete the wizard.

To modify a synchronization schedule for a push subscription in Replication Monitor

  1. Expand a Publisher group in the left pane of Replication Monitor, expand a Publisher, and then click a publication.

  2. Click the All Subscriptions tab.

  3. Right-click a subscription, and then click View Details.

  4. In the Subscription < SubscriptionName> window, click Action, and then click <AgentName> Job Properties.

  5. On the Schedules page of the Job Properties - <JobName> dialog box, click Edit.

  6. In the Job Schedule Properties dialog box, select a value from the Schedule Type drop-down list:

    • To specify that the agent should run continuously, select Start automatically when SQL Server Agent starts.

    • To specify that the agent should run on a schedule, select Recurring.

    • To specify that the agent should run on demand, select One time.

  7. If you select Recurring, specify a schedule for the agent.

  8. Select OK.

To modify a synchronization schedule for a push subscription in Management Studio

  1. Connect to the Distributor in [!INCLUDEssManStudio], and then expand the server node.

  2. Expand the SQL Server Agent folder, and then expand the Jobs folder.

  3. Right-click the job for the Distribution Agent or Merge Agent associated with the subscription, and then click Properties.

  4. On the Schedules page of the Job Properties - <JobName> dialog box, click Edit.

  5. In the Job Schedule Properties dialog box, select a value from the Schedule Type drop-down list:

    • To specify that the agent should run continuously, select Start automatically when SQL Server Agent starts.

    • To specify that the agent should run on a schedule, select Recurring.

    • To specify that the agent should run on demand, select One time.

  6. If you select Recurring, specify a schedule for the agent.

  7. Select OK.

To modify a synchronization schedule for a pull subscription in Management Studio

  1. Connect to the Subscriber in [!INCLUDEssManStudio], and then expand the server node.

  2. Expand the SQL Server Agent folder, and then expand the Jobs folder.

  3. Right-click the job for the Distribution Agent or Merge Agent associated with the subscription, and then click Properties.

  4. On the Schedules page of the Job Properties - <JobName> dialog box, click Edit.

  5. In the Job Schedule Properties dialog box, select a value from the Schedule Type drop-down list:

    • To specify that the agent should run continuously, select Start automatically when SQL Server Agent starts.

    • To specify that the agent should run on a schedule, select Recurring.

    • To specify that the agent should run on demand, select One time.

  6. If you select Recurring, specify a schedule for the agent.

  7. Select OK.

Using Transact-SQL

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 Create a Pull Subscription.

  2. At the Subscriber, execute sp_addpullsubscription_agent (Transact-SQL). Specify @publisher, @publisher_db, @publication, and the [!INCLUDEmsCoName] 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 Create a Push Subscription.

  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 Create a Pull Subscription.

  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 Create a Push Subscription.

  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.

Using Replication Management Objects (RMO)

Replication uses the SQL Server Agent to schedule jobs for activities that occur periodically, such as snapshot generation and subscription synchronization. You can use Replication Management Objects (RMO) programmatically to specify schedules for replication agent jobs.

Note

When you create a subscription and specify a value false for CreateSyncAgentByDefault (the default behavior for pull subscriptions) the agent job is not created and scheduling properties are ignored. In this case, the synchronization schedule must be determined by the application. For more information, see Create a Pull Subscription and Create a Push Subscription.

To define a replication agent schedule when you create a push subscription to a transactional publication

  1. Create an instance of the xref:Microsoft.SqlServer.Replication.TransSubscription class for the subscription you are creating. For more information, see Create a Push Subscription.

  2. Before you call xref:Microsoft.SqlServer.Replication.Subscription.Create%2A, set one or more of the following fields of the xref:Microsoft.SqlServer.Replication.Subscription.AgentSchedule%2A property:

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyType%2A - the type of frequency (such as daily or weekly) you use when you schedule the agent.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyInterval%2A - the day of the week that an agent runs.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyRelativeInterval%2A - the week of a given month when the agent is scheduled to run monthly.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyRecurrenceFactor%2A - the number of frequency-type units that occur between synchronizations.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencySubDay%2A - the frequency unit when the agent runs more often than once a day.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencySubDayInterval%2A - the number of frequency units between runs when the agent runs more often than once a day.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveStartTime%2A - earliest time on a given day that an agent run starts.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveEndTime%2A - latest time on a given day that an agent run starts.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveStartDate%2A - first day that the agent schedule is in effect.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveEndDate%2A - last day that the agent schedule is in effect.

    [!NOTE]
    If you do not specify one of these properties, a default value is set.

  3. Call the xref:Microsoft.SqlServer.Replication.Subscription.Create%2A method to create the subscription.

To define a replication agent schedule when you create a pull subscription to a transactional publication

  1. Create an instance of the xref:Microsoft.SqlServer.Replication.TransPullSubscription class for the subscription you are creating. For more information, see Create a Pull Subscription.

  2. Before you call xref:Microsoft.SqlServer.Replication.PullSubscription.Create%2A, set one or more of the following fields of the xref:Microsoft.SqlServer.Replication.PullSubscription.AgentSchedule%2A property:

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyType%2A - the type of frequency (such as daily or weekly) that you use when you schedule the agent.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyInterval%2A - the day of the week that an agent runs.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyRelativeInterval%2A - the week of a given month that the agent is scheduled to run monthly.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyRecurrenceFactor%2A - the number of frequency-type units that occur between synchronizations.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencySubDay%2A - the frequency unit when the agent runs more often than once a day.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencySubDayInterval%2A - the number of frequency units between runs when the agent runs more often than once a day.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveStartTime%2A - earliest time on a given day that an agent run starts.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveEndTime%2A - latest time on a given day that an agent run starts.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveStartDate%2A - first day that the agent schedule is in effect.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveEndDate%2A - last day that the agent schedule is in effect.

    [!NOTE]
    If you do not specify one of these properties, a default value is set.

  3. Call the xref:Microsoft.SqlServer.Replication.PullSubscription.Create%2A method to create the subscription.

To define a replication agent schedule when you create a pull subscription to a merge publication

  1. Create an instance of the xref:Microsoft.SqlServer.Replication.MergePullSubscription class for the subscription you are creating. For more information, see Create a Pull Subscription.

  2. Before you call xref:Microsoft.SqlServer.Replication.PullSubscription.Create%2A, set one or more of the following fields of the xref:Microsoft.SqlServer.Replication.PullSubscription.AgentSchedule%2A property:

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyType%2A - the type of frequency (such as daily or weekly) that you use when you schedule the agent.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyInterval%2A - the day of the week that an agent runs.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyRelativeInterval%2A - the week of a given month that the agent is scheduled to run monthly.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyRecurrenceFactor%2A - the number of frequency-type units that occur between synchronizations.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencySubDay%2A - the frequency unit when the agent runs more often than once a day.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencySubDayInterval%2A - the number of frequency units between runs when the agent runs more often than once a day.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveStartTime%2A - earliest time on a given day that an agent run starts.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveEndTime%2A - latest time on a given day that an agent run starts.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveStartDate%2A - first day that the agent schedule is in effect.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveEndDate%2A - last day that the agent schedule is in effect.

    [!NOTE]
    If you do not specify one of these properties, a default value is set.

  3. Call the xref:Microsoft.SqlServer.Replication.PullSubscription.Create%2A method to create the subscription.

To define a replication agent schedule when you create a push subscription to a merge publication

  1. Create an instance of the xref:Microsoft.SqlServer.Replication.MergeSubscription class for the subscription you are creating. For more information, see Create a Push Subscription.

  2. Before you call xref:Microsoft.SqlServer.Replication.Subscription.Create%2A, set one or more of the following fields of the xref:Microsoft.SqlServer.Replication.Subscription.AgentSchedule%2A property:

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyType%2A - the type of frequency (such as daily or weekly) that you use when you schedule the agent.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyInterval%2A - the day of the week that an agent runs.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyRelativeInterval%2A - the week of a given month that the agent is scheduled to run monthly.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencyRecurrenceFactor%2A - the number of frequency-type units that occur between synchronizations.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencySubDay%2A - the frequency unit when the agent runs more often than once a day.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.FrequencySubDayInterval%2A - the number of frequency units between runs when the agent runs more often than once a day.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveStartTime%2A - earliest time on a given day that an agent run starts.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveEndTime%2A - latest time on a given day that an agent run starts.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveStartDate%2A - first day that the agent schedule is in effect.

    • xref:Microsoft.SqlServer.Replication.ReplicationAgentSchedule.ActiveEndDate%2A - last day that the agent schedule is in effect.

    [!NOTE]
    If you do not specify one of these properties, a default value is set.

  3. Call the xref:Microsoft.SqlServer.Replication.Subscription.Create%2A method to create the subscription.

Example (RMO)

This example creates a push subscription to a merge publication and specifies the schedule on which the subscription is synchronized.

[!code-csHowTo#rmo_CreateMergePushSub]

[!code-vbHowTo#rmo_vb_CreateMergePushSub]

See Also

Replication Security Best Practices
Subscribe to Publications
Synchronize a Push Subscription
Synchronize a Pull Subscription
Synchronize Data