Trial And Error With Read-Only Replica Queries

Cody Konior stress tests Availability Group round-robin routing:

I’ve been hearing about round-robin read-only routing ever since SQL 2016 came out but whenever I tried to test if it’s working it never seemed to be. But now I know exactly how it works and there’s a few loopholes where it may not trigger, and they’re not the documented ones you’re thinking of.

To test the limits of it you’re going to need:

  • PowerShell 5.1
  • Pester 4 (Install-Module Pester -Force)
  • DbData (Install-Module DbData -Force)

I’ll explain any of the Pester and DbData bits along the way so don’t worry. They’re minor framework stuff.

There’s some good stuff here around connection pooling, so check it out.

Related Posts

Considerations Before Failing Over an AG

David Fowler points out a few things to look at before failing over an Availability Group: What about your SQL Agent jobs? Have you got jobs that perform actions on your data? If you have, do those jobs exist on the new primary? If they don’t then I’m happy to bet that whatever function that […]

Read More

Connecting with Read Intent

John McCormack shows two ways to connect to an Availablity Group listener with read-only intent: SQLCMDThe -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. […]

Read More

Categories

March 2018
MTWTFSS
« Feb Apr »
 1234
567891011
12131415161718
19202122232425
262728293031