1

We have 2 sql server replicas in Always On cluster and when we tested for performance issues we saw that only the primary is working (read and write).

the primary is set to read-write and the secondary is set to read only intent, we want to send the simple select queries to the secondary replica, while the write queries will be sent to the primary.

is there a way to configure that without making 2 different services with 2 different connection strings for the 2 cases (first is read-write, the second is read-only).

neer
  • 4,031
  • 6
  • 20
  • 34
sergey efimov
  • 11
  • 2
  • 2
    No, there isn't, and that's not typically what you want either. The reason is that mixing replicas that way can lead to [inconsistent results](http://stackoverflow.com/questions/39573960/dirty-reads-in-sql-server-alwayson). Even if you really wanted this, the engine can't detect what query is a "write query" before it goes to the server, so your service would still have to make the distinction. You could do it with one service and one connection string that you modify, but you'd still need two connections. – Jeroen Mostert Sep 28 '16 at 12:02

1 Answers1

1

No, the distinction between read-only and read-write for an Availability Group happens at the connection level. To make the connection go to a Read-Only replica use ApplicationIntent=ReadOnly in the connection string.

IanG
  • 111
  • 2