Skip to main content

Could not find the Distributor or the distribution database for the local server - Error while posts a tracer token in Replication

A few days back, I spoke about the manual failover of mirroring and also explained one issue and workaround too. Continuing with the same failover, I want to express one more issue here. This issue is not very critical but it somehow to create an issue while collecting some information for report or any other purpose. Let me elaborate everything here, why and how this error raised.
You all know about system stored procedure sys.sp_posttracertoken which posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics, which we can schedule on some frequency to post tracer tokens. Tracer tokens can be inserted with Replication monitor also,

You can find the tsql code for same below which must be run against the publisher database,
USE publisherdb 

DECLARE @out_tracer_token_id INT 

EXEC sys.Sp_posttracertoken 
  @publication = N'<Publication Name>',  -- Put Publication name here
  @tracer_token_id=@out_tracer_token_id out 

SELECT @out_tracer_token_id 
But after a failover when I tried the same tsql code in the switched publisher database I received an error,
"Could not find the Distributor or the distribution database for the local server.
The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor."
This script was running fine in the original publisher database before failover. During the investigation as per error message I found sp_helpdistributor was returning NULL values in publisher database. sp_helpdistpublisher also not showing publisher in Distributor server or a server where distribution database belongs to. That means we have to do two things,
  1. Configure distributor at publisher.
  2. Configure publisher at distributor.
So moving ahead towards the solution and apply below solution,
1. sp_adddistributor which creates an entry in linked server and executed at a publisher in the master database to configure remote distributor,
Use master

EXEC sp_adddistributor  
           @distributor=  '<Distributor>' , -- Put your distributor server name here
           @password= 'testpwd' -- password of distributor_admin 
2. sp_adddistpublisher configures a publisher in distributor server, which executed at the distributor side in the master database,
USE master

EXEC sp_adddistpublisher 
  @publisher= '<Publisher>'  -- Put publisher servername here
 ,@distribution_db= 'distribution'  -- Distribution database name
 ,@security_mode= 1 
 ,@login= 'sa' 
 ,@password= 'testpwd' 
After this workaround I was able to ran this script successfully at the publisher and scheduled for  every 5 minutes, so I can use it for replication latency alert and reports too.  Are you using sys.Sp_posttracertoken system stored procedure? Share your feedback here.