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,
But after a failover when I tried the same tsql code in the switched publisher database I received an error,
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. sp_adddistributor which creates an entry in linked server and executed at a publisher in the master database to configure remote distributor,
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 publisherdbError
GO
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.Solution
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,
- Configure distributor at publisher.
- Configure publisher at distributor.
1. sp_adddistributor which creates an entry in linked server and executed at a publisher in the master database to configure remote distributor,
Use master2. sp_adddistpublisher configures a publisher in distributor server, which executed at the distributor side in the master database,
GO
EXEC sp_adddistributor
@distributor= '<Distributor>' , -- Put your distributor server name here
@password= 'testpwd' -- password of distributor_admin
USE masterAfter 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.
GO
EXEC sp_adddistpublisher
@publisher= '<Publisher>' -- Put publisher servername here
,@distribution_db= 'distribution' -- Distribution database name
,@security_mode= 1
,@login= 'sa'
,@password= 'testpwd'
No comments:
Post a Comment
Please Use Good Leanguage