We have an app that needs to point to the Read Only Instance of our AG for reporting and I am finding that it is not pointing there it is only pointing to the primary.
How can I make sure where and how the connection string is working. Here is my connection string:
Data Source=Listener,Port;Initial Catalog=dbName;Integrated Security=SSPI;Trusted_Connection=True;ApplicationIntent=ReadOnly;Persist Security Info=true;MultipleActiveResultSets=True;App=EntityFramework
I am using SQL2014 latest patch
Don't direct the connection to the listener - go directly to the read-only instance.
There is a method called Read-Only Routing which allows you to connect to the AG listener but the traffic gets routed to the read-only (secondary replica) of the Availability Group.
I cannot talk you through setting it all up as I have not used it before, in fact we have moved away from using AG's now, but if you search for that term you shoud find some MS articles that walk you through it.
One thing to note, if you do use the method of just setting your connection string to the read-only instance, upon a failover of your AG's you would need to reconfigure the connection string again...