“Replication Monitor could not insert the tracer token”

I’m 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 via Replication Monitor or on the published database via stored procedures.

Replication Monitor

After installing SQL Server Management Studio 17.9 and latest CU for SQL 2016\17, run SQL Replication Monitor tool and connect to the AlwaysOn Listener.   In example below, built using AlwaysOn Azure template, SQLSERVER-0 is where publication was created. The publication appears under SQLSERVER-0, where it was created, independent of which AlwaysOn Replica is Primary.   SQLSERVER-1, the secondary Replica will never show the publication, even after failure.  No problem, simply connect to the Availability Group Listener in Replication Monitor.

When a failover occurs, the Replication Agents lose connections and “retrying”. Once new Primary is fully recovered, then Agents, will retry, and reconnect, resuming activity automatically.

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 on SQLSERVER-0, now secondary, will generate the following error:

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

Remember, simple solution, upgrade SSMS and Replication Distributor to latest builds then connect Replication Monitor to the Availability Group Listener and not individual replicas.

Via Stored Procedure

As an alternative, insert Tracer Token via stored procedures on the active Primary then view Tracer Token in Replication Monitor or directly in Distributor tracking tables.

--Insert new token on Primary Replica in the Published database

EXEC
sys.sp_posttracertoken
@publication ='your publication name'
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

Chris Skorlinski
Microsoft SQL Server Escalation Services