Replication Task Scheduling
In most cases, replication is scheduled through the replication administration interface provided by SQL Enterprise Manager, and you will not need to use SQL Executive to set up scheduled replication tasks.
However, it is possible to use SQL Executive to create replication tasks or modify already-scheduled replication tasks. You might typically:
-
Change the schedule for a replication task.
Once you have set up replication, you can use SQL Executive to fine-tune replication process execution intervals for best performance. For example, by default the log reader runs continuously. You could use SQL Executive to change this to every five minutes. Or you may have configured replication so that a sync task occurs once a week. You could use SQL Executive to change that interval.
-
Change what happens in the event of an error.
For example, you can enter the email name of a user to be notified if a scheduled replication task fails to execute.
-
Modify the command for a replication task.
Command parameters are discussed in the text that follows.
-
Disable a replication task.
For example, to temporarily stop replicating data from a publication server, you could disable the log reader task for that server.
Another reason to use SQL Executive is if you choose to set up replication manually, using replication stored procedures instead of SQL Enterprise Manager. You may find it convenient to schedule replication tasks using SQL Executive, instead of using the scheduling stored procedures.
There are three types of replication tasks that can be scheduled:
-
LogReader tasks schedule replication log reader process tasks.
-
Distribution tasks schedule replication distribution process tasks.
-
Sync tasks schedule replication synchronization process tasks.
When administering a LogReader task, the following optional commands can be entered in the Command box of the New Task or Edit Task dialog boxes:
-
-a packetsize
-
Where packetsize is the network packet size used for connections. The default is 4096.
-
-b transbatchsize
-
Where transbatchsize is the size of the transaction batch. This determines the maximum number of transactions that are read out of the log in each attempt. The default is 100.
-
-c commitbatchsize
-
Where commitbatchsize is the size of the commit batch. This determines how many transactions are placed in the distribution database before a commit can be issued against the distribution database. The default is the -b value (100).
-
-i pollinginterval
-
Where pollinginterval is the size of the polling interval, in milliseconds, when log reader runs as a continuous task.
-
-l logintimeout
-
Where logintimeout is the login timeout, in seconds. The default is 5 seconds.
-
-o filename
-
Where filename is the name of an optional output file. This outputs some debug information into the specified file as the log reader runs.
-
-p performancecounters
-
Where performancecounters is either 1 or 0. Providing 1 enables performance counter information to be maintained by the LogReader; providing 0 stops maintenance of that information. The default value is 1.
-
-q querytimeout
-
Where querytimeout is the query timeout, in seconds. The default is 30 seconds.
When administering a Distribution task, the following optional commands can be entered in the Command box of the New Task or Edit Task dialog boxes:
-
-a packetsize
-
Where packetsize is the network packet size used for connections. The default is 4096.
-
-b jobbatchsize
-
Where jobbatchsize is the maximum number of jobs to be read from the distribution database. The default is 0, which indicates all jobs in the database.
-
-B commandbuffers
-
Where commandbuffers is the number of 4K command buffers to use in the distribution buffer pool. The default number of buffers is 2.
-
-c commitbatchsize
-
Where commitbatchsize is the number of jobs to be issued to the subscription server before performing a commit on the subscription server. The default is 100.
-
-d subscriberdb
-
Where subscriberdb is the name of the subscription database.
-
-i pollinginterval
-
Where pollinginterval is the polling interval, in milliseconds. The polling interval is in effect when the distribution process is set for continuous.
-
-l logintimeout
-
Where logintimeout is the login timeout, in seconds. The default is 5 seconds.
-
-m missingjobsfailure
-
Where missingjobsfailure is 0 or 1. If 1, a missing job will raise an error with a severity of 20 (fatal). If 0, a missing job will raise an error with a severity of 10 (informational). The default is 0.
When you recover a subscriber there may be a case when the distribution database does not have the jobs to properly re-create transactions. This could happen if jobs between the subscriber's last job_id (in MSlast_job_info) and the distributor's last job_id (in MSsubscriber_status) are no longer in the distribution database (which can occur if sp_replcleanup was run and the retention period was inadequate).
-
-n publisherdb
-
Where publisherdb is the name of the publication database.
-
-o filename
-
Where filename is the name of an optional output file. This outputs some debug information into the specified file as the distribution process runs. It will output to the screen when a filename is not supplied.
-
-p publishername
-
Where publishername is the name of the publication server.
-
-P subpassword
-
Where subpassword is the password used for the subscription server login ID.
-
-q querytimeout
-
Where querytimeout is the query timeout, in seconds. The default is 30 seconds.
-
-s initscript
-
Where initscript is the filename of an initialization script that is run against the subscription database if the distribution process has never been run against that database before. The default is the DBREPL.SQL script, which configures the database as a subscription database and adds the MSlastjob_info table.
-
-S subscribername
-
Where name is the name of the subscription server.
-
-t subscribertype
-
Where subscribertype is 0 for SQL Server and 1 for an other ODBC source.
-
-U subloginid
-
Where subloginid is the login ID used when the process logs into the subscription server. The default is REPL_PUBLISHER.
When administering a Sync task, the following optional commands can be entered in the Command box of the New Task or Edit Task dialog boxes:
-
-a packetsize
-
Where packetsize is the network packet size used for connections. The default is 4096.
-
-l logintimeout
-
Where logintimeout is the login timeout, in seconds. The default is 5 seconds.
-
-o filename
-
Where filename is the name of an optional output file. This outputs some debug information into the specified file as the synchronization process runs.
-
-q querytimeout
-
Where querytimeout is the query timeout, in seconds. The default is 30 seconds.
-
-t pubtaskid
-
Where pubtaskid is the publication task ID (the ID of the synchronization task associated with the publication).
For information about replication, see Part 6, Replication. For information about using SQL Executive to create, modify, or disable a replication task, see Creating a Task, Modifying a Task, and Disabling a Task.