1

I'm trying to figure out an issue that I'm seeing when you setup a readonly replica. I have a simple application that will query the readonly replica and I can validate that it hits the correct one by running a @@servername. Everything works as planned. I can failover to the synchronous servers without any issue and my test application still reads from the replica.

The issue I have is that when I reboot the readonly replica, the application never returns back to the readonly replica. It goes to whatever is set in the routing even after the readonly replica is back online. New connections from a different server will hit the readonly replica. If I try a different method of connecting to the AG on the server I'm running the test, it will connect to the readonly replica. The powershell script below will continue to hit a different server.

If I stop the application, wait about 10 or 15 minutes then start up the application, I'll then hit the readonly replica. If I set my connection string to use pooling=false. Then it behaves as it's supposed to by hitting the replica once the replica is ready to accept connections.

This is what I'm using to test out the readonly replica scenario.

function Get-Readonly-Server
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=AG_OLTP_Listnr.myenv.local;Database=AGTest;Integrated Security=True;applicationintent=readonly"
    $SqlConnection.Open()
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "select @@servername as colName into #tmp; select colName from #tmp; drop table #tmp"
    $SqlCmd.Connection = $SqlConnection
    $servername = $SqlCmd.ExecuteScalar()
    $SqlConnection.Close()
    $servername
}

for ($i=1; $i -le 1000000; $i++) 
    {
        Get-Readonly-Server
        write-host $i
        Start-Sleep -s 1
    }

What am I missing? Or is my test incorrect?

jdgonzalez
  • 81
  • 7
  • Why are you inserting into #tmp as your test? Not sure if that over-rides the readonly replica behavior since it's a write action. Have you tried just selecting @@SERVERNAME to your output? – Mike Fal Sep 28 '15 at 21:24
  • I tried both ways. I put the #tmp there so that I could check to see if queries that had a temp table would work with the application intent. They do. The issue here is that if I bring down the readonly replica it stops reading from the replica (which is what you would expect) but when it comes back up, the application never starts reading from the readonly replica again. – jdgonzalez Sep 29 '15 at 13:52
  • I'll also add that if I cause a failover on the synchronous databases, the connections to the read only replica begin to work again. But that sort of defeats the purpose. – jdgonzalez Sep 29 '15 at 14:08

0 Answers0