May 29, 2016 at 12:45 am
Have created a always on setup in which 3 nodes are involved 2 in SQL server Failover Cluster and 1 Always On AG.
I have connected to the listener and tried to run select but its always going to primary it seems . I am trying this by running the select query followed by select @@servername.
Can anyone pls share a query to check where the select queries are routed?
May 29, 2016 at 10:25 pm
The listener is really a way for clients to find the instance where the databases in the availability group are currently available. The listener itself is not an instance of SQL Server at all which I think is what you are thinking.
If you connect to the listener (e.g. with SSMS) and list the databases that are on the instance, then failover to the other instance in the availability group, re-connect to the listener and list the databases again, you should see a different list because each of the instances (probably) has different database on it.
May 29, 2016 at 11:52 pm
Thanks HappyCat.
I understand that Listener is not an instance. My question is how can i determine where a query is routed, to primary or secondary? I believe that read-only queries goes to secondary and how can i make sure that.
May 30, 2016 at 2:07 pm
Queries aren't routed, the entire connection is. The connection does not change based on the type of query executing on that connection. You will connect to the Listener in either read-write (default) or read-only mode (called the "Application Intent"), and the Listener will route that connection to the proper instance.
Have you set up Read-Only Routing to specifically send read-only connections to the AG's secondary instance? The default is to send all connections only to the primary.
Are you setting the optional Application Intent property of the connection string to ReadOnly before you connect? All connections by default are read-write, and will go to the primary.
To connect SSMS2014 to an AG Listener in read-only mode (and be routed to a read-only secondary if that has been configured):
1. In the "Connect to Database Engine" box, click the 'Options >>' button, then click the 'Additional Connection Parameters' tab.
2. In the large text box provided for additional connection parameters, type ApplicationIntent=ReadOnly;
Note that all applications you wish to use the read-only secondary must also explicitly set the ApplicationIntent connection property to read-only at time of connection, or they will always go to the primary.
This MSDN guide on troubleshooting AG configuration may help: https://msdn.microsoft.com/en-us/library/ff878308%28v=sql.120%29.aspx#ROR
-Eddie
Eddie Wuerch
MCM: SQL
May 31, 2016 at 4:47 am
Eddie Wuerch (5/30/2016)
Queries aren't routed, the entire connection is. The connection does not change based on the type of query executing on that connection. You will connect to the Listener in either read-write (default) or read-only mode (called the "Application Intent"), and the Listener will route that connection to the proper instance.Have you set up Read-Only Routing to specifically send read-only connections to the AG's secondary instance? The default is to send all connections only to the primary.
Are you setting the optional Application Intent property of the connection string to ReadOnly before you connect? All connections by default are read-write, and will go to the primary.
To connect SSMS2014 to an AG Listener in read-only mode (and be routed to a read-only secondary if that has been configured):
1. In the "Connect to Database Engine" box, click the 'Options >>' button, then click the 'Additional Connection Parameters' tab.
2. In the large text box provided for additional connection parameters, type ApplicationIntent=ReadOnly;
Note that all applications you wish to use the read-only secondary must also explicitly set the ApplicationIntent connection property to read-only at time of connection, or they will always go to the primary.
This MSDN guide on troubleshooting AG configuration may help: https://msdn.microsoft.com/en-us/library/ff878308%28v=sql.120%29.aspx#ROR
-Eddie
Thanks alot Eddie,
I am trying to connect using SSMS and tried gave ApplicationIntent=ReadOnly , and when i query a table and if i run select @@servername along with that it showing primary. S
May 31, 2016 at 1:50 pm
Rechana Rajan (5/29/2016)
Have created a always on setup in which 3 nodes are involved 2 in SQL server Failover Cluster and 1 Always On AG.I have connected to the listener and tried to run select but its always going to primary it seems . I am trying this by running the select query followed by select @@servername.
Can anyone pls share a query to check where the select queries are routed?
Have you configured our AG readonly routing configuration, can you post details of the script you used (obfuscate first)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 31, 2017 at 2:31 am
Perry Whittle - Tuesday, May 31, 2016 1:50 PMRechana Rajan (5/29/2016)
Have created a always on setup in which 3 nodes are involved 2 in SQL server Failover Cluster and 1 Always On AG.I have connected to the listener and tried to run select but its always going to primary it seems . I am trying this by running the select query followed by select @@servername.Can anyone pls share a query to check where the select queries are routed?Have you configured our AG readonly routing configuration, can you post details of the script you used (obfuscate first)
Thanks Perry and sorry for the late response.
Did ReadOnly Routing as per below blog
Now getting below error when the primary is set to Allow Read\Write Connections.
Msg 979, Level 14, State 1, Line 1
The target database ('TESTDB') is in an availability group
and currently does not allow read only connections.
For more information about application intent, see SQL Server Books Online.
alter availability group [DB100L-TST]
modify replica on 'DB100-TST\AG1' with
(secondary_role(read_only_routing_url='TCP://DB100-TST.ABC.COMPANY.IN:5022'))
GO
alter availability group [DB100L-TST]
modify replica on 'DB103-TST\DR' with
(secondary_role(read_only_routing_url='TCP://DB103-TST.ABC.COMPANY.IN:5022'))
GO
January 31, 2017 at 5:18 am
I've seen this before. Even with read-only routing (seemingly) correctly configured, the listener is still sending read-intent queries straight to the Primary. Have you set up a read-only routing list?
January 31, 2017 at 5:30 am
Beatrix Kiddo - Tuesday, January 31, 2017 5:18 AMI've seen this before. Even with read-only routing (seemingly) correctly configured, the listener is still sending read-intent queries straight to the Primary. Have you set up a read-only routing list?
Thanks Beatrix,
Can you help me understand Read-Only Routing List? You mean the order of servers serving the request? We have only 1 secondary and I want to make sure read only requests from application is pointed to secondary.
January 31, 2017 at 10:34 pm
This was removed by the editor as SPAM
February 6, 2017 at 8:48 am
Rechana Rajan - Tuesday, January 31, 2017 5:30 AMBeatrix Kiddo - Tuesday, January 31, 2017 5:18 AMI've seen this before. Even with read-only routing (seemingly) correctly configured, the listener is still sending read-intent queries straight to the Primary. Have you set up a read-only routing list?Thanks Beatrix,
Can you help me understand Read-Only Routing List? You mean the order of servers serving the request? We have only 1 secondary and I want to make sure read only requests from application is pointed to secondary.
have you checked my stairway to alwayson, see this link
Level 6 will get you to the RO routing section.
Quick question, what are the settings for the replicas in the primary and secondary roles, screenshot if you like but obfuscated first
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply