Cannot get Read connections to Read-Intent only Secondaries

  • 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

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

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

  • 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

     

  • 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

  • This was removed by the editor as SPAM

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

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