Troubleshooting replication issues between Active Roles replication partners (4338786)

If an issue occurs during the replication operation, the Active Roles Console will usually display an error message on the Replication Status tab in the Properties dialog box for the Publisher or Subscriber(s). In this scenario, use the SQL Server Management Studio to examine the problem in detail. Consult with a DBA, if one is available. Active Roles Replication is really SQL Server Merge Replication. If there are SQL connection issues, the Replication within Active Roles will not function as expected.

Active Roles pushes the initial configuration to SQL, and then is hands-off. If issues occur past the initial configuration, then the issue lies within SQL, and it is best to leverage Microsoft resources for specific troubleshooting. From an Active Roles perspective, it is possible only to work in broad strokes: generally, break replication and re-establish it to see if that resolves the issue. The limited steps below may also be useful.

The following summarizes some typical problems with ARS replication that may occur:

Issue: Replication does not work, that is, changes made on a replication partner do not propagate to other replication partners. Replication Monitor in SQL Server Enterprise Manager does not indicate any error.

Possible reasons:

1. The SQL Server Agent service is not started on the Publisher SQL Server. Check the status of the SQL Server Agent. If necessary, restart the service.

2. The Snapshot Agent or a Merge Agent is not started at the Publisher SQL Server. Use Replication Monitor to check the status of the Snapshot Agent and Merge Agents. If an agent is stopped, right-click it, and then click Start Agent. To display a list of agents, expand Agents under Replication Monitor in the console tree and click Snapshot Agents or Merge Agents.

3. The replication schedule is incorrect: the Merge Agents must be configured to continuously run on the Publisher; the Snapshot Agent must be configured to start daily at 12:00 AM on the Publisher.

Issue: Replication Monitor in SQL Server Enterprise Manager displays one of these errors in the Error Details of the Merge Agent or Snapshot Agent Error Details window:

- Process cannot connect to Subscriber <Name>
- Process cannot connect to Publisher <Name>
- Login failed for user <user ID>

Possible reasons:

1. The Merge Agent uses incorrect credentials when connecting to the Publisher or a Subscriber. Ensure that the Replication Agents use the appropriate credentials when connecting to the Publisher and Subscribers.

2. The Snapshot Agent uses incorrect credentials when connecting to the Publisher. Ensure that the Replication Agents use the appropriate credentials when connecting to the Publisher.

Issue: Replication Monitor in SQL Server Enterprise Manager displays the following error:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Generate a new snapshot by opening replication monitor and right clicking on the troubled subscription and select Generate Snapshot.