Share via

Always ON publisher validation failure

Yolanda Herrero Rodríguez 1 Reputation point
2022-01-26T09:59:59.717+00:00

Hello,

I have configure a merge replication enviroment with Always On.

The servers paticipating on Always ON arquitecture are in different subnets, with a standard version.

I have configure a listener with two IP, one for each subnet, also I've redirected the original publisher to the listener's name.

Time to check, I've ran while Always On has it primary on the original publisher: 

USE distribution;  
GO  
DECLARE @redirected_publisher sysname;  
EXEC sys.sp_validate_replica_hosts_as_publishers  
    @original_publisher = 'S01PRIMARY',  
    @publisher_db = 'PUBDB',  
    @redirected_publisher = @redirected_publisher output;  

SELECT @redirected_publisher

I get this result, it seems to be OK, I'm using a standard version, and the secondary is not readable:

OLE DB provider "MSOLEDBSQL" for linked server "[0BFB787C-499C-4E5F-B068-D3495B404F5D]" returned message "Deferred prepare could not be completed.".
Msg 21899, Level 11, State 1, Procedure sys.sp_hadr_verify_subscribers_at_publisher, Line 109 [Batch Start Line 2]
The query at the redirected publisher 'S01SECONDARY' to determine whether there were sysserver entries for the subscribers of the original publisher 'S01PRIMARY' failed with error '976', error message 'Error 976, Level 14, State 1, Message: The target database, 'PruebaJSon', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.
One or more publisher validation errors were encountered for replica host 'S01SECONDARY'.
(1 row affected)
Completion time: 2022-01-26T09:47:26.8948935+00:00

Now, lets failover and check again, I get this result:

OLE DB provider "MSOLEDBSQL" for linked server "[010B97EC-74DF-4F6A-B208-2BF31112D72B]" returned message "Login timeout expired".
OLE DB provider "MSOLEDBSQL" for linked server "[010B97EC-74DF-4F6A-B208-2BF31112D72B]" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 21892, Level 16, State 1, Procedure sys.sp_hadr_validate_replica_hosts_as_publishers, Line 60 [Batch Start Line 2]
Unable to query sys.availability_replicas at the availability group primary associated with virtual network name 'S01LIS00' for the server names of the member replicas: error = 2, error message = Error 2, Level 16, State 1, Message: Named Pipes Provider: Could not open a connection to SQL Server [2]. .',
(1 row affected)
Completion time: 2022-01-26T09:33:16.2000786+00:00

In the replication monitor I get the same result... It seems like if can't access the listener, and, in failover, I can not sincronize my merge replication, and I need my merge replication working while I'm on failover.

Any ideas to make it work?

Thanks!!!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,461 Reputation points
    2022-01-27T08:14:09.637+00:00

    Hi @Yolanda Herrero Rodríguez ,

    Welcome to Microsoft Q&A!

    Either data movement is suspended or the availability replica is not enabled for read access.

    I hope you know about the features supported by different editions of SQL Server.
    See: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15#RDBMSHA
    168972-1.png

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Yolanda Herrero Rodríguez 1 Reputation point
    2022-01-27T10:15:02.457+00:00

    Hello @Seeya Xi-MSFT !

    First of all, many thanks for your help.

    I have a basic avalability group configured with one database with merge replication. I can not see any information about any incompatibility on using a Basic availability group with merge replication.

    When I have my primary database (the publisher) working, everything is fine, merge replication works as always and the database in the secondary can not be read.

    The problem comes when I make a failover, the database in the primary server can not be read, but it works in the secondary.

    I made a redirection in the primary merge publisher, and I expected that the replication configuration may be in order as microsoft says in this article:

    https://learn.microsoft.com/es-es/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-ver15

    But I can not validate the secondary merge publisher, and the merge replication doesn't work when I make failover.

    Best regards.

    0 comments