ApplicationIntent=ReadOnly does not send the connection to the secondary copy - by SQLPilot

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 700331 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 11/10/2011 6:01:34 AM
Access Restriction Public


I setup an Avaliablity group and let the secondary as Read in Read Only Intent mode.  I then wrote a small vbscript that should be sent to the secondary server but it did not.   The query did run, but it was on the Primary server.

I did Select @@ServerName in the query as well as Profiler trace.   Nothing touched the secondary.   Direct access to it was turned off unless I changed it to Read Only for direct connections.

I expect the connection to be sent to the secondary server for the read.   After that happens I would expect the Insert in my script to fail.   I don't expect ApplicationIntent=ReadOnly to make my connection ReadOnly, but rather send me to the database that is readonly.
Sign in to post a comment.
Posted by Ram Naresh Talluri on 5/25/2012 at 12:10 PM
I am facing same kind of problem. I have specified ApplicationIntent=ReadOnly in my connection string. But it still connecting to Primary not secondary. I have verified the server configuration and those are configured properly ..
Can you please help me here .. what to check??

Manually i tried running the update query on both primary and secondary .. Secondary is not allowing it is throwing readonly db error .. ANd I have executed below statements it is giving my seconday db details. Seems everything set properly .. but When I try to query using ApplicationIntent=Readonly in connection string it is connecting to primary only not secondary. Pleasehelp me Thanks in advance

DECLARE @groupID uniqueidentifier
DECLARE @groupdatabaseid uniqueidentifier
DECLARE @replicaid uniqueidentifier

select @groupID= group_id --,@groupdatabaseid=group_database_id
from sys.availability_databases_cluster where database_name ='DBName'
and group_id in (
select group_id--,listener_id,dns_name
from sys.availability_group_listeners where dns_name='ServerName")

select @replicaid=replica_id from sys.dm_hadr_availability_replica_states where role_desc='Secondary' and group_id =@groupID

select @groupdatabaseid=group_database_id from sys.dm_hadr_database_replica_states where group_id=@groupID and replica_id =@replicaid

select replica_server_name as secondaryServer from sys.availability_replicas where group_id =@groupID and replica_id =@replicaid
Posted by SQLPilot on 11/18/2011 at 7:48 AM
I'm now on RC0.

I have a healthy AG, All Green boxes, All sync'd

I have Secondary in ReadOnly mode. I use a vbscript with this constring....
connectionstring = "Provider=SQLNCLI11;Server=SQL11-1b;Database=HA1; Trusted_Connection=yes; ApplicationIntent=ReadOnly"

When I run the query "Select @@ServerName" I get...
C:\VBScript\test.vbs(20, 1) Microsoft SQL Server Native Client 11.0: The target
database ('HA1') is in an availability group and is currently accessible for con
nections when the application intent is set to read only. For more information a
bout application intent, see SQL Server Books Online.

Posted by SQLPilot on 11/18/2011 at 6:45 AM
Sorry I was OOF. I'll retry this today and provide feedback.    The Secondary was showing Synchronizing, not Synchronized. Not sure why yet, but I'll let you know.
Posted by Microsoft on 11/17/2011 at 12:58 PM
Hi I'm closing this if you see the same issue. please reply and we can revisit.
Posted by Microsoft on 11/15/2011 at 9:41 AM
Hi , As I understand this is solved.
so we close this connect item.
Posted by Microsoft on 11/11/2011 at 5:30 PM
Hi, This is a pretty well tested functionality, we have seen people misconfigure and run into this issue. Can you ...

1)Make sure VNN is set up correctly. Use VNN plus database only can you connect to primary correctly.
2)Make sure routing list is set up correctly.
3)Make sure the routing URL is correct. Can you use the URL connect to the correct target?
4)Make sure VNN is used here and ApplicationIntent is set to ReadOnly, and correct database name is provided.
5)Make sure secondary replica accepts readonly connects.
6)Make sure 2nd replica database is in sync state. Run select * from sys.dm_hadr_database_replica_states

Please run the following:
select * from sys.availability_replicas
select * from sys.availability_databases_cluster
select * from sys.availability_replica_routing_lists
select * from sys.availability_group_listeners
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_availability_replica_states

If everything seems right, please create XEvent session on the primary replica and add following events:
• read_only_route_complete
• read_only_route_fail
• hadr_evaluate_readonly_routing_info
• connectivity_ring_buffer_recorded

Try connecting to the primary replica VNN against and check events fired.