Availability Group Listener not routing ApplicationIntent=Readonly to secondary replica
-
I'm in the process of setting up a test environment for using availability groups. Our goal is to have our web based application leverage the secondary replica for readonly queries to effectively scale out our SQL Environment. I believe this is a pretty standard configuration?
I have two servers, each with a named instance of SQL installed. Both instances have been configured to use a specific port
HQFRMDBPRD01\INST1 - PORT 50101
HQFRMDBPRD01\INST2 - PORT 50102
I have built an availability group Named "AlwaysThere"
The Group contains one database also named "AlwaysThere"
The availability Listener is named "AGL_AlwaysThere" and is configured with one IP address, listening on port 1433.
The Replica's are configured as follows
HQFRMDBPRD01\INST1 - Role: Primary
HQFRMDBPRD01\INST2 - Role: Secondary
When I connect with SSMS and modify the connect options to "ApplicationIntent=ReadOnly" I get connected to the Primary Replica even though I specified the ApplicationIntent="ReadOnly" which I thought should route me to the Secondary Replica.
If I issue a Select statement
select * from AlwaysThere.dbo.dba
I get the following error
"The target database ('AlwaysThere') is in an availability group and currently does not allow read only connections. For more information about application intent, see SQL Server Books Online.
If I change my "Connections In Primary Role" setting to be "Allow All Connections" The query completes successfully, but it competes against the Primary Replica.
I did do a test, and if I connect directly against HQFRMDBPRD01\INST2 and specify "ApplicationIntent=ReadOnly" I can select from the database. This however means there is no dynamic routing in the event the Availability Group fails over and HQFRMDBPRD01\INST2 is now the Primary.
Is this really the way it is supposed to work? I was under the impression that I would use the Availability Group Listener for my connection point, and then based on whether ApplicationIntent=ReadOnly was specified or not the Availability Group Listener would direct the connection to the correct replica.
-
I've determined that I missed one important step.
In order to configure the readonly routing, you have to run TSQL scripts to first declare that the replica is available for readonly connections, and then give each replica a list of what servers to try to connect to when application intent is specified.
http://msdn.microsoft.com/en-us/library/hh710054.aspx
http://www.sqlservercentral.com/blogs/sqldownsouth/2012/02/08/alwayson-configuring-secondary-read-only-access/[/url]
I've followed these steps and I still can't get it to work properly.
-
replica_idrouting_priorityread_only_replica_id
13549E6F-6989-4EC8-AE31-64395DB036F51F96F2FAD-96C1-4326-8CD9-0094DC46F679
13549E6F-6989-4EC8-AE31-64395DB036F5213549E6F-6989-4EC8-AE31-64395DB036F5
F96F2FAD-96C1-4326-8CD9-0094DC46F679113549E6F-6989-4EC8-AE31-64395DB036F5
F96F2FAD-96C1-4326-8CD9-0094DC46F6792F96F2FAD-96C1-4326-8CD9-0094DC46F679
-
can you supply the results of this query please against primary and secondaries
selectar.replica_server_name
, ar.endpoint_url
, ar.replica_server_name
, ar.availability_mode_desc
, ar.failover_mode_desc
, ar.session_timeout
, ar.primary_role_allow_connections_desc
, ar.secondary_role_allow_connections_desc
, ar.backup_priority
, ar2.read_only_routing_url
from sys.availability_replicas ar
inner join sys.availability_read_only_routing_lists avr
on ar.replica_id = avr.replica_id
inner join sys.availability_replicas ar2
on avr.read_only_replica_id = ar2.replica_id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Primary
replica_server_nameendpoint_urlreplica_server_nameavailability_mode_descfailover_mode_descsession_timeoutprimary_role_allow_connections_descsecondary_role_allow_connections_descbackup_priorityread_only_routing_url
HQFRMDBPRD02\INST1TCP://HqFrmDbPrd02.HQ.CompanyName.LOCAL:5022HQFRMDBPRD02\INST1SYNCHRONOUS_COMMITAUTOMATIC10ALLREAD_ONLY50tcp://HqFrmDbPrd01.HQ.CompanyName.LOCAL:50101
HQFRMDBPRD01\INST1TCP://HqFrmDbPrd01.HQ.CompanyName.LOCAL:5022HQFRMDBPRD01\INST1SYNCHRONOUS_COMMITAUTOMATIC10ALLREAD_ONLY50tcp://HqFrmDbPrd02.HQ.CompanyName.LOCAL:50102
Secondary
replica_server_nameendpoint_urlreplica_server_nameavailability_mode_descfailover_mode_descsession_timeoutprimary_role_allow_connections_descsecondary_role_allow_connections_descbackup_priorityread_only_routing_url
HQFRMDBPRD02\INST1TCP://HqFrmDbPrd02.HQ.CompanyName.LOCAL:5022HQFRMDBPRD02\INST1SYNCHRONOUS_COMMITAUTOMATIC10ALLREAD_ONLY50tcp://HqFrmDbPrd01.HQ.CompanyName.LOCAL:50101
HQFRMDBPRD01\INST1TCP://HqFrmDbPrd01.HQ.CompanyName.LOCAL:5022HQFRMDBPRD01\INST1SYNCHRONOUS_COMMITAUTOMATIC10ALLREAD_ONLY50tcp://HqFrmDbPrd02.HQ.CompanyName.LOCAL:50102
-
Any thoughts?
-
I spoke with Microsoft support today about my issue. What I have been able to determine so far is that if I create a new text file on my desktop, and then rename the extension to .udl. Then change the Provider to SQL Server Native Client 11, then on the Connection tab enter the name of my availability group listener, connect with NT security, and select my database name from the drop down, then on the ALL tab change the application Intent from READWRITE to READONLY, then go back to the connection tab and click test, it does connect to my readable secondary. If I try to connect through SSMS with Application Intent = ReadOnly I get an error because of the space between Application and Intent, but apparently when I connect through this UDL method it works properly?
-
Try to use this in SSMS: Initial Catalog=DB NAME;ApplicationIntent=ReadOnly
I had the same problem but when I specified which DB to connect to it worked and used all the authorities the account had.
-
That's it. That was what I needed to do. Thank you for your help!
-
How can we make "ApplicationIntent = ReadOnly " persistent in SSMS?
Prakash B
-
In my experience, SSMS 2016 Will remember how the connection was last used. Unfortunately I haven't found a way to configure it that way in the registered servers.
-
Yes, I am aware of the setting being persistent on SQL2016. I would like to know for versions older than 2016.
Prakash B
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply