Nice one, if you don't read it carefully you can oversee the number of initial default files which in our case is 8.

For a quick second I had revise it...

JeepHound (9/28/2015)

---

I think I see your problem, when primary is myreplica then the route needs to have myreplica2 first, you originally posted the opposite so your primary settings should look...

WilburBud,

I tried this portion of the code:

sqlcmd -e -d MyDB -l 30 -M -K READONLY -Q

"SELECT CAST(@@SERVERNAME AS VARCHAR(30)) AS SERVER_NAME; SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DATABASE_NAME"

-S ListenerName

I...

Thanks for the link,

so this is what I got from the article.

Once I ran this query:

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",

...

Unfortunately no,

I'm still working to find the answer to this.

Once we find the answer, it will be very good to distribute this information forward, since there maybe many DBA out...

The Listener is setup to run on port 1433

Also to add,

I've notice the same has happen with SSMS, where I apply additional connection parameters (ApplicationIntent=Readonly) I still see the reads on the primary replica not the secondary.

Thanks for the reply,

I've notice a few things.

I needed to setup Read_only_Routing_URL and Read_Only_routing_List.

example:

ALTER AVAILABILITY GROUP MyGroup

MODIFY REPLICA ON N'Myreplica' WITH

(SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'tcp://Myreplica.domain.com:1433'))

ALTER AVAILABILITY GROUP MyGroup

MODIFY REPLICA ON N'Myreplica2'...

Not necessarily, unless you have secondary copies as read-only / no.

Example under the readable secondary if you have both primary/secondary to readable secondary = YES

when ever you need to failover...