Share via

Unable to connect to Primary replica using AG listener name

Abimbola Adeniran 46 Reputation points
2023-11-16T18:23:51.6066667+00:00

Hi here,

I have an azure VM 2 node WSFC cluster with SQL Server 2016 standard edition.

I setup a basic AG on named instance with a listener with floating IP. Based on the microsoft documentation (https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial-single-subnet?view=azuresql), connection test to the listener via sqlcmd (specifying port for named instance) works as it should but I am unable to connect to using the listener name to the instance on the secondary replica.

I am unsure of what the problem might be as I should be able to connect to the primary replica using the listener name on the secondary server via tools like SSMS.

As suggested by Q&A Assist, I provided additional connection parameters "MultiSubnetFailover=True" but failed as well with the error below:

TITLE: Connect to Server

Cannot connect to <fully qualified listener object name>.

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The wait operation timed out.) (Microsoft SQL Server, Error: 258)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=258&LinkId=20476

The wait operation timed out

BUTTONS:

OK

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,367 questions
Windows Server Clustering
Windows Server Clustering
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.Clustering: The grouping of multiple servers in a way that allows them to appear to be a single unit to client computers on a network. Clustering is a means of increasing network capacity, providing live backup in case one of the servers fails, and improving data security.
980 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2023-11-16T18:23:51.69+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    Based on the Microsoft documentation provided, to connect to the primary replica using the listener name on the secondary server via tools like SSMS, you need to specify MultiSubnetFailover=True in the Additional Connection Parameters window. This provides faster detection of and connection to the current primary replica.

    Here are the steps to connect to the primary replica using the listener name on the secondary server via SSMS:

    1. Use RDP to connect to a SQL Server VM that's in the same virtual network but doesn't own the replica, such as the other replica.
    2. Open SQL Server Management Studio, and in the Connect to Server dialog box type the name of the listener in Server name:, and then select Options.
    3. Enter MultiSubnetFailover=True in the Additional Connection Parameters window and then choose Connect to automatically connect to whichever instance is hosting the primary SQL Server replica.

    References:

    0 comments
  2. AniyaTang-MSFT 12,421 Reputation points Microsoft Vendor
    2023-11-17T05:22:08.7666667+00:00

    Hi @Abimbola Adeniran

    I found a similar thread for you: https://forums.sqlteam.com/t/aoag-on-azure-cant-reach-listener/7145, maybe you can refer to the discussion there.

    If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

    Best regards,

    Aniya

    0 comments