The following represents a list of some of the more common issues you can encounter when installing and using the replication feature of SQL Server.
A table does not appear in the list of possible articles when creating a publication in SQL Enterprise Manager.
Any table that will be published must have a PRIMARY KEY constraint defined using the CREATE TABLE or ALTER TABLE statement. A constraint can also be created using SQL Enterprise Manager.
Creating a unique index will not allow the table to appear in the list of possible articles. Users who have upgraded their database from prior versions of SQL Server usually do not have PRIMARY KEY constraints, but rather have used unique indexes because constraints were not supported until SQL Server version 6.0. Use ALTER TABLE to add constraints as appropriate.
LogReader and Distribution tasks do not show any entries in the Task History of SQL Enterprise Manager after setting up replication.
By default, LogReader and Distribution subsystem tasks are created with an "Auto Start" frequency. When setting up subscription servers, you can change the default for the Distribution task to a different frequency. This means the tasks will start when they are first created as tasks or any time SQL Executive is started. They run continuously in a polling "loop," pausing for each loop n milliseconds as defined by the "polling interval" set with the -i parameter. The tasks are set up with a default of one second for LogReader and two seconds for Distribution. Under this type of task scheduling, these two tasks will not display any task history, provided there are no errors, because the tasks never quit. Any error or retry attempt will cause an entry to be displayed in the Task History. If SQL Executive is shut down, these tasks will have an entry in the Task History with a message of:
This task was shutdown.
This message indicates the task ended as part of the shutdown to SQL Executive.
If a LogReader or Distribution task is scheduled to run "On Demand" or "Recurring," then it will process jobs until no more jobs are available, display information about its processing to the Task History, and then quit. For example, a LogReader task will display the following message:
Processed <n> replicated transactions consisting of <m> commands from '<published db>.'
One method that can be used to examine a more detailed view of the processing of a LogReader, Sync, or Distribution task is to add the -o parameter to each of these tasks.
-ofile_name
Do not use a space between -o and file_name or the file will not be created.
This parameter will force the task to dump details of replication processing. The parameter can be added by editing any of the tasks using SQL Enterprise Manager. Remember that if the task is set to "Auto Start," the parameter change will not take effect until SQL Executive is restarted. This parameter should only be used for troubleshooting situations as the amount of output written to the file can be substantial and could cause a degradation in performance. The -o parameter is valid for any LogReader, Distribution, and Sync task. These tasks open the file to "append" entries if the file exists and to share it so a user can view the file while the task is still executing. Remember that this parameter is only for troubleshooting purposes and only typically used when working with a support engineer. The task will not display any errors if the file cannot be created for any reason—for example, if the syntax is incorrect or the path does not exist.