Chris Skorlinski
Microsoft SQL Server Escalation Services

We’re seeing an uptick in customers using combination of AlwaysOn and Transactional Replication. Some are configuring the Publisher for high-availability, and some are configuring the Subscriber with Read-Only Secondary to prevent contention from the Distribution Agent updates and “reporting” read queries.

Once configured, Tracer Tokens are often used to Monitor end-to-end Transactional Replication Latency. Tracer tokens can be inserted on the Publisher via Replication Monitor or on the published database via stored procedures.

In Replication Monitor tool, connect to the original publisher, not the AlwaysOn Listener. In example below, built using AlwaysOn Azure template, SQLSERVER-0 is server on which Replication was configured. The publication appears under SQLSERVER-0 independent of which AlwaysOn Replica is Primary.

When a failover occurs, the Replication Agents lose connections and go into “retrying” mode. Once new Primary is fully recovered, then Agents, configured for retry, will resume activity automatically.

Okay, so let me get to the point of this posting. If a failover has occurred and Published database is now running on a node which was not Primary when Replication was setup, i.e. now on Secondary as Primary, inserting a Tracer Token via Replication Monitor will generate the following error:

Replication Monitor could not insert the tracer token.
Failed to update database “AdventureWorks2014” because the database is read-only. (Microsoft SQL Server, Error: 3906)

You can still insert Tracer Token via stored procedures on the current Primary then show latency in Replication Monitor, you just can’t insert new Token via Replication Monitor after failover to secondary replica.

https://blogs.msdn.microsoft.com/repltalk/2010/03/11/divide-and-conquer-transactional-replication-using-tracer-tokens/

–Insert new token on Primary Replica in the Published database

EXEC
sys.sp_posttracertoken
@publication =
‘’

Go

–View Tracer Token History from Distributor in the Distribution database

SELECT
Top 20 tt.tracer_id, tt.publication_id, tt.publisher_commit, tt.distributor_commit, th.agent_id, th.subscriber_commit

FROM MStracer_tokens tt

JOIN MStracer_history th ON tt.tracer_id = th.parent_tracer_id

Order
by tt.publisher_commit desc