Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

“Replication Monitor could not insert the tracer token”

Discussion in 'Official Microsoft News' started by Chris Skorlinski [MSFT], Mar 29, 2017.

Thread Status:
Not open for further replies.
  1. Chris Skorlinski [MSFT]

    Chris Skorlinski [MSFT] Guest

    Blog Posts:
    0
    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.

    [​IMG]

    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.

    [​IMG]

    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/re...ransactional-replication-using-tracer-tokens/

    –Insert new token on Primary Replica in the Published database

    EXEC
    sys.sp_posttracertoken
    @publication =
    ‘<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


    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software