0

I have a two node SQL Server 2016 Multi subnet Availability group. I am trying to setup transnational replication from the AG to a standalone instance for reporting, which is also acting as a remote distributor. We only need a small subset of the tables so adding the standalone instance as a third replica isn't an option.

I've followed the Microsoft Guide for configuring this. I've run the EXEC sys.sp_redirect_publisher and when I run EXEC sys.sp_validate_replica_hosts_as_publishers it returns the AG listener. But when we failover the AG the replication stops working with the following error reporting in replication monitor

Error messages: Unable to query the redirected server 'AGL' for original publisher 'PUB1' and publisher database 'AlwaysOnTest' to determine the name of the remote server; Error 7303, Error message 'Error 7303, Level 16, State 1, Message: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "[B66E8E27-E422-41DB-BBBC-254D450468D7]".'. (Source: MSSQL_REPL, Error number: MSSQL_REPL21879) Get help: http://help/MSSQL_REPL21879 Errors were logged when validating the redirected publisher. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) Get help: http://help/MSSQL_REPL22037

Couple of things I've checked and tried

  1. Checked linked servers for the subscriber exist on all secondary replica hosts.
  2. Switched the security mode to SQL auth using sp_changedistpublisher based on this.

When the AG is failed over if I re-run on the distributor EXEC sys.sp_validate_replica_hosts_as_publishers it returns

Unable to query sys.availability_replicas at the availability group primary associated with virtual network name 'AGL' for the server names of the member replicas: error = 7303, error message = Error 7303, Level 16, State 1, Message: Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "[7A7DA613-BFDA-4C51-B62C-9C7CD94E769E]"..',

I am stuck what to try next....help please!!!

UPDATE 16/06/2020

When the AG is failed over I tried connecting to the AG listener from the remote distributor. SSMS connected fine, but when I tried connecting with SQLCMD it returned an error. Also I can connect to the failover instance directly from the distributor, bypassing the listener.

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server 2. . Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : 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 i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

7
  • 1
    From your remote distributor after a failover can you connect using SSMS or SQLCMD to the Availability Group Listener? Jun 16 '20 at 15:57
  • @DavidBrowne-Microsoft SSMS connected fine, but SQLCMD failed with an error. I've updated the question with the error.
    – Adrian S
    Jun 16 '20 at 19:07
  • 1
    Next step run nslookup for the AGL and test-netconnection for each of the returned IP addresses on the target port. Jun 16 '20 at 19:10
  • We'e getting somewhere, test-netconnection failed for the IP of the failover node, which is on a different subnet. The AG is running on the default port (1433) at the moment.
    – Adrian S
    Jun 16 '20 at 19:19
  • 1
    test-netconnection should succeed only for the IP address belonging to the current primary replica. And which one succeeds should flip after failover. Jun 16 '20 at 19:24
0

The root cause of this was connection timeouts because the availability group was in a Multi-subnet Availability Group. This was resolved by following this document from Microsoft.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.