Post reply

Cannot get Read connections to Read-Intent only Secondaries

  • November 10, 2021 at 9:44 am

    #3949828

    Hi all

    Thanks last week first of all for your assistance in getting a quick setup for a third replica in our Always On system 🙂

    Problem I have now is baffling me. We have the Primary and one Secondary on subnet A, the (new, remote) Secondary on subnet B. They all are set to Read-Intent only when they are Secondaries but when application users connect using the relevant string no matter what they always connect to the Primary and not one of the Secondaries.

    I am told this wasn't the case a few weeks ago so looked into any SQL patching or config changes but nothing correlates with the issue first appearing. The SQL Listener has the IP address of the current Primary as well as the new Secondary, the URLs and routing list for each node all look correct (happy to post abstracted screen shots on request).

    I thought I was quite good on things Always On, maybe am missing something obvious but your advice and assistance would be much appreciated; for now the Dev DBA has added a workaround to the application that does the Reporting (checks for Primary and manually redirects to one of the other nodes) but this is only a short-term fix.

    Thanks in advance!

    Ali

  • November 11, 2021 at 10:10 am

    #3951962

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • November 12, 2021 at 12:13 pm

    #3952353

    Hi,

    have you changed the connection string at your application?

    I think, this would only work, if you aoag is ready for read only, and if the connection string got the following information:

    ApplicationIntent=ReadOnly

    Kind regards, and good luck,

    Andreas

     

  • November 12, 2021 at 12:42 pm

    #3952362

    Hi Andreas

    Thanks, the apps do have ApplicationIntent=ReadOnly in their connection strings and both Secondaries are set to Read-Intent Only. I also have the entry in SSMS when I test the connection either locally or remotely, same result sadly.

    One oddity though is when I run sqlcmd on the server console with the -K parameter and specify ReadOnly then type SELECT @@SERVERNAME it does comes back with the first Secondary!

    I've been through the Read Only Routing List, recycled the Secondaries, switched back to non-Readable then Read Intent Only for them, read through postings online for setting up / troubleshooting Read Only connectivity to AOAGs but sadly nothing 'looks' wrong yet it doesn't work 🙁

    Cheers

    Ali

  • November 25, 2021 at 2:47 am

    This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply