Re-Use Secondary Connection via Read Only Routing

• alex.sqldba

  SSChampion

  Points: 10254

  More actions

  February 25, 2022 at 10:09 am

  #3988741

  Morning Guys,

  I have a situation where we have a report/connection that connects to the Listener with applicationintent=readonly. We have read only routing configured accross 3 aysnc replicas - so a read only connection can be routed to 1 of 3 replicas and this works fine.

  Except! We have some reports/processes that have multiple connections in them, and on some occasions the the second connection made is done to a replica that isnt quite in sync yet - we dont have particularly high latency between the replicas, but sometimes those 20 or so seconds make a difference to the end user who has mis-matching range of transactions on the screen. (Think an Invoice records, that has no InvoiceItems to display)

  So my questions, the first, is there a way to ensure or to persist the first the connection/replica is re-used subsequently (one option is to grab the servername on its first connection, and re-use it on its child connections, but I was hoping rahter than code change, there would be a connection string change to magically do this?)

  Secondly, in terms of latency between the replicas what is the best way to determine where that latency lies - is it the network, or is either of the principle or replica servers being slow?

  Cheers,

  A.R

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  February 26, 2022 at 10:10 am

  #3989402

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

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