“The subscription does not exist” when a distributor primary replica fails over to a replica that do...

最新推荐文章于 2021-10-27 18:05:49 发布

转载最新推荐文章于 2021-10-27 18:05:49 发布 · 640 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/stswordman/p/10357111.html

文章标签：

#数据库

当SQLServer2017中使用非默认代理配置的分发代理，在可用性组内发生主副本切换时，若目标副本代理配置不同，将导致订阅不存在错误。此问题源于代理配置在msdb中管理，无法在分发数据库可用性组间同步，代理启动时通过存储过程查找配置失败。

Symptoms

Consider the following scenario:

In Microsoft SQL Server 2017, you have a distribution agent that does not use a default agent profile.
The distribution database is added to the availability group (AG).
The primary replica of the distribution database fails over to a replica that does not use the exact same agent profile.

In this scenario, the distribution agent fails, and you receive the following error message :

The subscription does not exist.

Cause

Agent profiles are managed and persisted in the msdb database. Changes to an agent profile are persisted in msdb and cannot be sent to other distributors in the distribution database AG.

Replication agents are associated with profile through profile_id. After a failover, the agent might be unable to find the correct profile. Alternatively, it might find the wrong profile. This is because a nondefault profile in one distributor could differ from that of another distributor, or it may never have existed, or it may have a different profile_id.

The distribution agent job issues the sp_MShelp_distribution_agentid stored procedure to get the agent ID when it starts. If the profile does not exist, or if the profile IDs are different, the distribution agent job does not get the agent ID, and it returns the "subscription does not exist" error message.

Workaround

To work around this issue, use one of the following methods.

Method 1 Specify the parameters in the distribution agent command directly, instead of using the agent profile. Also, apply the changes to the distribution agent job in all Distributor replicas.

Method 2 Make sure that the agent profile is created in all Distributors that are participating in the distribution database in the AG, and make sure that the profile IDs are the same.
The distribution agent runs the sp_MShelp_distribution_agentid stored procedure to get the agent profile. If the agent profile does not exist, or if the profile ID is different, the correct agent profile is not found, and the “The subscription does not exist” error message is returned.

To prevent this issue, specify the correct agent profile (profile ID) in the sp_MShelp_distribution_agentid stored procedure.

The following is a code segment of the sp_MShelp_distribution_agentid stored procedure:

select distribAgent.id , distribAgent.name , distribAgent.publisher_database_id , distribAgent.publisher_db , distribAgent.subscriber_db , profileName.profile_id , profileName.profile_name from MSdistribution_agents as distribAgent INNER JOIN msdb..MSagent_profiles as profileName on distribAgent.profile_id = profileName.profile_id

https://support.microsoft.com/en-us/help/4488815/subscription-does-not-exist-when-distributor-primary-replica-fails

This is a design issue, may be addressed in further version....

转载于:https://www.cnblogs.com/stswordman/p/10357111.html