Επεξεργασία

Κοινή χρήση μέσω

Specify Synchronization Schedules

  • Άρθρο

Applies to: SQL Server Azure SQL Managed Instance

This topic describes how to specify synchronization schedules in SQL Server by using SQL Server Management Studio, Transact-SQL, 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 SQL Server Management Studio 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 Management Studio, 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 Management Studio, 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 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 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 TransSubscription class for the subscription you are creating. For more information, see Create a Push Subscription.

  2. Before you call Create, set one or more of the following fields of the AgentSchedule property:

    Note

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

  3. Call the Create 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 TransPullSubscription class for the subscription you are creating. For more information, see Create a Pull Subscription.

  2. Before you call Create, set one or more of the following fields of the AgentSchedule property:

    Note

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

  3. Call the Create 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 MergePullSubscription class for the subscription you are creating. For more information, see Create a Pull Subscription.

  2. Before you call Create, set one or more of the following fields of the AgentSchedule property:

    Note

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

  3. Call the Create 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 MergeSubscription class for the subscription you are creating. For more information, see Create a Push Subscription.

  2. Before you call Create, set one or more of the following fields of the AgentSchedule property:

    Note

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

  3. Call the Create 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.

// Define the Publisher, publication, and databases.
string publicationName = "AdvWorksSalesOrdersMerge";
string publisherName = publisherInstance;
string subscriberName = subscriberInstance;
string subscriptionDbName = "AdventureWorks2022Replica";
string publicationDbName = "AdventureWorks2022";
string hostname = @"adventure-works\garrett1";

//Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

// Create the objects that we need.
MergePublication publication;
MergeSubscription subscription;

try
{
    // Connect to the Publisher.
    conn.Connect();

    // Ensure that the publication exists and that 
    // it supports push subscriptions.
    publication = new MergePublication();
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;
    publication.ConnectionContext = conn;

    if (publication.IsExistingObject)
    {
        if ((publication.Attributes & PublicationAttributes.AllowPush) == 0)
        {
            publication.Attributes |= PublicationAttributes.AllowPush;
        }

        // Define the push subscription.
        subscription = new MergeSubscription();
        subscription.ConnectionContext = conn;
        subscription.SubscriberName = subscriberName;
        subscription.PublicationName = publicationName;
        subscription.DatabaseName = publicationDbName;
        subscription.SubscriptionDBName = subscriptionDbName;
        subscription.HostName = hostname;

        // Set a schedule to synchronize the subscription every 2 hours
        // during weekdays from 6am to 10pm.
        subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.Weekly;
        subscription.AgentSchedule.FrequencyInterval = Convert.ToInt32(0x003E);
        subscription.AgentSchedule.FrequencyRecurrenceFactor = 1;
        subscription.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Hour;
        subscription.AgentSchedule.FrequencySubDayInterval = 2;
        subscription.AgentSchedule.ActiveStartDate = 20051108;
        subscription.AgentSchedule.ActiveEndDate = 20071231;
        subscription.AgentSchedule.ActiveStartTime = 060000;
        subscription.AgentSchedule.ActiveEndTime = 100000;

        // Specify the Windows login credentials for the Merge Agent job.
        subscription.SynchronizationAgentProcessSecurity.Login = winLogin;
        subscription.SynchronizationAgentProcessSecurity.Password = winPassword;

        // Create the push subscription.
        subscription.Create();
    }
    else
    {
        // Do something here if the publication does not exist.
        throw new ApplicationException(String.Format(
            "The publication '{0}' does not exist on {1}.",
            publicationName, publisherName));
    }
}
catch (Exception ex)
{
    // Implement the appropriate error handling here.
    throw new ApplicationException(String.Format(
        "The subscription to {0} could not be created.", publicationName), ex);
}
finally
{
    conn.Disconnect();
}

' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2022Replica"
Dim publicationDbName As String = "AdventureWorks2022"
Dim hostname As String = "adventure-works\garrett1"

'Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As MergePublication
Dim subscription As MergeSubscription

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Ensure that the publication exists and that 
    ' it supports push subscriptions.
    publication = New MergePublication()
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName
    publication.ConnectionContext = conn

    If publication.IsExistingObject Then
        If (publication.Attributes And PublicationAttributes.AllowPush) = 0 Then
            publication.Attributes = publication.Attributes _
            Or PublicationAttributes.AllowPush
        End If

        ' Define the push subscription.
        subscription = New MergeSubscription()
        subscription.ConnectionContext = conn
        subscription.SubscriberName = subscriberName
        subscription.PublicationName = publicationName
        subscription.DatabaseName = publicationDbName
        subscription.SubscriptionDBName = subscriptionDbName
        subscription.HostName = hostname

        ' Set a schedule to synchronize the subscription every 2 hours
        ' during weekdays from 6am to 10pm.
        subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.Weekly
        subscription.AgentSchedule.FrequencyInterval = Convert.ToInt32("0x003E", 16)
        subscription.AgentSchedule.FrequencyRecurrenceFactor = 1
        subscription.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Hour
        subscription.AgentSchedule.FrequencySubDayInterval = 2
        subscription.AgentSchedule.ActiveStartDate = 20051108
        subscription.AgentSchedule.ActiveEndDate = 20071231
        subscription.AgentSchedule.ActiveStartTime = 60000
        subscription.AgentSchedule.ActiveEndTime = 100000

        ' Specify the Windows login credentials for the Merge Agent job.
        subscription.SynchronizationAgentProcessSecurity.Login = winLogin
        subscription.SynchronizationAgentProcessSecurity.Password = winPassword

        ' Create the push subscription.
        subscription.Create()
    Else

        ' Do something here if the publication does not exist.
        Throw New ApplicationException(String.Format( _
         "The publication '{0}' does not exist on {1}.", _
         publicationName, publisherName))
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here.
    Throw New ApplicationException(String.Format( _
    "The subscription to {0} could not be created.", publicationName), ex)
Finally
    conn.Disconnect()
End Try

Related content