-1

We have configured SQL Server - Always On Availability group with SQL SERVER 2022 Enterprise Edition. where there is 2 instance which are also configured with Readable secondary 'Yes'. According to my little knowledge all the read queries should return data from my secondary instances, but it didn't happen.
I have attached my configuration

Read-only routing summary

I want to active the readable secondary replica.

Improve this question
1
  • Can you show the connection string your application is using?
    – Zikato
    Commented Apr 29 at 7:28

1 Answer 1

Reset to default
4

[...] all the read queries should return data from my secondary instances, but it didn't happen.

Readable secondary replicas can be read by either 1) directly connecting to them and running the queries, or 2) utilizing read only routing (ROR) to have new connections that meet the ROR criteria be transparently redirected to the secondary readable replica(s).

In your case it seems as you want #2. In order for that to occur, you'll need to satisfy the ROR criteria for clients which requires the use of:

  • TCP/IP
  • Listener used as server name
  • ApplicationIntent set to ReadOnly
  • Initial Database set to a database in the AG referenced by the Listener

It seems you have the requirements met from the server side, after properly setting the client configuration, read only routing should work.

Improve this answer
2
  • I have done all the settings but result same. its routed to primary for read
    – user3024056
    Commented Apr 29 at 3:56
  • @user3024056 what driver is being used? If everything is setup properly, it should work.
    – Sean Gallardy
    Commented Apr 29 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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