How to: Specify Synchronization Schedules (RMO Programming)

  • Makale

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 How to: Create a Pull Subscription (RMO Programming) and How to: Create a Push Subscription (RMO Programming).

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 How to: Create a Push Subscription (RMO Programming).

  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 How to: Create a Pull Subscription (RMO Programming).

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

    • FrequencyType - the type of frequency (such as daily or weekly) that you use when you schedule the agent.
    • FrequencyInterval - the day of the week that an agent runs.
    • FrequencyRelativeInterval - the week of a given month that the agent is scheduled to run monthly.
    • FrequencyRecurrenceFactor - the number of frequency-type units that occur between synchronizations.
    • FrequencySubDay - the frequency unit when the agent runs more often than once a day.
    • FrequencySubDayInterval - the number of frequency units between runs when the agent runs more often than once a day.
    • ActiveStartTime - earliest time on a given day that an agent run starts.
    • ActiveEndTime - latest time on a given day that an agent run starts.
    • ActiveStartDate - first day that the agent schedule is in effect.
    • ActiveEndDate - 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 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 How to: Create a Pull Subscription (RMO Programming).

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

    • FrequencyType - the type of frequency (such as daily or weekly) that you use when you schedule the agent.
    • FrequencyInterval - the day of the week that an agent runs.
    • FrequencyRelativeInterval - the week of a given month that the agent is scheduled to run monthly.
    • FrequencyRecurrenceFactor - the number of frequency-type units that occur between synchronizations.
    • FrequencySubDay - the frequency unit when the agent runs more often than once a day.
    • FrequencySubDayInterval - the number of frequency units between runs when the agent runs more often than once a day.
    • ActiveStartTime - earliest time on a given day that an agent run starts.
    • ActiveEndTime - latest time on a given day that an agent run starts.
    • ActiveStartDate - first day that the agent schedule is in effect.
    • ActiveEndDate - 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 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 How to: Create a Push Subscription (RMO Programming).

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

    • FrequencyType - the type of frequency (such as daily or weekly) that you use when you schedule the agent.
    • FrequencyInterval - the day of the week that an agent runs.
    • FrequencyRelativeInterval - the week of a given month that the agent is scheduled to run monthly.
    • FrequencyRecurrenceFactor - the number of frequency-type units that occur between synchronizations.
    • FrequencySubDay - the frequency unit when the agent runs more often than once a day.
    • FrequencySubDayInterval - the number of frequency units between runs when the agent runs more often than once a day.
    • ActiveStartTime - earliest time on a given day that an agent run starts.
    • ActiveEndTime - latest time on a given day that an agent run starts.
    • ActiveStartDate - first day that the agent schedule is in effect.
    • ActiveEndDate - 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 Create method to create the subscription.

Example

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 = "AdventureWorksReplica";
string publicationDbName = "AdventureWorks";
string hostname = @"adventure-works\garrett1";

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

// 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 = "AdventureWorksReplica"
Dim publicationDbName As String = "AdventureWorks"
Dim hostname As String = "adventure-works\garrett1"

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

' 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

See Also

Tasks

How to: Create a Publication (RMO Programming)
How to: Create a Pull Subscription (RMO Programming)
How to: Create a Push Subscription (RMO Programming)
How to: Specify Synchronization Schedules (Replication Transact-SQL Programming)

Other Resources

Subscribing to Publications

Help and Information

Getting SQL Server 2005 Assistance