1

I'm designing an API (a WCF nettcp intranet webservice, not a public web api) that will target "AlwaysOn Availability Groups" database. The AlwaysOn is set in Synchronous-commit mode. It was suggested to me that I should use secondary replica for performance purposes. There is a way to target read-only db directly (client's connection string must specify the application intent as "read-only.") to offload workloads from your primary replica. So I was thinking... is there really a design pattern handle this situation?

We're talking about two connection strings (read-write and read-only ) for theoretically one database. Simple rule is: API should targtet primary for writes and secondary for reads, but life is not that simple.

For example: If a client calls DeleteItem and immediately after GetAllItems, even in sync-mode, it might get all items including the deleted one because the read-only db didn't have enough time to update (sometimes data is not yet available for read, we're talking about milliseconds. I tested it). Because API is the one who decides which connection string to use, it will use read-write for DeleteItem operation, and then read-only for GetAllItems operation.

So my question is how should I handle active secondary database?

  1. Should I let API to decide whether to use read-only db to offload worload?

  2. Should API's clietns have an option to decide explicitly? Like, "Hi API, I want all items, but use read-only connection string!"?

  3. Is there a desing pattern, how to handle this situation?

Thanks for all the help,

Ish Thomas
  • 2,270
  • 2
  • 27
  • 57
  • These 2 calls, are they coming from the same client and are part of the same request? If not, then it's okay to return previous state of the database. If they are, I somehow doubt it - in High Availability mode, primary doesn't commit the transaction until secondary confirms it has committed it. – Roger Wolf Jun 28 '19 at 16:50
  • @RogerWolf They are coming from the same client. Even in sync mode, the sequence goes like this: Primary receives the update, saves the change in the log in on the secondary machine, returns to client, secondary db update itself from the log. Sometimes I will catch the update, sometimes not. Sometimes secondary update itself, but the update is still not yet available for the read, and therefore I get the deleted item. It's easy to reproduce actually. – Ish Thomas Jun 28 '19 at 17:16

0 Answers0